Hi,
please, correct me if I am on the wrong list. I think, that everything
related to current cvs version belongs to dbmail.devel?
today I checked out current cvs version. It take some time for me to
convert tables. It works now, but fetching every single mail is very slow.
Here is a quota from statistics:
LOG: query: SELECT mailbox_idnr FROM dbmail_mailboxes WHERE
mailbox_idnr = '3' AND owner_idnr = '3'
LOG: duration: 0.004183 sec
LOG: query: SELECT pm.rfcsize FROM dbmail_physmessage pm,
dbmail_messages msg WHERE pm.id = msg.physmessage_id AND
msg.message_idnr = '551047' AND msg.status< '2' AND msg.unique_id != ''
AND msg.mailbox_idnr = '3'
LOG: duration: 0.094456 sec
LOG: query: SELECT messageblk FROM dbmail_messageblks LEFT JOIN
dbmail_messages USING (physmessage_id) WHERE
dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr
LOG: duration: 6.670869 sec
LOG: query: SELECT messageblk FROM dbmail_messageblks LEFT JOIN
dbmail_messages USING (physmessage_id) WHERE
dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr
LOG: duration: 2.088558 sec
LOG: query: SELECT seen_flag FROM dbmail_messages WHERE message_idnr =
'551047' AND status < '2' AND unique_id != '' AND mailbox_idnr = '3'
LOG: duration: 0.000868 sec
first of all, the message seems to be fetched 2 times, but the main
problem is in query: SELECT messageblk FROM dbmail_messageblks LEFT JOIN
dbmail_messages USING (physmessage_id) WHERE
dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr
which takes up to 6 seconds!!!
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)
here are table descriptions:
\d dbmail_messages Table
"public.dbmail_messages"
Column | Type | Modifiers
----------------+-----------------------+-----------------------------------------------------------
message_idnr | bigint | not null default
nextval('dbmail_message_idnr_seq'::text)
mailbox_idnr | bigint | not null default '0'
physmessage_id | bigint | not null default '0'
seen_flag | smallint | not null default '0'
answered_flag | smallint | not null default '0'
deleted_flag | smallint | not null default '0'
flagged_flag | smallint | not null default '0'
recent_flag | smallint | not null default '0'
draft_flag | smallint | not null default '0'
unique_id | character varying(70) | not null
status | smallint | not null default '0'
Indexes: dbmail_messages_pkey primary key btree (message_idnr),
dbmail_messages_mailbox_idx btree (mailbox_idnr),
dbmail_messages_physmessage_idx btree (physmessage_id),
dbmail_messages_seen_flag_idx btree (seen_flag),
dbmail_messages_status_idx btree (status),
dbmail_messages_status_notdeleted_idx btree (status) WHERE
(status < 2::smallint),
dbmail_messages_unique_id_idx btree (unique_id)
Foreign Key constraints: $3 FOREIGN KEY (physmessage_id) REFERENCES
dbmail_physmessage(id) ON UPDATE NO ACTION ON DELETE CASCADE,
$2 FOREIGN KEY (mailbox_idnr) REFERENCES
dbmail_mailboxes(mailbox_idnr) ON UPDATE NO ACTION ON DELETE CASCADE
dbmail=# \d dbmail_messageblks Table
"public.dbmail_messageblks"
Column | Type | Modifiers
-----------------+----------+--------------------------------------------------------------
messageblk_idnr | bigint | not null default
nextval('dbmail_messageblk_idnr_seq'::text)
physmessage_id | bigint | not null default '0'
messageblk | text | not null
blocksize | bigint | not null default '0'
is_header | smallint | not null default '0'
Indexes: dbmail_messageblks_pkey primary key btree (messageblk_idnr),
dbmail_messageblks_physmessage_idx btree (physmessage_id),
dbmail_messageblks_physmessage_is_header_idx btree
(physmessage_id, is_header)
Foreign Key constraints: $2 FOREIGN KEY (physmessage_id) REFERENCES
dbmail_physmessage(id) ON UPDATE NO ACTION ON DELETE CASCADE
Do I miss some important index? Thanks in advance
--
Best regards, Sergey Spiridonov
