So people looking for their INBOX end up forcing a full table scan to find out
when lower( name ) = lower( 'INBOX' ), when in fact 99% of the time, the value
of column name => "INBOX" anyways.
Changing the query from lower( '%s' ) to lower( 'inbox' ) is basically a
micro-optimization because INBOX gets snprintf'd into the query, and becomes
lower( 'INBOX' ) which should evaluate immediately and only once in SQL.
The bigger concern is that because we haven't indexed the table in lowercase,
like you suggested earlier, and because lowercase 'inbox' doesn't appear to
begin with, there's a table scan. Try changing it to upper( name ) = upper(
'%s' ) and I bet it'll stop doing table scans because INBOX, in all uppercase,
will be in the closest index, which is on unmodified name itself (unless
PostgreSQL is too smart to look into the "wrong" index, and it may well be).
Aaron
Thomas Mueller <[EMAIL PROTECTED]> said:
> Hi Aaron,
>
> > In CVS, lower() has been removed from all mailbox queries to allow for
> > case sensitive mailbox names (as is common for Unix filesystem based
> > mailers).
>
> Nope. The lower() has been removed from every mailbox except for the
> INBOX. According to the RFC the INBOX has to be case insensitive.
>
> db.c (we should change the query to WHERE LOWER(name) = 'inbox'):
> ----------------------------------------------------------------------
> /* if we check the INBOX, we need to be case insensitive */
> if (strncasecmp(name, "INBOX", 5) == 0) {
> snprintf(query, DEF_QUERYSIZE,
> "SELECT mailbox_idnr FROM mailboxes "
> "WHERE LOWER(name) = LOWER('%s') "
> "AND owner_idnr='%llu'", name, owner_idnr);
> } else {
> snprintf(query, DEF_QUERYSIZE,
> "SELECT mailbox_idnr FROM mailboxes "
> "WHERE name='%s' AND owner_idnr='%llu'", name,
> owner_idnr);
> }
> ----------------------------------------------------------------------
>
>
> Thomas
> --
> http://www.tmueller.com for pgp key (95702B3B)
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
--
