Re: Wildly erratic query performance

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Fri, Oct 31, 2008 at 1:34 PM, Eric Schwarzenbach
> <subscriber(at)blackbrook(dot)org> wrote:
>> This is postgreslq 8.3, on Windows XP. The query joins about 17 tables
>> (without an explicit JOIN, just using the WHERE criteria) with a few

> OK, whether you use join syntax or where clause syntax, postgresql can
> attempt to use the GEQO method to determine a close fit for the query
> plan. You can change these settings:

> #geqo = on
> #geqo_threshold = 12
> #geqo_effort = 5 # range 1-10

> To control the GEQO. Just crank the threshold to 20 or something so
> it doesn't kick in for now and see how long your queries take. The
> planning time will go up, because pgsql will do exhaustive logic to
> determine the best plan, but it should consistently pick a good plan.

The exhaustive search's time can be exponential in the number of tables
to be joined, so the above advice might or might not be workable. If
you find that planning takes too long when you disable geqo or bump up
the threshold, an alternative possibility is to kick up the geqo_effort
parameter to make it more likely that the randomized search will find a
decent plan.

> and look at these too:
> #from_collapse_limit = 8
> #join_collapse_limit = 8

If the query is given in the form of a "flat" FROM-list of 17 tables,
neither of those knobs will affect anything.

regards, tom lane