Re: [PERFORM] slow DELETE on 12 M row table

2009/6/27 Scott Carey : > In addition to the above, note that long lived transactions cause all sorts > of other problems in the database.  In particular, table and index bloat can > become severe due to this sort of poor client behavior if there is a lot of > update or delete activity.  You can fi

Re: [PERFORM] Odd performance / query plan with bitmasked field as opposed to equality

On Mon, Jul 13, 2009 at 4:46 PM, Frank Joerdens wrote: > I can't figure what is going on below; first of all, this count  which > returns 1.5 million from a ~2 million row table: > > woome=# explain analyze SELECT COUNT(*) FROM "webapp_person" WHERE > "webapp_person"."permissionflags" = > B'000

Re: [PERFORM] regression ? 8.4 do not apply One-Time Filter to subquery

On Wed, Jul 1, 2009 at 8:08 PM, Sergey Burladyan wrote: > > 8.4 from CVS HEAD: > EXPLAIN ANALYZE select * from (select n, 1 as r from generate_series(1, > 10) as n union all select n, 2 from generate_series(1, 10) as n) as x > where r = 3; >                                                

Re: [PERFORM] Performance difference between IN(...) and ANY(...) operator

On Wed, Jul 8, 2009 at 2:50 AM, Gnanam wrote: > I'm using both IN and ANY() operators extensively my application.  Can > anybody answer me on the following questions: >        1) Which operator is advantage over the another, interms of > performance? >        2) If I've indexed these columns, will

Re: [PERFORM] Very big insert/join performance problem (bacula)

On Thu, Jul 16, 2009 at 6:30 PM, Kevin Grittner wrote: > Marc Cousin wrote: > >> As mentionned in another mail from the thread (from Richard Huxton), >> I felt this message in the documentation a bit misleading : >> >> effective_cache_size (integer) >>  Sets the planner's assumption about the effe

Re: [PERFORM] Very big insert/join performance problem (bacula)

On Fri, Jul 24, 2009 at 1:13 AM, Marc Cousin wrote: >> It really has very little impact.  It only affects index scans, and >> even then only if effective_cache_size is less than the size of the >> table. >> >> Essentially, when this kicks in, it models the effect that if you are >> index scanning a

Re: [PERFORM] Performance of quer or procedure going down when we are taking the backup

On Mon, Jul 20, 2009 at 6:15 AM, ramasubramanian wrote: >   Dear all, >       Performance of query or procedure going down when we are taking the > backup of that schema(it is obvious), But how to  increase the performance. > > Regards, > Ram. You're going to need to provide an awful lot more deta

Re: [PERFORM] Can Postgres use an INDEX over an OR?

2009/7/20 Віталій Тимчишин : > 20 липня 2009 р. 11:02 Chris написав: >> >> Віталій Тимчишин wrote: >>> >>> >>> 2009/7/20 Robert James >> > >>> >>> >>>    Hi. I notice that when I do a WHERE x, Postgres uses an index, and >>>    when I do WHERE y, it does so as well,

Re: [PERFORM] Can Postgres use an INDEX over an OR?

2009/7/27 Віталій Тимчишин : > > > 27 липня 2009 р. 13:53 Robert Haas написав: >> >> Hmm.  What you're suggesting here is that we could consider >> implementing OR conditions by rescanning the inner side for each index >> qual and then unique-ifying th

Re: [PERFORM] Full text search with ORDER BY performance issue

On Mon, Jul 20, 2009 at 8:12 AM, Oleg Bartunov wrote: >> Here's a couple of queries: >> >> archive=> explain analyze select * from a where  comment_tsv @@ >> plainto_tsquery('love') order by timestamp desc limit 24 offset 0; >> >> QUERY PLAN >> -- >> Limit  (cost=453248.73..453248.79 rows=2

Re: [PERFORM] Full text search with ORDER BY performance issue

On Wed, Jul 29, 2009 at 10:22 AM, Tom Lane wrote: > Robert Haas writes: >> If love is an uncommon word, there's no help for queries of this type >> being slow unless the GIN index can return the results in order.  But >> if love is a common word, then it would be faster

Re: [PERFORM] Full text search with ORDER BY performance issue

On Wed, Jul 29, 2009 at 11:29 AM, Tom Lane wrote: > Ain't transactional DDL wonderful? Yes. :-) ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] autovacuum 'stuck' ?

On Wed, Jul 29, 2009 at 12:47 PM, Doug Hunley wrote: > When reviewing the vacuum logs, I notice that on any given day > autovacuum only seems to touch four of the tables in one of our > schemas (not counting toast tables). However, if I look at the > pgstatspack output for the same day, I see that

Re: [PERFORM] Performance 8.4.0

On Fri, Jul 31, 2009 at 12:22 AM, Chris Dunn wrote: > constraint_exclusion = on This is critical if you need it, but a waste of CPU time if you don't. Other than that your paramaters look good. Are you using the default page cost settings? I see you have 12 GB RAM; how big is your database? ..

Re: FW: [PERFORM] Performance 8.4.0

On Sun, Aug 2, 2009 at 10:04 PM, Chris Dunn wrote: > The database is 8gb currently. Use to be a lot bigger but we removed all > large objects out and developed a file server storage for it, and using > default page costs for 8.4, I did have it changed in 8.1.4 You might want to play with lowerin

Re: [PERFORM] select count(idn) is slow (Seq Scan) instead of Bitmap Heap.. why?

2009/8/2 Adam PAPAI : > Hello, > > > I have a problem with an inner join + count(). > > my query is: > > explain analyze select > k.idn,k.kerdes_subject,k.kerdes_text,u.vezeteknev,u.keresztnev,u.idn as > user_id, kg.kategoria_neve, count(v.idn) > > FROM kategoriak as kg > > INNER JOIN kerdesek as k

Re: [PERFORM] GiST, caching, and consistency

On Tue, Aug 4, 2009 at 12:06 PM, Matthew Wakeling wrote: > > I'm seeing an interesting phenomenon while I'm trying to > performance-optimise a GiST index. Basically, running a performance test > appears to be the same thing as running a random number generator. For > example, here I'm running the s

Re: [PERFORM] GiST, caching, and consistency

On Wed, Aug 5, 2009 at 6:20 AM, Matthew Wakeling wrote: > It is certainly doing a sequential scan. So are you saying that it will > start a sequential scan from a different part of the table each time, even > in the absence of other simultaneous sequential scans? Looks like I'm going > to have to r

Re: [PERFORM] postgresql and syslog

On Tue, Aug 4, 2009 at 5:16 PM, Ibrahim Harrani wrote: > Hi, > > I am using postgresql 8.3 with FreeBSD. FreeBSD is using syslog by > default for postgresql logs. > I would like to disable syslog in postgresql.conf. Does this change > increase the performance? > What is the impact of using syslog o

Re: [PERFORM] SQL select query becomes slow when using limit (with no offset)

On Fri, Aug 7, 2009 at 4:00 AM, Kees van Dieren wrote: > Would it get attention if I submit this to > http://www.postgresql.org/support/submitbug ? (in fact it is not really a > bug, but an improvement request). I think that many of the people who read that mailing list also read this one, includi

Re: [PERFORM] SQL select query becomes slow when using limit (with no offset)

On Fri, Aug 7, 2009 at 5:09 PM, Scott Carey wrote: > On 8/7/09 5:53 AM, "Robert Haas" wrote: > >> On Fri, Aug 7, 2009 at 4:00 AM, Kees van Dieren >> wrote: >>> Would it get attention if I submit this to >>> http://www.postgresql.org/support/submitbug

Re: [PERFORM] Need suggestions on kernel settings for dedicated FreeBSD/Postgresql machine

On Fri, Aug 7, 2009 at 5:24 PM, Culley Harrelson wrote: > Hi Everyone, > > I manage a freeBSD server that is dedicated to postgresql.  The > machine has 4 gigs of ram and there is a single database powering a > web application that is hosted on a neighboring machine.  The web > application is mostl

Re: [PERFORM] ORDER BY ... LIMIT and JOIN

On Sun, Aug 9, 2009 at 3:26 PM, Fizu wrote: >               ->  Index Scan using country_ranking_user_idx on "user" >  (cost=0.00..4807.25 rows=1710 width=143) (actual > time=20.923..4898.931 rows=1972 loops=1) >                     Index Cond: (country_id = 5) An index scan that picks up 1972 row

Re: [PERFORM] Bottleneck?

On Mon, Aug 10, 2009 at 2:22 AM, Ip Wing Kin John wrote: > Hi Scott, > > Thanks for you suggestion. I have follow your suggestion by disable > nestloop and have a substantial improvement. Takes 51s now. I have > attached the new query plan in another file. > > What I want to ask is, is there any ot

Re: [PERFORM] SQL select query becomes slow when using limit (with no offset)

On Mon, Aug 10, 2009 at 11:19 AM, Kevin Grittner wrote: > Robert Haas wrote: > >> Just handling better the case where we pick a straight nested loop >> rather than a hash join would help a lot of people.  Some basic >> conservatism about the number of outer rows

Re: [PERFORM] Why is vacuum_freeze_min_age 100m?

On Tue, Aug 11, 2009 at 5:23 PM, Josh Berkus wrote: > On 8/11/09 2:14 PM, Josh Berkus wrote: >> All, >> >> I've just been tweaking some autovac settings for a large database, and >> came to wonder: why does vacuum_max_freeze_age default to such a high >> number?  What's the logic behind that? >> >>

Re: [PERFORM] Why is vacuum_freeze_min_age 100m?

On Tue, Aug 11, 2009 at 6:06 PM, Josh Berkus wrote: > >> I don't think that's the name of the parameter, since a Google search >> gives zero hits.  There are so many fiddly parameters for this thing >> that I don't want to speculate about which one you meant. > > Sorry, subject line had it correct.

Re: [PERFORM] Why is vacuum_freeze_min_age 100m?

On Wed, Aug 12, 2009 at 5:57 PM, Tom Lane wrote: > "Kevin Grittner" writes: >> Yeah, I know, but feel like I'm being a bit naughty in using VACUUM >> FREEZE -- the documentation says: > >> | Selects aggressive "freezing" of tuples. Specifying FREEZE is >> | equivalent to performing VACUUM with the

[PERFORM] Re: [HACKERS] freezing tuples ( was: Why is vacuum_freeze_min_age 100m? )

On Thu, Aug 13, 2009 at 5:33 PM, Jeff Davis wrote: > Or, perhaps when the bgwriter is flushing dirty buffers, it can look for > opportunities to set hint bits or freeze tuples. One of the tricky things here is that the time you are mostly likely to want to do this is when you are loading a lot of

Re: [PERFORM] Why is vacuum_freeze_min_age 100m?

On Thu, Aug 13, 2009 at 5:15 PM, Josh Berkus wrote: > Robert, > >> Ah.  Yeah, I agree with Tom: how would it help to make this smaller? >> It seems like that could possibly increase I/O, if the old data is >> changing at all, but even if it doesn't it I don't see that it saves >> you anything to fr

Re: [PERFORM] Memory reporting on CentOS Linux

On Fri, Aug 14, 2009 at 3:43 PM, Reid Thompson wrote: > On Fri, 2009-08-14 at 14:00 -0400, Jeremy Carroll wrote: >> I am confused about what the OS is reporting for memory usage on >> CentOS 5.3 Linux. Looking at the resident memory size of the >> processes. Looking at the resident size of all post

Re: [PERFORM] number of rows estimation for bit-AND operation

On Tue, Aug 18, 2009 at 6:34 PM, Scott Marlowe wrote: > 2009/8/18 Slava Moudry : >>> increase default stats target, analyze, try again. >> This field has only 5 values. I had put values/frequencies in my first post. > > Sorry, kinda missed that.  Anyway, there's no way for pg to know which > operat

Re: [PERFORM] number of rows estimation for bit-AND operation

On Thu, Aug 20, 2009 at 9:58 PM, Scott Marlowe wrote: > On Thu, Aug 20, 2009 at 7:32 PM, Scott Marlowe wrote: >> 2009/8/20 Slava Moudry : >>> Hi, >>> Yes, I thought about putting the bit-flags in separate fields. >>> Unfortunately - I expect to have quite a lot of these and space is an issue >>> w

Re: [PERFORM] Number of tables

On Thu, Aug 20, 2009 at 8:38 PM, Alvaro Herrera wrote: > Greg Stark wrote: > >> It would be nice to have a solution to that where you could create >> lightweight temporary objects which belong to an "application session" >> which can be picked up by a different database connection each go >> around

Re: [PERFORM] limiting results makes the query slower

On Thu, Aug 20, 2009 at 9:50 PM, Jaime Casanova wrote: > in a web app we have a query that we want to show in limited results > at a time, this one executes in 10 seconds if i use limit but executes > in 300ms if i remove it. > why is that happening? the query is using and index for avoiding the >

Re: [PERFORM] PostgreSQL does CAST implicitely between int and a domain derived from int

2009/8/27 Kevin Grittner : > It is *possible* that if you boost your default_statistics_target and > run ANALYZE (or VACUUM ANALYZE), it will recognize that it isn't a > good idea to read backwards on that index.  I would try it and see, if > that's practical for you. I notice this in one of the p

Re: [PERFORM] Slow select times on select with xpath

On Wed, Sep 2, 2009 at 11:04 AM, astro77 wrote: > > I've got a table set up with an XML field that I would like to search on with > 2.5 million records. The xml are serialized objects from my application > which are too complex to break out into separate tables. I'm trying to run a > query similar

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis wrote: > Sorry about not responding to the whole list earlier - this is my first time > posting to a mailing list. > > Would providing more information about the size and complexities of the > databases help? > > I measure I/O stats with iostat - here is

Re: [PERFORM] Slow select times on select with xpath

On Thu, Sep 3, 2009 at 4:06 PM, astro77 wrote: > I was receiving an error that an XML field does not support the various > indexes available in postgresql. Please post what happens when you try. > Is there an example of how to do this > properly? Not sure. ...Robert -- Sent via pgsql-performa

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

On Thu, Sep 3, 2009 at 5:27 PM, Andy Colson wrote: > Robert Haas wrote: >> >> On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis wrote: >>> >>> Sorry about not responding to the whole list earlier - this is my first >>> time posting to a mailing list. >>

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

On Thu, Sep 3, 2009 at 7:11 PM, Scott Otis wrote: >> Can you post to the list all the uncommented lines from your > postgresql.conf file and attach the results of "select * from > pg_stat_all_tables" as an attachment? > > I attached a CSV of "select * from pg_stat_all_tables" from one of our > more

Re: [PERFORM] Planner question - "bit" data types

On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > There was a previous thread and I referenced it. I don't have the other one > in my email system any more to follow up to it. > > I give up; the attack-dog crowd has successfully driven me off.  Ciao. Perhaps I'm biased by knowing some of the

Re: [PERFORM] Planner question - "bit" data types

On Mon, Sep 7, 2009 at 8:51 PM, Karl Denninger wrote: > Robert Haas wrote: > > On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > > > There was a previous thread and I referenced it. I don't have the other one > in my email system any more to follow up to it. >

Re: [PERFORM] Planner question - "bit" data types

On Mon, Sep 7, 2009 at 10:05 PM, Karl Denninger wrote: > The individual boolean fields don't kill me and in terms of some of the > application issues they're actually rather easy to code for. > > The problem with re-coding for them is extensibility (by those who install > and administer the package

Re: [PERFORM] Best Profiler for PostgreSQL

On Wed, Sep 9, 2009 at 6:15 AM, Reydan Cankur wrote: > Hi, > > I am running PostgreSQL-8.4.0 on a SMP Server which has 32 processors > (32X2=64 cores). I am working on database parallelism and I need to do > profiling in order to find the relevant parts to parallelize. I wrote 15 > queries which a

Re: [PERFORM] Sub-optimal plan chosen

2009/9/10 : >> Playing around with seq_page_cost (1) and random_page_cost (1), I can get >> the correct index selected. Applying those same settings to our production >> server does not produce the optimal plan, though. > > I doubt setting seq_page_cost and random_page_cost to the same value is >

Re: [PERFORM] Sub-optimal plan chosen

On Thu, Sep 10, 2009 at 12:56 PM, bricklen wrote: > On Thu, Sep 10, 2009 at 8:43 AM, Tom Lane wrote: >> >> bricklen writes: >> > Is there any other data I can provide to shed some light on this? >> >> The table and index definitions? >> >> The straight indexscan would probably win if the index c

Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity

On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily wrote: > On Monday 07 September 2009 03:25:23 Tom Lane wrote: >> Vincent de Phily writes: >> > I've been running this simple delete since yesterday afternoon : >> >> db=# explain delete from message where datetime < '2009-03-03'; >> >> Seq Scan on

Re: [PERFORM] possible wrong query plan on pg 8.3.5,

2009/9/14 : > It seems there's something very wrong - the plans are "equal" but in the > first case the results (actual time) are multiplied by 100. Eithere there > is some sort of cache (so the second execution is much faster), or the > system was busy during the first execution, or there is some

Re: [PERFORM] possible wrong query plan on pg 8.3.5,

2009/9/14 : > Цитат от Robert Haas : > >> 2009/9/14  : >>> >>> It seems there's something very wrong - the plans are "equal" but in the >>> first case the results (actual time) are multiplied by 100. Eithere there >>> is some

Re: [PERFORM] disable heavily updated (but small) table auto-vecuuming

2009/9/15 Ludwik Dylag : > Hello > I have a database where I daily create a table. > Every day it is being inserted with ~3mln rows and each of them is being > updated two times.The process lasts ~24 hours so the db load is the same at > all the time. total size of the table is ~3GB. > My current v

Re: [PERFORM] Database performance post-VACUUM FULL

On Fri, Sep 18, 2009 at 8:44 AM, Karl Wright wrote: > Hi all, > > We're using Postgresql 8.3.7 on Debian.  We are seeing a very strange > performance situation with our application which I am hoping that someone > can shed light on. > > Our tests find that our application runs quite well on 8.3.7

Re: [PERFORM] Use of BETWEEN with identical values

On Thu, Sep 17, 2009 at 5:02 PM, André Volpato wrote: > André Volpato escreveu: >> >> (...) >> >> (Postgres 8.3.6, Debian Linux 2.6.18-6-amd64) >> >> (...) > >> Condition 1: >> # select fat_referencia from bds_contratacao_fatura where fat_referencia >> BETWEEN 200908 AND 200908; >> Index Scan usin

Re: [PERFORM] Different query plans for the same query

2009/9/18 : >> Hi all, >> >> on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans >> for one of our (weird) queries are generated. One of the query plans >> seems to be good (and is used most of the time). The other one is bad - >> the query takes about 2 minutes and the database

Re: [PERFORM] query memory consumption

On Mon, Sep 21, 2009 at 10:47 AM, Alan McKay wrote: > We are looking to optimize the query I was talking about last week > which is killing our system. > > We have explain and analyze which tell us about the cost of a query > time-wise, but what does one use to determine (and trace / predict?) > m

Re: [PERFORM] Regarding Sequential Scans count increase each time we press refresh .

On Thu, Sep 24, 2009 at 8:25 PM, Josh Berkus wrote: > You can't "turn off" sequential scans.  You can only make the planner > less likely to choose them.  But if there's no way to get the data you > need other than a seqscan, it's still going to do one. And that's not a bad thing. For a very sma

Re: [PERFORM] query memory consumption

2009/9/25 Jeff Janes : > 2009/9/22 Grzegorz Jaśkiewicz : >> On Tue, Sep 22, 2009 at 1:46 PM, Alan McKay wrote: Best practice to avoid that, is to bump the work_mem temporarily before the query, and than lower it again, lowers the chance of memory exhaustion. >>> >>> Interesting - I

Re: [PERFORM] Slow query after upgrade to 8.4

On Thu, Sep 24, 2009 at 8:22 AM, Jared Beck wrote: > Should we try to improve statistics collection for that column > (variableid) by using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS? It's worth a try, but I'm not sure it's going to help much. The LIKE condition is hard for the planner to e

Re: [PERFORM] LIMIT confuses the planner (again)

On Mon, Sep 28, 2009 at 4:43 AM, Kouber Saparev wrote: > Hello, > > I am using PostgreSQL 8.3.7 and I am experiencing an issue similar to the > one I've already described some time ago: > http://archives.postgresql.org/pgsql-performance/2009-02/msg00261.php > > Again, adding a LIMIT clause to a qu

Re: [PERFORM] Speed while runnning large transactions.

2009/9/24 : >> On Thu, Sep 24, 2009 at 9:27 AM, wrote: >> >>> Hi. >>> >>> I have a transaction running at the database for around 20 hours .. >>> still >>> isn't done. But during the last hours it has come to the point where it >>> really hurts performance of "other queries". >>> >>> Given pg_sta

Re: [PERFORM] CPU cost of operators

On Wed, Sep 30, 2009 at 1:12 PM, Matthew Wakeling wrote: > > Episode umpteen of the ongoing saga with my GiST indexes. > > For some reason, GiST uses loads of CPU. I have a query that runs entirely > out of cache, and it takes ages. This much I have tried to fix and failed so > far. > > What I wou

Re: [PERFORM] CPU cost of operators

On Wed, Sep 30, 2009 at 4:13 PM, Robert Haas wrote: > On Wed, Sep 30, 2009 at 1:12 PM, Matthew Wakeling wrote: >> >> Episode umpteen of the ongoing saga with my GiST indexes. >> >> For some reason, GiST uses loads of CPU. I have a query that runs entirely >> out

Re: [PERFORM] updating a row in a table with only one row

On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure wrote: > On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek wrote: >>  Hello everyone, >> >>  I'm using PostgreSQL 8.3.8 running on a server with 2 Xeon CPUs, 4GB >>  RAM, 4+2 disks in RAID 5 and CentOS 5.3. There's only one database >>  which dumped wit

Re: [PERFORM] Confusion on shared buffer

On Thu, Oct 1, 2009 at 4:11 AM, S Arvind wrote: > In some docs i read that shared buffer must be increased based on the > maximum dataset size. For my scenario the dataset size is relative small > less then a Gb, but database#  handled by a server is nearly 200db per > server and average connectio

Re: [PERFORM] Confusion on shared buffer

On Sat, Oct 3, 2009 at 2:11 AM, S Arvind wrote: > Thanks Robert, > So for our scenario what is the most important factor to be noted > for performance. Tough to say without benchmarking, but if you have a lot of small databases that easily fit in RAM, and a lot of concurrent connections,

Re: [PERFORM] Confusion on shared buffer

On Sun, Oct 4, 2009 at 9:28 AM, Gurjeet Singh wrote: > On Sun, Oct 4, 2009 at 6:32 AM, Robert Haas wrote: >> >> On Sat, Oct 3, 2009 at 2:11 AM, S Arvind wrote: >> > Thanks Robert, >> > So for our scenario what is the most important factor to b

Re: [PERFORM] Bad performance of SELECT ... where id IN (...)

On Sun, Oct 4, 2009 at 9:58 PM, Omar Kilani wrote: > Hi Xia, > > Try this patch: > > http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch > > It's a hack, but it works for us. I think you're probably spending > most of your query time planning, and this patch helps speed things up > 10x o

Re: [PERFORM] Best suiting OS

On Mon, Oct 5, 2009 at 2:00 AM, Craig James wrote: > Fedora is a very nice project, but it's not suitable for production database > servers. The trick is to write such a kick-ass application that before the Fedora support window ends, the load has increased enough that it's time to upgrade the ha

Re: [PERFORM] Getting a random row

2009/10/13 Grzegorz Jaśkiewicz : > > > 2009/10/13 Shaul Dar >> >> Sorry, I guess I wasn't clear. >> I have an existing table in my DB, and it doesn't have a column with >> serial values (actually it did originally, but due to later deletions of >> about 2/3 of the rows the column now has "holes").

Re: [PERFORM] Indexes on low cardinality columns

On Sat, Oct 17, 2009 at 1:02 PM, Vikul Khosla wrote: > > Thanks Greg!. > > Yes, we do need to query on all 3000 values ... potentially. Considering > that when we changed the B-Tree indexes to Bitmap indexes in Oracle > we saw a huge performance boost ... doesn't that suggest that absence of > thi

Re: [PERFORM] Calculation of unused columns

On Sat, Oct 17, 2009 at 9:41 PM, Tom Lane wrote: > I've been thinking about this since your earlier mail, and I think it > would probably be possible to suppress unused columns in a non-flattened > subquery.  I remain unconvinced that it's worth the trouble though. > A real (not handwavy) example

Re: [PERFORM] Calculation of unused columns

On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: > On Sun, Oct 18, 2009 at 10:35 AM, Tim Landscheidt > wrote: >> Daniel Migowski wrote: >> >>> I have a very common example which would illustrate the >>> above problem a bit more. Guess the following view on a >>> company table, which references

Re: [PERFORM] Calculation of unused columns

On Sun, Oct 18, 2009 at 4:54 PM, Tom Lane wrote: > Robert Haas writes: >> On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: >>> Even if country.id is a primary or unique key? > >> Well, we currently don't have any logic for making inferences based on >&g

Re: [PERFORM] Improving join performance over multiple moderately wide tables

On Fri, Oct 16, 2009 at 5:12 PM, miller_2555 wrote: >  [...snip...] attempted to set the `join_collapse_limit` to '1' [...] That seems like an odd thing to do - why did you do this? What happens if you don't? I have never seen anything like the bizarrely large row estimates that you have here.

Re: [PERFORM] Performance with sorting and LIMIT on partitioned table

On Mon, Oct 12, 2009 at 10:14 AM, Michal Szymanski wrote: > We have performance problem with query on partitioned table when query > use order by and we want to use first/last rows from result set. > More detail description: > We have big table where each row is one telephone call (CDR). > Definit

Re: [PERFORM] Partitioned Tables and ORDER BY

2009/10/19 Grzegorz Jaśkiewicz : > > > On Sun, Oct 11, 2009 at 3:30 PM, Michal Szymanski > wrote: >> >> We have similar problem and now we are try to find solution. When you >> execute query on partion there is no sorting - DB use index to >> retrieve data and if you need let say 50 rows it reads

Re: [PERFORM] Partitioned Tables and ORDER BY

2009/10/19 Grzegorz Jaśkiewicz : > > > 2009/10/19 Robert Haas >> >> 2009/10/19 Grzegorz Jaśkiewicz : >> > >> > >> > On Sun, Oct 11, 2009 at 3:30 PM, Michal Szymanski >> > wrote: >> >> >> >> We have similar problem a

Re: [PERFORM] Random penalties on GIN index updates?

On Wed, Oct 21, 2009 at 2:35 PM, Tom Lane wrote: > Jesper Krogh writes: >> What I seems to miss a way to make sure som "background" application is >> the one getting the penalty, so a random user doing a single insert >> won't get stuck. Is that doable? > > You could force a vacuum every so often

Re: [PERFORM] optimizing query with multiple aggregates

On Wed, Oct 21, 2009 at 10:21 PM, Doug Cole wrote: > On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure wrote: >> >> On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole wrote: >> > I have a reporting query that is taking nearly all of it's time in >> > aggregate >> > functions and I'm trying to figure out

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

On Mon, Oct 26, 2009 at 4:02 PM, Jesper Krogh wrote: > Hi. > > I'm currently trying to figure out why the tsearch performance seems to > vary a lot between different queryplans. I have created a sample dataset > that sort of resembles the data I have to work on. > > The script that builds the data

Re: [PERFORM] bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).

On Tue, Oct 27, 2009 at 11:08 AM, wrote: > In my example the seq-scan evaulates 50K tuples and the heap-scan 40K. > The question is why does the "per-tuple" evaluation become that much more > expensive (x7.5)[1] on the seq-scan than on the index-scan, when the > complete dataset indeed is in memo

Re: [PERFORM] sub-select in IN clause results in sequential scan

On Thu, Oct 29, 2009 at 10:10 AM, Anj Adu wrote: > Join did not help. A sequential scan is still being done. The > hardcoded value in the IN clause performs the best. The time > difference is more than an order of magnitude. If you want help debugging a performance problem, you need to post your

Re: [PERFORM] sub-select in IN clause results in sequential scan

2009/10/30 Grzegorz Jaśkiewicz : > for explains, use http://explain.depesz.com/ > besides, why are you using left join ? > equivlent of IN () is just JOIN, not LEFT JOIN. > And please, format your query so it readable without twisting eyeballs > before sending. I prefer to have things posted to th

Re: [PERFORM] Modeling a table with arbitrary columns

On Thu, Oct 29, 2009 at 4:52 PM, Andreas Hartmann wrote: > Hi everyone, > > I want to model the following scenario for an online marketing application: > > Users can create mailings. The list of recipients can be uploaded as > spreadsheets with arbitrary columns (each row is a recipient). I expect

Re: [PERFORM] Compression in PG

On Sun, Nov 1, 2009 at 11:53 AM, Shaul Dar wrote: > I am aware of the TOAST mechanism (actually complained about it in this > forum...). The text fields I have are below the limits that trigger this > mechanism, and also I may want to compress specific fields, not all of them. > And also I have pe

Re: [PERFORM] database size growing continously

On Mon, Nov 2, 2009 at 7:50 AM, Peter Meszaros wrote: > Increasing max_fsm_pages can be also helpful, but I've read that > 'vacuum verbose ...' will issue warnings if max_fsm_pages is too small. > I've never seen such messag, this command is either run and finish or > goes to an endless loop as it

Re: [PERFORM] CREATE TABLE slowing down significantly over time

On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya wrote: > We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to > schedule this weekly moving forward). > > Interesting pointer about system catalog bloat. I tried to vacuum full the > system catalog tables (pg_*), and the perfo

Re: [PERFORM] CREATE TABLE slowing down significantly over time

On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya wrote: > We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to > schedule this weekly moving forward). > > Interesting pointer about system catalog bloat. I tried to vacuum full the > system catalog tables (pg_*), and the perfo

Re: [PERFORM] random_page_cost for tablespace

2009/11/9 Laszlo Nagy : > We have a bigger table with some million rows. Number of index scans is > high, number of seq reads is low. This table if often joined with > others... so we want to buy a new SSD drive, create a tablespace on it > and put this big table on it. Random read speed on SSD is

Re: [PERFORM] CREATE TABLE slowing down significantly over time

On Mon, Nov 9, 2009 at 9:46 AM, Anj Adu wrote: > Why is reindex needed ? VACUUM FULL does not fix index bloat, only table boat. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] Why age (datfrozenxid) in postgres becomes 1073742202 not zero after each vacuum of database.

[ removing -jobs from cc list as it is not appropriate for this posting ] On Thu, Nov 12, 2009 at 3:18 AM, Brahma Prakash Tiwari wrote: > Hi all > > Why age (datfrozenxid) in postgres becomes 1073742202 not zero after vacuum > of database. > > Thanks in advance I think you're misunderstanding th

Re: [PERFORM] SSD + RAID

2009/11/14 Laszlo Nagy : > 32GB is for one table only. This server runs other applications, and you > need to leave space for sort memory, shared buffers etc. Buying 128GB memory > would solve the problem, maybe... but it is too expensive. And it is not > safe. Power out -> data loss. Huh? ...Rob

Re: [PERFORM] Is Diskeeper Automatic Mode safe?

On Mon, Nov 16, 2009 at 12:14 PM, cb wrote: > I've got a pair of servers running PostgreSQL 8.0.4 on Windows. We have > several tables that add and delete massive amounts of data in a single day > and are increasingly having a problem with drive fragmentation and it > appears to be giving us a dec

Re: [PERFORM] Is Diskeeper Automatic Mode safe?

On Mon, Nov 16, 2009 at 1:11 PM, Robert Schnabel wrote: > cb wrote: >> >> I'm curious if anyone else has used Diskeeper's Automatic Mode in >>  combination with PostgreSQL to defrag and keep the drive defragged  while >> PostgreSQL is actually running. >> >> Thanks! >> >> -chris >> >> > > I've be

Re: [PERFORM] View based upon function won't use index on joins

2009/11/20 Jonathan Foy : > Shy of just throwing a trigger in the > table to actually populate a second table with the same data solely for > reporting purposes, That's what I would do in your situation, FWIW. Query optimization is a hard problem even under the best of circumstances; getting the

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Sun, Nov 22, 2009 at 10:31 AM, Richard Neill wrote: > Dear All, > > Thanks for your help earlier with the previous question. I wonder if I might > ask another. > > > We have various queries that need to run, of which I'm going to focus on 2, > "vox" and "du_report". > > Both of them are extreme

Re: [PERFORM] Best possible way to insert and get returned ids

On Mon, Nov 23, 2009 at 3:53 PM, Jason Dictos wrote: > Is an INSERT command with a SELECT statement in the RETURNING * parameter > faster than say an INSERT and then a SELECT? Does the RETURNING * parameter > simply amount to a normal SELECT command on the added rows? We need to > basically insert

Re: [PERFORM] query optimization

On Mon, Nov 23, 2009 at 5:47 PM, Faheem Mitha wrote: > > Hi everybody, > > I've got two queries that needs optimizing. Actually, there are others, but > these are pretty representative. > > You can see the queries and the corresponding plans at > > http://bulldog.duhs.duke.edu/~faheem/snpdb/opt.pd

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Wed, Nov 25, 2009 at 7:27 AM, Richard Neill wrote: > Sergey Aleynikov wrote: >> >> Hello, >> >>> * Is there any way I can nail the query planner to a particular query >>> plan, >>> rather than have it keep changing its mind? >> >> All these setting leads to choosing different plans. If you have

Re: [PERFORM] query optimization

On Wed, Nov 25, 2009 at 5:54 PM, Faheem Mitha wrote: > > Hi Robert, > > Thanks very much for your suggestions. > >>> Hi everybody, >>> >>> I've got two queries that needs optimizing. Actually, there are others, >>> but these are pretty representative. >>> >>> You can see the queries and the corres

<    1   2   3   4   5   6   7   >