Aaron Stone wrote:
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).
Isn't the simplest solution this:
change the query for the special case of 'INBOX' to:
SELECT mailbox_idnr FROM mailboxes WHERE name = 'INBOX' and owner_idnr =
'%llu';
We always store 'INBOX' as 'INBOX' (all caps), so, this will always
work. And we have no problem with indexes this way.
It just too simple probably.. Perhaps I'm overlooking something?
Ilja
