Geo Carncross wrote:
> DROP the index for dbmail_messages_physmessage_idx - I think Pg is
> selecting it because it has less collisions, the other one just isn't
> being used. I'd like an explain of it AFTER the index drop too (remember
> to REINDEX and do a VACUUM ANALYZE)
Sorry this will be little longer. To be sure I got everything right I'll
write in detail what I did.
This is where I started (list of indizes):
public | dbmail_messages_mailbox_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_physmessage_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_pkey | index | dbmail
| dbmail_messages
public | dbmail_messages_seen_flag_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_status_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_status_notdeleted_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_tmm_status_recent_idx | index | dbmail
| dbmail_messages
public | dbmail_messages_unique_id_idx | index | dbmail
| dbmail_messages
Where dbmail_messages_tmm_status_recent_idx = mailbox_idnr, status,
recent_flag
VACUUM ANALYZE;
explain analyze SELECT a.seen_flag, a.answered_flag, a.deleted_flag,
a.flagged_flag, a.draft_flag, a.recent_flag, b.internaldate, b.rfcsize,
a.message_idnr FROM (SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag, recent_flag, message_idnr, physmessage_id AS
id FROM dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND
mailbox_idnr = '81' AND (status = '0' OR status = '1')) AS a JOIN
(SELECT id, internal_date AS internaldate, rfcsize FROM
dbmail_physmessage WHERE id IN (SELECT physmessage_id FROM
dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND
mailbox_idnr = '81' AND (status = '0' OR status = '1'))) AS b USING (id);
QUERY PLAN
----------
Nested Loop (cost=6467.78..9818.32 rows=8 width=36) (actual
time=636.215..14318.093 rows=1827 loops=1) -> Nested Loop
(cost=6467.78..7955.77 rows=467 width=32) (actual
time=617.371..13171.764 rows=1827 loops=1)
-> HashAggregate (cost=6467.78..6467.78 rows=467 width=8)
(actual time=553.705..578.470 rows=1827 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx on
dbmail_messages (cost=0.00..6463.54 rows=1699 width=8) (actual
time=17.364..544.043 rows=1827 loops=1)
Index Cond: (mailbox_idnr = 81::bigint)
Filter: ((message_idnr >= 1::bigint) AND
(message_idnr <= 417966::bigint) AND ((status = 0::smallint) OR (status
= 1::smallint)))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage (cost=0.00..3.17 rows=1 width=24) (actual
time=6.862..6.869 rows=1 loops=1827)
Index Cond: (dbmail_physmessage.id = "outer".physmessage_id)
-> Index Scan using dbmail_messages_physmessage_idx on
dbmail_messages (cost=0.00..3.98 rows=1 width=28) (actual
time=0.588..0.601 rows=1 loops=1827)
Index Cond: (dbmail_messages.physmessage_id =
"outer".physmessage_id)
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint) AND (mailbox_idnr = 81::bigint) AND ((status =
0::smallint) OR (status = 1::smallint)))
Total runtime: 14326.437 ms
DROP INDEX dbmail_messages_physmessage_idx;
CREATE INDEX dbmail_messages_wide ON dbmail_messages(mailbox_idnr,
message_idnr, status);
CREATE INDEX dbmail_messages_wider ON dbmail_messages(mailbox_idnr,
message_idnr, status, physmessage_id);
REINDEX TABLE dbmail_messages;
VACUUM ANALYZE;
explain analyze SELECT a.seen_flag, a.answered_flag, a.deleted_flag,
a.flagged_flag, a.draft_flag, a.recent_flag, b.internaldate, b.rfcsize,
a.message_idnr FROM (SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag, recent_flag, message_idnr, physmessage_id AS
id FROM dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND
mailbox_idnr = '81' AND (status = '0' OR status = '1')) AS a JOIN
(SELECT id, internal_date AS internaldate, rfcsize FROM
dbmail_physmessage WHERE id IN (SELECT physmessage_id FROM
dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND
mailbox_idnr = '81' AND (status = '0' OR status = '1'))) AS b USING (id);
QUERY PLAN
----------
Hash Join (cost=7875.69..13937.33 rows=9 width=36) (actual
time=14235.767..14269.361 rows=1827 loops=1)
Hash Cond: ("outer".physmessage_id = "inner".id)
-> Index Scan using dbmail_messages_mailbox_idx on dbmail_messages
(cost=0.00..6053.64 rows=1581 width=28) (actual time=0.052..13.376
rows=1827 loops=1)
Index Cond: (mailbox_idnr = 81::bigint)
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint) AND ((status = 0::smallint) OR (status = 1::smallint)))
-> Hash (cost=7874.26..7874.26 rows=572 width=32) (actual
time=14235.648..14235.648 rows=0 loops=1) -> Nested Loop
(cost=6057.60..7874.26 rows=572 width=32) (actual
time=741.976..14223.254 rows=1827 loops=1)
-> HashAggregate (cost=6057.60..6057.60 rows=572
width=8) (actual time=684.463..708.010 rows=1827 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx on
dbmail_messages (cost=0.00..6053.64 rows=1581 width=8) (actual
time=9.096..674.740 rows=1827 loops=1)
Index Cond: (mailbox_idnr = 81::bigint)
Filter: ((message_idnr >= 1::bigint) AND
(message_idnr <= 417966::bigint) AND ((status = 0::smallint) OR (status
= 1::smallint)))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage (cost=0.00..3.16 rows=1 width=24) (actual
time=7.363..7.372 rows=1 loops=1827)
Index Cond: (dbmail_physmessage.id =
"outer".physmessage_id)
Total runtime: 14276.660 ms
Different plans, same runtime. But Pg seems not to want your indizes :-)
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
