Quick Summary - I suggest we add the following two indexes:
CREATE INDEX dbmail_headervalue_physmsg_id on dbmail_headervalue
(physmessage_id );
CREATE INDEX dbmail_headername_lower_headername on
dbmail_headername(lower(headername));
Details:
I was getting reports of serious performance problems from some of our
users using Squirrelmail, so I took a quick look. I saw this query
popping up faily often in my PostgreSQL logs:
SELECT message_idnr,headername,headervalue FROM dbmail_headervalue v
JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id JOIN
dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr JOIN
dbmail_headername n ON v.headername_id=n.id WHERE m.mailbox_idnr = 484
AND message_idnr BETWEEN 1539669 AND 2432588 AND lower(headername) IN
('date','to','cc','from','subject','x-priority','importance','priority','content-type');
Before the indexes, this query was taking nearly two minutes to return
16 rows. After the two new Indexes, it's taking approx 1.0 seconds.
The big difference is that there was a Sequential Scan on both
lower(headername) and dbmail_headervalue.physmessage_id.
The Explain Analyze Output for this query before the new indexes was:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=274.40..357370.72 rows=537 width=97) (actual
time=65041.129..133927.163 rows=670 loops=1)
-> Index Scan using mailboxes_pkey on dbmail_mailboxes b
(cost=0.00..3.47 rows=1 width=8) (actual time=0.031..0.038 rows=1 loops=1)
Index Cond: (484 = mailbox_idnr)
-> Hash Join (cost=274.40..357361.88 rows=537 width=105) (actual
time=65041.082..133920.843 rows=670 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Hash Join (cost=75.47..357096.58 rows=12202 width=91)
(actual time=64958.937..133821.885 rows=2802 loops=1)
Hash Cond: ("outer".physmessage_id = "inner".physmessage_id)
-> Seq Scan on dbmail_headervalue v
(cost=0.00..298019.39 rows=11775939 width=83) (actual
time=0.007..77345.065 rows=11776512 loops=1)
-> Hash (cost=74.73..74.73 rows=296 width=24) (actual
time=80.875..80.875 rows=103 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx
on dbmail_messages m (cost=0.00..74.73 rows=296 width=24) (actual
time=13.484..80.347 rows=103 loops=1)
Index Cond: (mailbox_idnr = 484)
Filter: ((message_idnr >= 1539669) AND
(message_idnr <= 2432588))
-> Hash (cost=198.58..198.58 rows=139 width=30) (actual
time=82.106..82.106 rows=21 loops=1)
-> Seq Scan on dbmail_headername n (cost=0.00..198.58
rows=139 width=30) (actual time=0.075..81.985 rows=21 loops=1)
Filter: ((lower((headername)::text) =
'date'::text) OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'from'::text) OR (lower((headername)::text) = 'subject'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'importance'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'content-type'::text))
Total runtime: 133930.438 ms
(16 rows)
The Explain Analyze Output for this query with the new indexes is:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=51.68..41742.66 rows=537 width=97) (actual
time=192.164..1016.753 rows=670 loops=1)
-> Index Scan using mailboxes_pkey on dbmail_mailboxes b
(cost=0.00..3.47 rows=1 width=8) (actual time=0.047..0.053 rows=1 loops=1)
Index Cond: (484 = mailbox_idnr)
-> Hash Join (cost=51.68..41733.82 rows=537 width=105) (actual
time=192.099..1006.412 rows=670 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Nested Loop (cost=0.00..41615.76 rows=12203 width=91)
(actual time=91.344..888.840 rows=2802 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx on
dbmail_messages m (cost=0.00..74.73 rows=296 width=24) (actual
time=91.286..566.275 rows=103 loops=1)
Index Cond: (mailbox_idnr = 484)
Filter: ((message_idnr >= 1539669) AND
(message_idnr <= 2432588))
-> Index Scan using dbmail_headervalue_physmsg_id on
dbmail_headervalue v (cost=0.00..121.47 rows=1510 width=83) (actual
time=1.709..2.866 rows=27 loops=103)
Index Cond: (v.physmessage_id =
"outer".physmessage_id)
-> Hash (cost=51.33..51.33 rows=139 width=30) (actual
time=100.717..100.717 rows=21 loops=1)
-> Bitmap Heap Scan on dbmail_headername n
(cost=18.52..51.33 rows=139 width=30) (actual time=59.706..100.593
rows=21 loops=1)
Recheck Cond: ((lower((headername)::text) =
'date'::text) OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'from'::text) OR (lower((headername)::text) = 'subject'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'importance'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'content-type'::text))
-> BitmapOr (cost=18.52..18.52 rows=142 width=0)
(actual time=59.687..59.687 rows=0 loops=1)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=47.204..47.204 rows=2 loops=1)
Index Cond: (lower((headername)::text)
= 'date'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.030..0.030 rows=3 loops=1)
Index Cond: (lower((headername)::text)
= 'to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.012..0.012 rows=3 loops=1)
Index Cond: (lower((headername)::text)
= 'cc'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond: (lower((headername)::text)
= 'from'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.027..0.027 rows=3 loops=1)
Index Cond: (lower((headername)::text)
= 'subject'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=12.270..12.270 rows=3 loops=1)
Index Cond: (lower((headername)::text)
= 'x-priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.015..0.015 rows=2 loops=1)
Index Cond: (lower((headername)::text)
= 'importance'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (lower((headername)::text)
= 'priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.06 rows=16 width=0)
(actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (lower((headername)::text)
= 'content-type'::text)
Total runtime: 1020.206 ms
(34 rows)
