Aaron Stone wrote:
I get the same results for both queries, however I also get the same
EXPLAIN. I'm using MyISAM tables with the earlier 2.0 defs renamed to
dbmail_ by hand. Which database scripts did you use? I can run them on a
test database on my machine and see if there are any differences in the
indices.
I converted to dbmail_* by hand just yesterday also. I'm using postgres,
and for postgres explain differs:
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)
-> 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)
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)
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...
# postgres --version
postgres (PostgreSQL) 7.3.6
--
Best regards, Sergey Spiridonov
