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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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