Re: col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

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