Re: Wildly erratic query performance

On Fri, Oct 31, 2008 at 1:34 PM, Eric Schwarzenbach
<subscriber(at)blackbrook(dot)org> wrote:
> I've got a particular query that is giving me ridiculously erratic query
> performance. I have the SQL in a pgadmin query window, and from one
> execution to another, with no changes, the time it takes varies from

SNIP

> 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.

and look at these too:

#from_collapse_limit = 8
#join_collapse_limit = 8