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