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
