Sergey Spiridonov wrote:
> I converted to dbmail_* by hand just yesterday also. I'm using
> postgres, and for postgres explain differs:
This is for Postgres 7.4.2:
> dbmail=# explain SELECT messageblk FROM dbmail_messageblks LEFT JOIN
> dbmail_messages USING (physmessage_id) WHERE
> dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr;
> QUERY PLAN
> -------------------------------------------------------------------------------------------
>
>
> Sort (cost=71153.35..71246.10 rows=37101 width=758)
> Sort Key: dbmail_messageblks.messageblk_idnr
> -> Hash Join (cost=21564.50..36827.54 rows=37101 width=758)
> Hash Cond: ("outer".physmessage_id = "inner".physmessage_id)
> Filter: ("inner".message_idnr = 551047::bigint)
> -> Seq Scan on dbmail_messageblks (cost=0.00..3764.01
> rows=37101 width=742)
Ouch! A Seq Scan on 37000 rows? There should be an index I think?
> -> Hash (cost=16381.40..16381.40 rows=702040 width=16)
> -> Seq Scan on dbmail_messages (cost=0.00..16381.40
> rows=702040 width=16)
> (8 rows)
Sort (cost=35.95..35.97 rows=7 width=863)
Sort Key: messageblks.messageblk_idnr
-> Nested Loop (cost=0.00..35.85 rows=7 width=863)
-> Index Scan using messages_pkey on messages
(cost=0.00..4.77 rows=2 width=8)
Index Cond: (message_idnr = 1::bigint)
-> Index Scan using messageblks_physmessage_idx on messageblks
(cost=0.00..15.50 rows=3 width=871)
Index Cond: (messageblks.physmessage_id =
"outer".physmessage_id)
Completly different query plan.
> dbmail=# explain SELECT messageblk FROM dbmail_messageblks,
> dbmail_messages WHERE dbmail_messageblks.physmessage_id =
> dbmail_messages.physmessage_id AND dbmail_messages.message_idnr =
> '551047' ORDER BY messageblk_idnr;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------
>
>
> Sort (cost=8.58..8.59 rows=1 width=750)
> Sort Key: dbmail_messageblks.messageblk_idnr
> -> Nested Loop (cost=0.00..8.57 rows=1 width=750)
> -> Index Scan using dbmail_messages_pkey on dbmail_messages
> (cost=0.00..3.08 rows=1 width=8)
> Index Cond: (message_idnr = 551047::bigint)
> -> Index Scan using dbmail_messageblks_physmessage_idx on
> dbmail_messageblks (cost=0.00..5.45 rows=4 width=742)
> Index Cond: (dbmail_messageblks.physmessage_id =
> "outer".physmessage_id)
> (7 rows)
Sort (cost=35.95..35.97 rows=7 width=863)
Sort Key: messageblks.messageblk_idnr
-> Nested Loop (cost=0.00..35.85 rows=7 width=863)
-> Index Scan using messages_pkey on messages
(cost=0.00..4.77 rows=2 width=8)
Index Cond: (message_idnr = 1::bigint)
-> Index Scan using messageblks_physmessage_idx on messageblks
(cost=0.00..15.50 rows=3 width=871)
Index Cond: (messageblks.physmessage_id =
"outer".physmessage_id)
> Seems to be postgresql problem. This query is used in 3 places:
> 2 times in dbmsgbuf.c and 1 time in dbsearch.c. I changed them and
> till now everything works fine...
Normally an explicit join is better for the query planer.
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
