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

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" <dev(at)archonet(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance