Paul J Stevens wrote:
> Geo Carncross wrote:
>
>> 'q',COUNT(*)
>>
>> as the request for each. This was a minor note. The result would then be
>> three separate rows, second column being the interesting part.
>
> Fixing the query thus, and adding the needed indexes result in *really*
> fast returns on mysql, comparable with the SUM() version.
>
> Could someone please check/verify this for pgsql?? I don't have a dbmail
> installation for pgsql with a significant amount of mail in it...
I'm really astonished - impressive!
I only created one index:
CREATE INDEX dbmail_messages_8 ON dbmail_messages
(mailbox_idnr,status,recent_flag);
Sorry this will look ugly but if someone is interested:
Unique (cost=25868.81..25868.83 rows=3 width=0) (actual
time=546.688..546.721 rows=3 loops=1)
-> Sort (cost=25868.81..25868.82 rows=3 width=0) (actual
time=546.678..546.689 rows=3 loops=1)
Sort Key: count
-> Append (cost=8619.40..25868.79 rows=3 width=0) (actual
time=532.167..546.535 rows=3 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=8619.40..8619.41
rows=1 width=0) (actual time=532.158..532.169 rows=1 loops=1)
-> Aggregate (cost=8619.40..8619.40 rows=1
width=0) (actual time=532.147..532.150 rows=1 loops=1)
-> Index Scan using dbmail_messages_8 on
dbmail_messages (cost=0.00..8613.72 rows=2270 width=0) (actual
time=0.140..528.864 rows=611 loops=1)
Index Cond: (mailbox_idnr = 5::bigint)
Filter: ((status = 0::smallint) OR
(status = 1::smallint))
-> Subquery Scan "*SELECT* 2" (cost=8624.33..8624.34
rows=1 width=0) (actual time=7.688..7.700 rows=1 loops=1)
-> Aggregate (cost=8624.33..8624.33 rows=1
width=0) (actual time=7.678..7.682 rows=1 loops=1)
-> Index Scan using dbmail_messages_8 on
dbmail_messages (cost=0.00..8619.48 rows=1937 width=0) (actual
time=0.053..4.886 rows=610 loops=1)
Index Cond: (mailbox_idnr = 5::bigint)
Filter: (((status = 0::smallint) OR
(status = 1::smallint)) AND (seen_flag = 1))
-> Subquery Scan "*SELECT* 3" (cost=8625.03..8625.04
rows=1 width=0) (actual time=6.615..6.626 rows=1 loops=1)
-> Aggregate (cost=8625.03..8625.03 rows=1
width=0) (actual time=6.605..6.608 rows=1 loops=1)
-> Index Scan using dbmail_messages_8 on
dbmail_messages (cost=0.00..8619.48 rows=2217 width=0) (actual
time=0.205..4.244 rows=573 loops=1)
Index Cond: (mailbox_idnr = 5::bigint)
Filter: (((status = 0::smallint) OR
(status = 1::smallint)) AND (recent_flag = 1))
Total runtime: 566.370 ms
Paul, my apt-get will run in an endless loop from now on ;-)
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
