Geo Carncross wrote:
> On Thu, 2005-12-15 at 17:35 +0100, Thomas Mueller wrote:
>>Geo Carncross wrote:
>>
>>>I completely agree with this. There's just the problem of databases
>>>being a moving target. Remember we talked about COUNT(*) not being
>>>optimally implemented in Pg, but one day it could be, and at that point,
>>>we should use COUNT(*) - even if it's slower on older database versions.
>>
>>That day is in the past :-) 8.1 release notes:
>>
>>Faster Aggregates: aggregate functions have been improved to make
>>reporting queries even faster. The PostgreSQL developers both rewritten
>>memory management for aggregates and added indexing optimizations for
>>MIN() and MAX().
>
> Woot!
>
> I mean, does it apply specifically to COUNT(*)? I checked the release
> notes and I couldn't find mention of it...
Sh** - you're right. I was sure I read count(*) can use an index now,
but I was wrong:
dbmail=# explain analyze select count(*) from dbmail_messageblks;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37939.09..37939.10 rows=1 width=0) (actual
time=9015.915..9015.919 rows=1 loops=1)
-> Seq Scan on dbmail_messageblks (cost=0.00..37252.27 rows=274727
width=0) (actual time=8.808..7867.205 rows=273767 loops=1)
Total runtime: 9016.105 ms
(3 rows)
>>>As a side note: I've been working on minimime recently for doing a
>>>single pass decoding of MIME message parts. It doesn't require we load
>>>the whole message into memory for parsing (!)
>>
>>That leads to a question I ask myself for a while now: wouldn't it make
>>sense to change the way messages are stored at the moment? If we would
>>split MIME messages at the boundaries IMAP searches in the body could
>>easily ignore all not readable (like Base64) parts.
>>At the moment dbmail 2.1 isn't (much) faster than 2.0 here I guess?
>
> With a mime message like this:
>
> --b1
> text
> --b1
> rfc822
> --b2
> text
> --b2--
> --b1--
>
> Perhaps a parts layout similar to this:
>
> part,pre_boundary, content, encoding, post_boundary
> '1', 'b1', 'text', 'us-ascii', NULL,
> '2', 'b1', 'rfc822', NULL, NULL,
> '2.1', 'b2', 'text', 'base64', 'b2',
> NULL, NULL, NULL, NULL, 'b2',
>
> It would allow quick access to a section, searching of only parts we
> want (discovered at insert time), and make it pretty easy to reassemble
> the message if anyone asks...
Yes something like that. That would break compatibility with 2.0 but
improve search performance a lot.
Greetings,
Thomas
