Looking at stats for my pg dbmail, I found that the query: SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name ILIKE ? AND owner_idnr=?
was particularly expensive. As it turns out, the only way to do that in pg and take advantage of an index is to write it as: SELECT mailbox_idnr FROM dbmail_mailboxes WHERE lower(name) LIKE lower(?) AND owner_idnr=? Provided, of course, that an index like: "dbmail_mailboxes_name_owner_idnr_idx" btree (owner_idnr, lower(name)) exists. I had that index, but it is of no value when the queries use ILIKE. The db_get_sql(SQL_INSENSITIVE_LIKE) usage will require surgery to make that work. This change also is needed on the dbmail_3_0 and master branches. With pg9.2 and the above index, and with about 40k rows, the index scan is around 700 times faster in terms of explain's "cost" and about 200 to 300 times faster in terms of total runtime than the ILIKE sequential scan. That could be a significant advantage on busy databases. -JimC -- James Cloos <[email protected]> OpenPGP: 1024D/ED7DAEA6 _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
