Hmm. Seems there might be a quick solution for a least some speedup. At least with pg 9.
Try this: create extension pg_trgm; create index dbmail_mailboxes_name_gin_idx on dbmail_mailboxes using gin (name gin_trgm_ops); and then: SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name ILIKE ? and owner_idnr=? is about 20 times faster than w/o the trgm index. Roughly the geometric mean of current and optimal. If you have a lot of rows in dmail_mailboxes that trgm index will eventually become too expensive to maintain. But perhaps not before millions of rows. A fix to use LIKE and LOWER instead of ILIKE is still the best solution. -JimC -- James Cloos <[email protected]> OpenPGP: 1024D/ED7DAEA6 _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
