Re: Problem with GEQO when using views and nested selects

From: Jeff Davis <davis(at)netcomuk(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Problem with GEQO when using views and nested selects
Date: 2002-12-16 21:47:17
Message-ID: 15870.18917.306678.855332@test.xorch.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Tom> Jeff Davis <davis(at)netcomuk(dot)co(dot)uk> writes:
>> I have been trying tune joins against a view we use a lot for which
>> the optimizer generates very poor query plans when it uses the GEQO.
>> The long involved version (and more readable version) of the problem
>> is here: http://xarg.net/writing/misc/GEQO

Tom> This is not actually using GEQO. The reason you are seeing an effect
Tom> from raising geqo_threshold is that geqo_threshold determines whether
Tom> or not the view will be flattened into the upper query. For this
Tom> particular query situation, flattening the view is essential (since you
Tom> don't want the thing to compute the whole view). The relevant source
Tom> code tidbit is

Tom> /*
Tom> * Yes, so do we want to merge it into parent? Always do
Tom> * so if child has just one element (since that doesn't
Tom> * make the parent's list any longer). Otherwise we have
Tom> * to be careful about the increase in planning time
Tom> * caused by combining the two join search spaces into
Tom> * one. Our heuristic is to merge if the merge will
Tom> * produce a join list no longer than GEQO_RELS/2.
Tom> * (Perhaps need an additional user parameter?)
Tom> */

Tom> AFAICS, your only good solution is to make geqo_threshold at least 14,
Tom> since you want a 7-way join after flattening.

Thanks very much. I have to admit it was all very mysterious to me
and the only knobs I had seemed to indicate that the GEQO was the
issue.

I think having another user parameter as mentioned in the comment is a
good idea (although I see it's been discussed before), that or maybe
some better guidance on the actual interpretation of GEQO_THRESHOLD
(the comment is hugely more illuminating than the documentation on
this point).

Now that I understand what is going on, I know in our case this crops
up a fair bit and no one had really figured ever figured out what was
causing views to work ok some of the time and then fall over in other
queries.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2002-12-17 04:03:51 Profiling
Previous Message Tom Lane 2002-12-16 19:30:16 Re: Problem with GEQO when using views and nested selects