Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Ilja Booij
Thomas Mueller wrote: Hi Ilja, Aha, it wasn't as simple as I thought... The 'if' isn't right, because strcasecmp only looks at the first 5 characters, and you just gave the perfect example why this does not work. What we can do is the following: keep the 'if' as it is. Convert the first 5

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Thomas Mueller
Hi Ilja, > >strncasecmp() tests if the folder begins with INBOX and thats always > >true - right? So we have to use strcasecmp(name, "INBOX") right? > >Confused ... > > Aha, it wasn't as simple as I thought... The 'if' isn't right, because > strcasecmp only looks at the first 5 characters, and y

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Ilja Booij
Thomas Mueller wrote: Hi Ilja, 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

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Thomas Mueller
Hi Ilja, > 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 w

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Ilja Booij
Aaron Stone wrote: Ilja Booij <[EMAIL PROTECTED]> said: [snip] 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 pro

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Aaron Stone
Ilja Booij <[EMAIL PROTECTED]> said: [snip] > > 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 wi

Re: [Dbmail-dev] Missing postgres index

2004-06-01 Thread Ilja Booij
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-optim

Re: [Dbmail-dev] Missing postgres index

2004-05-31 Thread Aaron Stone
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

Re: [Dbmail-dev] Missing postgres index

2004-05-31 Thread Thomas Mueller
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

Re: [Dbmail-dev] Missing postgres index

2004-05-31 Thread Aaron Stone
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). Not sure if this behaviour has been changed in 1.2.x, though. Aaron Thomas Mueller <[EMAIL PROTECTED]> said: > Hi, > > I had 800.000 SeqScans o

[Dbmail-dev] Missing postgres index

2004-05-31 Thread Thomas Mueller
Hi, I had 800.000 SeqScans on the mailboxes table in 5 days. The only explanation I have is this missing index: CREATE INDEX mailboxes_name_low_idx ON mailboxes(lower(name)); The INBOX is searched using lower(name). Thomas -- http://www.tmueller.com for pgp key (95702B3B)