Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Scott Marlowe
On Tue, Mar 10, 2009 at 9:15 PM, Steve McLellan wrote: > Thanks - the nested loop is indeed causing problems - reducing > seq_page_cost had the same effect of removing the nested loop for this > query. We'd noticed the poor row count estimation. Increasing the statistics > doesn't seem to have muc

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Steve McLellan
> > > > *"Kevin Grittner" * > 03/10/2009 05:06 PM EST > > > enable_seqscan = off > > Not a good idea; some queries will optimize better with seqscans. > You can probably get the behavior you want using other adjustments. > The bullet to cure the headache, as Scott said. > > You probably need to re

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Steve McLellan
> > > *Scott Marlowe * > 03/10/2009 05:19 PM > > > > > Nested Loop (cost=466.34..192962.24 rows=15329 width=12) (actual > > time=13653.238..31332.113 rows=131466 loops=1) > > > Both your query plans end with this nested loop join which is taking > up about half your time in your query. Notice th

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Steve McLellan
> > > > *Tom Lane * > Sent by: pgsql-performance-ow...@postgresql.org > 03/10/2009 08:16 PM AST > > "Steve McLellan" writes: > > lc_messages = 'en_US.UTF-8' > > lc_monetary = 'en_US.UTF-8' > > lc_numeric = 'en_US.UTF-8' > > lc_time = 'en_US.UTF-8' > > BTW, aside from the points already made: the a

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Tom Lane
"Steve McLellan" writes: > lc_messages = 'en_US.UTF-8' > lc_monetary = 'en_US.UTF-8' > lc_numeric = 'en_US.UTF-8' > lc_time = 'en_US.UTF-8' BTW, aside from the points already made: the above indicates that you initialized your database in en_US.utf8 locale. This is not necessarily a good decisio

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Tom Lane
"Kevin Grittner" writes: > You probably need to reduce random_page_cost. If your caching is > complete enough, you might want to set it equal to seq_page_cost > (never set it lower that seq_page_cost!) and possibly reduce both of > these to 0.1. > Some people have had good luck with boosting cp

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Scott Marlowe
On Tue, Mar 10, 2009 at 3:12 PM, Steve McLellan wrote: > >  Nested Loop  (cost=466.34..192962.24 rows=15329 width=12) (actual > time=13653.238..31332.113 rows=131466 loops=1) Both your query plans end with this nested loop join which is taking up about half your time in your query. Notice the e

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Kevin Grittner
>>> "Steve McLellan" wrote: > The server itself is a dual-core 3.7GHz Xeon Dell (each core > reporting 2 logical CPUs) running an amd64 build of FreeBSD 6.2, and > postgres 8.3.5 built from source. It's got 400GB storage in RAID-5 > (on 5 disks). It has 8GB of physical RAM. I'm able to use about

Re: [PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Scott Marlowe
On Tue, Mar 10, 2009 at 3:12 PM, Steve McLellan wrote: > Hi, > > I'd be grateful for any advice we can get... we recently switched from MySQL > to PostgreSQL on the basis of some trials we carried out with datasets of > varying sizes and varying rates of contention. For the most part we've been >

[PERFORM] Query performance over a large proportion of data

2009-03-10 Thread Steve McLellan
Hi, I'd be grateful for any advice we can get... we recently switched from MySQL to PostgreSQL on the basis of some trials we carried out with datasets of varying sizes and varying rates of contention. For the most part we've been pleased with performance, but one particular application runs queri

Re: [PERFORM] When does sequential performance matter in PG?

2009-03-10 Thread Scott Carey
On 3/10/09 6:28 AM, "Matthew Wakeling" wrote: On Tue, 10 Mar 2009, henk de wit wrote: > It is frequently said that for PostgreSQL the number 1 thing to pay > attention to when increasing performance is the amount of IOPS a storage > system is capable of. Now I wonder if there is any situation

Re: [PERFORM] When does sequential performance matter in PG?

2009-03-10 Thread Greg Smith
On Tue, 10 Mar 2009, henk de wit wrote: Now I wonder if there is any situation in which sequential IO performance comes into play. E.g. perhaps during a tablescan on a non-fragmented table, or during a backup or restore? If you're doing a sequential scan of data that was loaded in a fairly l

Re: [PERFORM] Query much slower when run from postgres function

2009-03-10 Thread Tom Lane
f...@redhat.com (Frank Ch. Eigler) writes: > For a prepared statement, could the planner produce *several* plans, > if it guesses great sensitivity to the parameter values? Then it > could choose amongst them at run time. We've discussed that in the past. "Choose at runtime" is a bit more easily

Re: [PERFORM] When does sequential performance matter in PG?

2009-03-10 Thread henk de wit
Hi, > On Tue, 10 Mar 2009, henk de wit wrote: > > Now I wonder if there is any situation in which > > sequential IO performance comes into play. E.g. perhaps during a > > tablescan on a non-fragmented table, or during a backup or restore? > > Yes, up to a point. That point is when a single CPU

Re: [PERFORM] Query much slower when run from postgres function

2009-03-10 Thread Frank Ch. Eigler
Tom Lane writes: > Mario Splivalo writes: >> Now I'm confused, why is 'sql' function much slower than 'direct' SELECT? > > Usually the reason for this is that the planner chooses a different plan > when it has knowledge of the particular value you are searching for than > when it does not. I su

Re: [PERFORM] When does sequential performance matter in PG?

2009-03-10 Thread Matthew Wakeling
On Tue, 10 Mar 2009, henk de wit wrote: It is frequently said that for PostgreSQL the number 1 thing to pay attention to when increasing performance is the amount of IOPS a storage system is capable of. Now I wonder if there is any situation in which sequential IO performance comes into play. E

[PERFORM] When does sequential performance matter in PG?

2009-03-10 Thread henk de wit
Hi, It is frequently said that for PostgreSQL the number 1 thing to pay attention to when increasing performance is the amount of IOPS a storage system is capable of. Now I wonder if there is any situation in which sequential IO performance comes into play. E.g. perhaps during a tablescan on a