Re: Query performance over a large proportion of data

[Steve McLellan <smclellan@xxxxxxxxxx>]

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