Обсуждение: Problem with GEQO when using views and nested selects

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

I have tried doing a variety of explicit joins but generally end up
with something a lot poorer than the result from the exhaustive
search.  I am hoping someone has some advice on how to tackle this (my
inclination is to turn of GEQO since we use this and similiarly
complex views quite a lot and with a poor plan these queries are very
slow, I would trade predictably slow query planning against
unpredictably slow queries I guess).


Anyway, Here is the view:

create view cc_users as
SELECT o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
  FROM acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr
 WHERE o.object_id = pa.party_id
   and pa.party_id = pe.person_id
   and pe.person_id = u.user_id
   and u.user_id = m.member_id
   and m.group_id = acs__magic_object_id('registered_users')
   and m.rel_id = mr.rel_id
   and m.container_id = m.group_id;


and here are the two query plans:

oatest=# set geqo_threshold to 11; explain analyze select * from cc_users u, forums_messages m where u.user_id =
m.user_idand m.message_id = 55001; 
SET VARIABLE
NOTICE:  QUERY PLAN:

Nested Loop  (cost=15202.01..19099.49 rows=1 width=1483) (actual time=6012.96..6054.26 rows=1 loops=1)
  ->  Index Scan using forums_messages_pk on forums_messages m  (cost=0.00..3.38 rows=1 width=983) (actual
time=0.06..0.08rows=1 loops=1) 
  ->  Materialize  (cost=18571.15..18571.15 rows=41997 width=500) (actual time=5996.36..6009.62 rows=42002 loops=1)
        ->  Hash Join  (cost=15202.01..18571.15 rows=41997 width=500) (actual time=4558.36..5920.36 rows=42002 loops=1)
              ->  Merge Join  (cost=0.00..3089.82 rows=42002 width=354) (actual time=0.13..651.67 rows=42002 loops=1)
                    ->  Index Scan using parties_pk on parties pa  (cost=0.00..992.58 rows=42018 width=146) (actual
time=0.05..122.78rows=42018 loops=1) 
                    ->  Index Scan using users_pk on users u  (cost=0.00..1362.17 rows=42002 width=208) (actual
time=0.03..223.07rows=42002 loops=1) 
              ->  Hash  (cost=15097.01..15097.01 rows=41997 width=146) (actual time=4558.05..4558.05 rows=0 loops=1)
                    ->  Hash Join  (cost=4639.30..15097.01 rows=41997 width=146) (actual time=1512.75..4445.08
rows=42002loops=1) 
                          ->  Seq Scan on acs_objects o  (cost=0.00..8342.17 rows=318117 width=90) (actual
time=0.03..1567.37rows=318117 loops=1) 
                          ->  Hash  (cost=4534.30..4534.30 rows=41997 width=56) (actual time=1511.87..1511.87 rows=0
loops=1)
                                ->  Hash Join  (cost=2951.31..4534.30 rows=41997 width=56) (actual time=857.33..1291.41
rows=42002loops=1) 
                                      ->  Seq Scan on persons pe  (cost=0.00..848.02 rows=42002 width=32) (actual
time=0.01..73.65rows=42002 loops=1) 
                                      ->  Hash  (cost=2846.30..2846.30 rows=42004 width=24) (actual time=856.92..856.92
rows=0loops=1) 
                                            ->  Hash Join  (cost=1318.18..2846.30 rows=42004 width=24) (actual
time=584.26..806.18rows=42002 loops=1) 
                                                  ->  Seq Scan on membership_rels mr  (cost=0.00..688.04 rows=42004
width=16)(actual time=0.01..60.95 rows=42004 loops=1) 
                                                  ->  Hash  (cost=1213.16..1213.16 rows=42009 width=8) (actual
time=583.69..583.69rows=0 loops=1) 
                                                        ->  Seq Scan on group_element_index  (cost=0.00..1213.16
rows=42009width=8) (actual time=0.05..430.06 rows=42002 loops=1) 
Total runtime: 6064.47 msec

------------------------------------------------------------

oatest=# set geqo_threshold to 15; explain analyze select * from cc_users u, forums_messages m where u.user_id =
m.user_idand m.message_id = 55001; 
SET VARIABLE
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..21.65 rows=1 width=1483) (actual time=0.42..0.44 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..18.62 rows=1 width=1451) (actual time=0.36..0.37 rows=1 loops=1)
        ->  Nested Loop  (cost=0.00..15.59 rows=1 width=1435) (actual time=0.30..0.32 rows=1 loops=1)
              ->  Nested Loop  (cost=0.00..12.54 rows=1 width=1289) (actual time=0.22..0.23 rows=1 loops=1)
                    ->  Nested Loop  (cost=0.00..9.44 rows=1 width=1199) (actual time=0.17..0.18 rows=1 loops=1)
                          ->  Nested Loop  (cost=0.00..6.41 rows=1 width=991) (actual time=0.12..0.13 rows=1 loops=1)
                                ->  Index Scan using forums_messages_pk on forums_messages m  (cost=0.00..3.38 rows=1
width=983)(actual time=0.06..0.06 rows=1 loops=1) 
                                ->  Index Scan using group_elem_idx_element_idx on group_element_index
(cost=0.00..3.02rows=1 width=8) (actual time=0.05..0.05 rows=1 loops=1) 
                          ->  Index Scan using users_pk on users u  (cost=0.00..3.02 rows=1 width=208) (actual
time=0.03..0.03rows=1 loops=1) 
                    ->  Index Scan using acs_objects_pk on acs_objects o  (cost=0.00..3.08 rows=1 width=90) (actual
time=0.03..0.03rows=1 loops=1) 
              ->  Index Scan using parties_pk on parties pa  (cost=0.00..3.04 rows=1 width=146) (actual time=0.05..0.05
rows=1loops=1) 
        ->  Index Scan using membership_rel_rel_id_pk on membership_rels mr  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.02..0.02rows=1 loops=1) 
  ->  Index Scan using persons_pk on persons pe  (cost=0.00..3.01 rows=1 width=32) (actual time=0.03..0.03 rows=1
loops=1)
Total runtime: 1.01 msec

Jeff Davis <davis@netcomuk.co.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

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

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

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

            regards, tom lane

 Tom> Jeff Davis <davis@netcomuk.co.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.