Re: Query performance over a large proportion of data - Mailing list pgsql-performance

From Steve McLellan
Subject Re: Query performance over a large proportion of data
Date
Msg-id cfca83d70903102030h3fdff9f6n6e55b4161a216a56@mail.gmail.com
Whole thread Raw
In response to Query performance over a large proportion of data  ("Steve McLellan" <smclellan@mintel.com>)
List pgsql-performance



"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
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 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.

Reducing seq_page_cost relative to random_page_cost seems to make an enormous difference for this query. Removing the nested loop seems to be what makes a difference. We'll continue to play with these and check there are no adverse effects on other queries.

Thanks again, Steve

pgsql-performance by date:

Previous
From: Steve McLellan
Date:
Subject: Re: Query performance over a large proportion of data
Next
From: Scott Marlowe
Date:
Subject: Re: Query performance over a large proportion of data