Some hard numbers:
dbmail=# explain SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr BETWEEN '1611115' AND '1611115' AND
mailbox_idnr = '5' AND status IN ('0','1') ORDER BY message_idnr ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10.96..10.97 rows=1 width=36)
Sort Key: msg.message_idnr
-> Nested Loop (cost=2.28..10.95 rows=1 width=36)
-> Bitmap Heap Scan on dbmail_messages msg (cost=2.28..6.11
rows=1 width=28)
Recheck Cond: ((mailbox_idnr = 5::bigint) AND
(message_idnr >= 1611115::bigint) AND (message_idnr <= 1611115::bigint))
Filter: ((status = 0::smallint) OR (status = 1::smallint))
-> BitmapAnd (cost=2.28..2.28 rows=1 width=0)
-> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..1.01 rows=3 width=0)
Index Cond: (mailbox_idnr = 5::bigint)
-> Bitmap Index Scan on dbmail_messages_pkey
(cost=0.00..1.02 rows=3 width=0)
Index Cond: ((message_idnr >=
1611115::bigint) AND (message_idnr <= 1611115::bigint))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage pm (cost=0.00..4.82 rows=1 width=24)
Index Cond: (pm.id = "outer".physmessage_id)
(13 rows)
dbmail=# explain SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr BETWEEN '1611115' AND '1611115' AND
mailbox_idnr = '5' AND status IN (0,1) ORDER BY message_idnr ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10.96..10.97 rows=1 width=36)
Sort Key: msg.message_idnr
-> Nested Loop (cost=2.28..10.95 rows=1 width=36)
-> Bitmap Heap Scan on dbmail_messages msg (cost=2.28..6.11
rows=1 width=28)
Recheck Cond: ((mailbox_idnr = 5::bigint) AND
(message_idnr >= 1611115::bigint) AND (message_idnr <= 1611115::bigint))
Filter: ((status = 0) OR (status = 1))
-> BitmapAnd (cost=2.28..2.28 rows=1 width=0)
-> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..1.01 rows=3 width=0)
Index Cond: (mailbox_idnr = 5::bigint)
-> Bitmap Index Scan on dbmail_messages_pkey
(cost=0.00..1.02 rows=3 width=0)
Index Cond: ((message_idnr >=
1611115::bigint) AND (message_idnr <= 1611115::bigint))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage pm (cost=0.00..4.82 rows=1 width=24)
Index Cond: (pm.id = "outer".physmessage_id)
(13 rows)
dbmail=# explain SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr = 1611115 AND
mailbox_idnr = '5' AND status < 2 ORDER BY message_idnr ASC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..9.67 rows=1 width=36)
-> Index Scan using dbmail_messages_pkey on dbmail_messages msg
(cost=0.00..4.83 rows=1 width=28)
Index Cond: (message_idnr = 1611115)
Filter: ((mailbox_idnr = 5::bigint) AND (status < 2))
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage pm
(cost=0.00..4.82 rows=1 width=24)
Index Cond: (pm.id = "outer".physmessage_id)
(6 rows)
dbmail=# explain SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag,
recent_flag, TO_CHAR(internal_date, 'YYYY-MM-DD HH24:MI:SS' ), rfcsize,
message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id =
msg.physmessage_id AND message_idnr = 1611115 AND
mailbox_idnr = '5' AND status in (0,1) ORDER BY message_idnr ASC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..9.67 rows=1 width=36)
-> Index Scan using dbmail_messages_pkey on dbmail_messages msg
(cost=0.00..4.83 rows=1 width=28)
Index Cond: (message_idnr = 1611115)
Filter: ((mailbox_idnr = 5::bigint) AND ((status = 0) OR
(status = 1)))
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage pm
(cost=0.00..4.82 rows=1 width=24)
Index Cond: (pm.id = "outer".physmessage_id)
(6 rows)
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
