It's not necessarily the join order that's an issue; it could also be
due to the merge join that it does in the first case. I've also run into
situations where the cost estimate for a merge join is way off the mark.
Rather than forcing the join order, you might try setting
enable_mergejoin=false.
On Mon, Feb 19, 2007 at 06:03:22PM +0100, Reinhard Vicinus wrote:
PostgreSQL version: 8.1.6
OS: Debian etch
The following query needs a lot of time because the query planner
reorders the joins:
select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
join dbmail_headername n ON v.headername_id=n.id
where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
and lower(n.headername) IN
('from','to','cc','subject','date','message-id',
'priority','x-priority','references','newsgroups','in-reply-to',
'content-type','x-spam-status','x-spam-flag');
If I prevent the query planner from reordering the joins with 'set
join_collapse_limit=1;' then the same query is faster. At the end of the
Mail is the output of a explain analyze for both cases.
The statistics of the database are updated each night. Is there an error
(in the statistical data) which is responsible for the reordering of the
joins? And if not are there other alternatives for preventing join
reordering?
Thanks
Reinhard
Explain analyze with set join_collapse_limit=8:
Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
time=82677.950..89103.192 rows=2699 loops=1)
Merge Cond: (outer.physmessage_id = inner.physmessage_id)
- Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
time=247.238..247.578 rows=373 loops=1)
Sort Key: m.physmessage_id
- Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
Recheck Cond: (mailbox_idnr = 1022)
Filter: ((message_idnr = 698928) AND (message_idnr =
1496874))
- Bitmap Index Scan on dbmail_messages_8
(cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
loops=1)
Index Cond: (mailbox_idnr = 1022)
- Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
time=80156.731..85760.186 rows=3278076 loops=1)
Sort Key: v.physmessage_id
- Hash Join (cost=51.00..285787.17 rows=533108 width=127)
(actual time=34.519..28260.855 rows=3370242 loops=1)
Hash Cond: (outer.headername_id = inner.id)
- Seq Scan on dbmail_headervalue v
(cost=0.00..241200.39 rows=7840939 width=115) (actual
time=0.006..16844.479 rows=7854485 loops=1)
- Hash (cost=50.72..50.72 rows=113 width=28) (actual
time=34.493..34.493 rows=35 loops=1)
- Bitmap Heap Scan on dbmail_headername n
(cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
rows=35 loops=1)
Recheck Cond: ((lower((headername)::text) =
'from'::text) OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
(lower((headername)::text) = 'message-id'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'references'::text) OR
(lower((headername)::text) = 'newsgroups'::text) OR
(lower((headername)::text) = 'in-reply-to'::text) OR
(lower((headername)::text) = 'content-type'::text) OR
(lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
dername)::text) = 'x-spam-flag'::text))
- BitmapOr (cost=28.44..28.44 rows=116
width=0) (actual time=11.786..11.786 rows=0 loops=1)
- Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.037..0.037 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'from'::text)
- Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'to'::text)
- Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'cc'::text)
- Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'subject'::text)
