While tuning an application, I found the posts from 2003 recomending the
use of LOWER() and LIKE in place of ILIKE to take advantage of indices.
For this app, given the limitations of the upper-layer protocol it must
support, that change replaced about 30 minutes of repeated seq scans with
about 1 minute of repeated index scans! On a query-set often repeated
several times per day. (Probably more times per day now.)
Is there any contraindication to recasting:
foo ILIKE 'bar'
into:
LOWER(foo) LIKE LOWER('bar')
and documenting that an index has to be on LOWER(column) to benefit ILIKE?
Perhaps the parser could read the former as the latter?
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6