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 <[email protected]> OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
