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