ILIKE vs indices
| От | James Cloos |
|---|---|
| Тема | ILIKE vs indices |
| Дата | |
| Msg-id | m34nj5eq1e.fsf@carbon.jhcloos.org обсуждение исходный текст |
| Ответы |
Re: ILIKE vs indices
|
| Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: