Thanks to Tom Lane The mystery is solved, here are the gory details in the original form for anyone who is interested...
I found that when postgresql switches from using doing an exhaustive search to using the genetic query optimizer the results are awful when joining agains a complex view or a nested select. I have tried tweaking the optimizer costs and the GEQO parameters but am unable to get anything sensible from the GEQO. The example where I saw this happening is with the cc_users view which works fine when queried directly but is horrible when joined against other tables.
The view
Here is the original 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;
The plan
Here is the plan for a direct query against the view:
oatest=# explain analyze select * from cc_users where user_id = 2599;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..18.26 rows=1 width=500) (actual time=0.30..0.32 rows=1 loops=1)
-> Nested Loop (cost=0.00..15.23 rows=1 width=484) (actual time=0.26..0.28 rows=1 loops=1)
-> Nested Loop (cost=0.00..12.17 rows=1 width=338) (actual time=0.21..0.22 rows=1 loops=1)
-> Nested Loop (cost=0.00..9.14 rows=1 width=330) (actual time=0.14..0.15 rows=1 loops=1)
-> Nested Loop (cost=0.00..6.05 rows=1 width=240) (actual time=0.10..0.11 rows=1 loops=1)
-> Index Scan using users_pk on users u (cost=0.00..3.02 rows=1 width=208) (actual time=0.06..0.06 rows=1 loops=1)
-> Index Scan using persons_pk on persons pe (cost=0.00..3.01 rows=1 width=32) (actual time=0.02..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 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 parties_pk on parties pa (cost=0.00..3.04 rows=1 width=146) (actual time=0.02..0.03 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)
Total runtime: 0.78 msec
The plan when joined
Joined against another table (forums_messages in this case), the plan for the query with GEQO_THRESHOLD at 11 (the normal default) is terrible:
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
but setting GEQO_THRESHOLD to 15 and running the same query produces a very fast plan:
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
Explicit joins
Trying to create a view which joins in the same order as the original simple query against the cc_users view (as per the Postgresql explicit-joins docs) results in the exhaustive search and the GEQO plan both being poor.
Here is an explicit join version of the view (I also tried several others with essentially the same results):
CREATE VIEW cc_usersx
as
SELECT o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id
FROM membership_rels mr JOIN
(parties pa JOIN
(group_member_map m JOIN
(acs_objects o JOIN
(persons pe JOIN users u
ON (pe.person_id = u.user_id))
ON (o.object_id = pe.person_id))
ON (m.member_id = u.user_id and m.container_id = m.group_id and m.group_id = acs__magic_object_id('registered_users')))
ON (pa.party_id = pe.person_id))
ON mr.rel_id = m.rel_id;
The plan for a simple query on the new view:
oatest=# explain analyze select * from cc_usersx where user_id = 2599;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..18.26 rows=1 width=500) (actual time=0.28..0.30 rows=1 loops=1)
-> Nested Loop (cost=0.00..15.23 rows=1 width=484) (actual time=0.24..0.26 rows=1 loops=1)
-> Nested Loop (cost=0.00..12.17 rows=1 width=338) (actual time=0.20..0.22 rows=1 loops=1)
-> Nested Loop (cost=0.00..9.14 rows=1 width=330) (actual time=0.12..0.14 rows=1 loops=1)
-> Nested Loop (cost=0.00..6.05 rows=1 width=240) (actual time=0.08..0.09 rows=1 loops=1)
-> Index Scan using users_pk on users u (cost=0.00..3.02 rows=1 width=208) (actual time=0.05..0.06 rows=1 loops=1)
-> Index Scan using persons_pk on persons pe (cost=0.00..3.01 rows=1 width=32) (actual time=0.02..0.02 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 group_elem_idx_element_idx on group_element_index (cost=0.00..3.02 rows=1 width=8) (actual time=0.06..0.06 rows=1 loops=1)
-> Index Scan using parties_pk on parties pa (cost=0.00..3.04 rows=1 width=146) (actual time=0.02..0.02 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)
Total runtime: 0.68 msec
Now the join against the view
oatest=# set geqo_threshold to 5; explain analyze select * from cc_usersx u, forums_messages m where u.user_id = m.user_id and m.message_id = 55001;
SET VARIABLE
NOTICE: QUERY PLAN:
Nested Loop (cost=19441.69..20361.44 rows=1 width=1483) (actual time=8536.11..8632.95 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.05..0.07 rows=1 loops=1)
-> Materialize (cost=20348.91..20348.91 rows=733 width=500) (actual time=8518.51..8532.87 rows=42002 loops=1)
-> Hash Join (cost=19441.69..20348.91 rows=733 width=500) (actual time=6891.21..8429.28 rows=42002 loops=1)
-> Seq Scan on membership_rels mr (cost=0.00..688.04 rows=42004 width=16) (actual time=0.14..73.86 rows=42004 loops=1)
-> Hash (cost=19439.86..19439.86 rows=733 width=484) (actual time=6890.95..6890.95 rows=0 loops=1)
-> Hash Join (cost=18437.43..19439.86 rows=733 width=484) (actual time=5867.34..6790.35 rows=42002 loops=1)
-> Seq Scan on parties pa (cost=0.00..783.18 rows=42018 width=146) (actual time=0.03..76.79 rows=42018 loops=1)
-> Hash (cost=18423.57..18423.57 rows=5547 width=338) (actual time=5867.15..5867.15 rows=0 loops=1)
-> Hash Join (cost=4400.27..18423.57 rows=5547 width=338) (actual time=1281.57..5681.72 rows=42002 loops=1)
-> Hash Join (cost=3082.09..16721.04 rows=42002 width=330) (actual time=945.64..4804.99 rows=42002 loops=1)
-> Seq Scan on acs_objects o (cost=0.00..8342.17 rows=318117 width=90) (actual time=0.03..1277.56 rows=318117 loops=1)
-> Hash (cost=2977.09..2977.09 rows=42002 width=240) (actual time=944.57..944.57 rows=0 loops=1)
-> Hash Join (cost=953.02..2977.09 rows=42002 width=240) (actual time=178.69..671.47 rows=42002 loops=1)
-> Seq Scan on users u (cost=0.00..1184.02 rows=42002 width=208) (actual time=0.02..168.08 rows=42002 loops=1)
-> Hash (cost=848.02..848.02 rows=42002 width=32) (actual time=178.32..178.32 rows=0 loops=1)
-> Seq Scan on persons pe (cost=0.00..848.02 rows=42002 width=32) (actual time=0.02..109.08 rows=42002 loops=1)
-> Hash (cost=1213.16..1213.16 rows=42009 width=8) (actual time=335.38..335.38 rows=0 loops=1)
-> Seq Scan on group_element_index (cost=0.00..1213.16 rows=42009 width=8) (actual time=0.12..281.89 rows=42002 loops=1)
Total runtime: 8643.21 msec
oatest=# set geqo_threshold to 15; explain analyze select * from cc_usersx u, forums_messages m where u.user_id = m.user_id and m.message_id = 55001;
SET VARIABLE
NOTICE: QUERY PLAN:
Nested Loop (cost=19441.69..20361.44 rows=1 width=1483) (actual time=8282.34..8486.87 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.05..0.07 rows=1 loops=1)
-> Materialize (cost=20348.91..20348.91 rows=733 width=500) (actual time=8264.40..8278.43 rows=42002 loops=1)
-> Hash Join (cost=19441.69..20348.91 rows=733 width=500) (actual time=6604.52..8143.48 rows=42002 loops=1)
-> Seq Scan on membership_rels mr (cost=0.00..688.04 rows=42004 width=16) (actual time=0.02..68.05 rows=42004 loops=1)
-> Hash (cost=19439.86..19439.86 rows=733 width=484) (actual time=6604.37..6604.37 rows=0 loops=1)
-> Hash Join (cost=18437.43..19439.86 rows=733 width=484) (actual time=5589.30..6510.34 rows=42002 loops=1)
-> Seq Scan on parties pa (cost=0.00..783.18 rows=42018 width=146) (actual time=0.03..76.62 rows=42018 loops=1)
-> Hash (cost=18423.57..18423.57 rows=5547 width=338) (actual time=5589.12..5589.12 rows=0 loops=1)
-> Hash Join (cost=4400.27..18423.57 rows=5547 width=338) (actual time=979.12..5440.51 rows=42002 loops=1)
-> Hash Join (cost=3082.09..16721.04 rows=42002 width=330) (actual time=650.71..4510.95 rows=42002 loops=1)
-> Seq Scan on acs_objects o (cost=0.00..8342.17 rows=318117 width=90) (actual time=0.02..1468.69 rows=318117 loops=1)
-> Hash (cost=2977.09..2977.09 rows=42002 width=240) (actual time=649.70..649.70 rows=0 loops=1)
-> Hash Join (cost=953.02..2977.09 rows=42002 width=240) (actual time=119.11..572.67 rows=42002 loops=1)
-> Seq Scan on users u (cost=0.00..1184.02 rows=42002 width=208) (actual time=0.02..173.64 rows=42002 loops=1)
-> Hash (cost=848.02..848.02 rows=42002 width=32) (actual time=118.73..118.73 rows=0 loops=1)
-> Seq Scan on persons pe (cost=0.00..848.02 rows=42002 width=32) (actual time=0.02..68.70 rows=42002 loops=1)
-> Hash (cost=1213.16..1213.16 rows=42009 width=8) (actual time=327.88..327.88 rows=0 loops=1)
-> Seq Scan on group_element_index (cost=0.00..1213.16 rows=42009 width=8) (actual time=0.04..275.85 rows=42002 loops=1)
Total runtime: 8497.10 msec
The results with and without the GEQO are now the same but are identically poor (since constraining the join order prevents the exhaustive search from finding a particularly good query (?)).
No view
The GEQO seems to do fine if there is no view:
oatest=# set GEQO_THRESHOLD TO 4; explain analyze 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, forums_messages fm
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 fm.message_id = 55001
and u.user_id = fm.message_id;
SET VARIABLE
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..21.65 rows=1 width=504) (actual time=0.08..0.08 rows=0 loops=1)
-> Nested Loop (cost=0.00..18.62 rows=1 width=472) (actual time=0.08..0.08 rows=0 loops=1)
-> Nested Loop (cost=0.00..15.59 rows=1 width=456) (actual time=0.08..0.08 rows=0 loops=1)
-> Nested Loop (cost=0.00..12.54 rows=1 width=310) (actual time=0.08..0.08 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.44 rows=1 width=220) (actual time=0.08..0.08 rows=0 loops=1)
-> Nested Loop (cost=0.00..6.41 rows=1 width=12) (actual time=0.08..0.08 rows=0 loops=1)
-> Index Scan using forums_messages_pk on forums_messages fm (cost=0.00..3.38 rows=1 width=4) (actual time=0.05..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.02..0.02 rows=0 loops=1)
-> Index Scan using users_pk on users u (cost=0.00..3.02 rows=1 width=208)
-> Index Scan using acs_objects_pk on acs_objects o (cost=0.00..3.08 rows=1 width=90)
-> Index Scan using parties_pk on parties pa (cost=0.00..3.04 rows=1 width=146)
-> Index Scan using membership_rel_rel_id_pk on membership_rels mr (cost=0.00..3.01 rows=1 width=16)
-> Index Scan using persons_pk on persons pe (cost=0.00..3.01 rows=1 width=32)
Total runtime: 0.63 msec
Nested Select rather than a view
So the query with a nested select gives the same results as using the view (my ignorance of postgresql internals prevents me from knowing if that is simply a tautology).
Nested select with GEQO:
oatest=# SET GEQO_THRESHOLD TO 11; explain analyze select * from (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) xx, forums_messages fm
where fm.message_id = 55001
and xx.user_id = fm.message_id;
SET VARIABLE
NOTICE: QUERY PLAN:
Hash Join (cost=15205.38..18784.51 rows=1 width=1483) (actual time=4702.60..4702.60 rows=0 loops=1)
-> Hash Join (cost=15202.01..18571.15 rows=41997 width=500) (actual time=3315.62..4669.50 rows=42002 loops=1)
-> Merge Join (cost=0.00..3089.82 rows=42002 width=354) (actual time=0.11..643.22 rows=42002 loops=1)
-> Index Scan using parties_pk on parties pa (cost=0.00..992.58 rows=42018 width=146) (actual time=0.04..121.62 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..219.36 rows=42002 loops=1)
-> Hash (cost=15097.01..15097.01 rows=41997 width=146) (actual time=3315.35..3315.35 rows=0 loops=1)
-> Hash Join (cost=4639.30..15097.01 rows=41997 width=146) (actual time=943.69..3241.24 rows=42002 loops=1)
-> Seq Scan on acs_objects o (cost=0.00..8342.17 rows=318117 width=90) (actual time=0.02..1256.51 rows=318117 loops=1)
-> Hash (cost=4534.30..4534.30 rows=41997 width=56) (actual time=942.80..942.80 rows=0 loops=1)
-> Hash Join (cost=2951.31..4534.30 rows=41997 width=56) (actual time=618.70..883.17 rows=42002 loops=1)
-> Seq Scan on persons pe (cost=0.00..848.02 rows=42002 width=32) (actual time=0.01..72.56 rows=42002 loops=1)
-> Hash (cost=2846.30..2846.30 rows=42004 width=24) (actual time=618.33..618.33 rows=0 loops=1)
-> Hash Join (cost=1318.18..2846.30 rows=42004 width=24) (actual time=348.46..567.59 rows=42002 loops=1)
-> Seq Scan on membership_rels mr (cost=0.00..688.04 rows=42004 width=16) (actual time=0.01..60.63 rows=42004 loops=1)
-> Hash (cost=1213.16..1213.16 rows=42009 width=8) (actual time=347.90..347.90 rows=0 loops=1)
-> Seq Scan on group_element_index (cost=0.00..1213.16 rows=42009 width=8) (actual time=0.05..296.17 rows=42002 loops=1)
-> Hash (cost=3.38..3.38 rows=1 width=983) (actual time=0.07..0.07 rows=0 loops=1)
-> Index Scan using forums_messages_pk on forums_messages fm (cost=0.00..3.38 rows=1 width=983) (actual time=0.06..0.06 rows=1 loops=1)
Total runtime: 4703.15 msec
Nested select without GEQO:
oatest=# SET GEQO_THRESHOLD TO 20; explain analyze select * from (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) xx, forums_messages fm
where fm.message_id = 55001
and xx.user_id = fm.message_id;
SET VARIABLE
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..21.65 rows=1 width=1483) (actual time=0.09..0.09 rows=0 loops=1)
-> Nested Loop (cost=0.00..18.62 rows=1 width=1451) (actual time=0.09..0.09 rows=0 loops=1)
-> Nested Loop (cost=0.00..15.59 rows=1 width=1435) (actual time=0.09..0.09 rows=0 loops=1)
-> Nested Loop (cost=0.00..12.54 rows=1 width=1289) (actual time=0.09..0.09 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.44 rows=1 width=1199) (actual time=0.09..0.09 rows=0 loops=1)
-> Nested Loop (cost=0.00..6.41 rows=1 width=991) (actual time=0.09..0.09 rows=0 loops=1)
-> Index Scan using forums_messages_pk on forums_messages fm (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.02..0.02 rows=0 loops=1)
-> Index Scan using users_pk on users u (cost=0.00..3.02 rows=1 width=208)
-> Index Scan using acs_objects_pk on acs_objects o (cost=0.00..3.08 rows=1 width=90)
-> Index Scan using parties_pk on parties pa (cost=0.00..3.04 rows=1 width=146)
-> Index Scan using membership_rel_rel_id_pk on membership_rels mr (cost=0.00..3.01 rows=1 width=16)
-> Index Scan using persons_pk on persons pe (cost=0.00..3.01 rows=1 width=32)
Total runtime: 0.70 msec
Mystery Solved
Through the magic of open source and the internet, the mystery was solved by Tom Lane. The very short answer is that it is not the GEQO at all, rather it is the interpretation of GEQO_THRESHOLD within the code. Says Tom Lane on pgsql-performance:
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
Last modified: Mon Dec 16 17:33:07 EST 2002