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