Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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:

 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

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

In response to

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group