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

[milos d <acerbitdrain@xxxxxxxxxxx>]

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

Thanks Scott,

Yes you are right, my code does lower case 'foo%'. I would expect SELECT ... WHERE col1 ILIKE 'foo%' to use an index.
The way I understand it is that the planner would translate this to SELECT ... WHERE lower(col1) LIKE lower('foo%') ?

You may be right, with more tests I see that ILIKE never uses an index. e.g SELECT ... WHERE lower(col1) ILIKE lower('foo%' ) doesn't use an index.

I think we need clarification from the developers.

Tools like Hibernate and NHibernate use ILIKE to do insensitive matching when using PostgresDialect.

Regards,
Milos.

---

From: scott@xxxxxxxxxxxxxxxxx
To: dev@xxxxxxxxxxxx; acerbitdrain@xxxxxxxxxxx
CC: pgsql-performance@xxxxxxxxxxxxxx
Date: Thu, 12 Feb 2009 11:50:42 -0800
Subject: 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

Let ninemsn property help! Need a new place to rent, share or buy?