I'm pretty sure the intent was:
WHERE lower(col1) LIKE lower('foo%');
Most likely, his client code ensures the lower on the string passed in the
query. Whether it should use an index or not has nothing to do with his
example.
All I can do when answering this question, is confirm that the query planner
doesn't know when it can and can't pair ILIKE with an index, even if lower() or
upper() are used on parameters and indexes.
I use
WHERE lower(col1) LIKE
(and create a functional index on lower(col1))
And just pretend that there isn't an ILIKE.
On 2/12/09 4:04 AM, "Richard Huxton" <d...@archonet.com> wrote:
milos d wrote:
> Hello,
>
> I have a table 'foo_bar' with a column 'col1' defined as
> 'col1 varchar(512)'. This column is indexed using an expression index
> defined as
>
> CREATE INDEX ix_foo_bar_by_col1 ON foo_bar(lower(col1) col1
> varchar_pattern_ops)
>
> The
> problem is when I try matching using ILIKE, (col1 ILIKE 'foo%')
> PostgreSQL does not use an index scan but a Seq scan of the whole
> table, but when I try (lower(col1) LIKE 'foo%')
> PostgreSQL uses an index scan.
Why should it use the index? They're not even equivalent queries:
SELECT ... WHERE lower(col1) LIKE 'FOO%'
SELECT ... WHERE col1 ILIKE 'FOO%'
One is guaranteed to return no rows, the other not.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
