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

Problem with GEQO when using views and nested selects

From: Jeff Davis <davis(at)netcomuk(dot)co(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Problem with GEQO when using views and nested selects
Date: 2002-12-16 18:55:29
Message-ID: 15870.8609.145600.122190@test.xorch.net (view raw or flat)
Thread:
Lists: pgsql-performance
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_id and 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.08 rows=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.78 rows=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.07 rows=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=42002 loops=1)
                          ->  Seq Scan on acs_objects o  (cost=0.00..8342.17 rows=318117 width=90) (actual time=0.03..1567.37 rows=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=42002 loops=1)
                                      ->  Seq Scan on persons pe  (cost=0.00..848.02 rows=42002 width=32) (actual time=0.01..73.65 rows=42002 loops=1)
                                      ->  Hash  (cost=2846.30..2846.30 rows=42004 width=24) (actual time=856.92..856.92 rows=0 loops=1)
                                            ->  Hash Join  (cost=1318.18..2846.30 rows=42004 width=24) (actual time=584.26..806.18 rows=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.69 rows=0 loops=1)
                                                        ->  Seq Scan on group_element_index  (cost=0.00..1213.16 rows=42009 width=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_id and 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.02 rows=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.03 rows=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.03 rows=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=1 loops=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.02 rows=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


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2002-12-16 19:30:16
Subject: Re: Problem with GEQO when using views and nested selects
Previous:From: Tom LaneDate: 2002-12-16 18:34:49
Subject: Re: ~* + LIMIT => infinite time?

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