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 l
