Problem with GEQO when using views and nested selects - Mailing list 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_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