Skip site navigation (1) Skip section navigation (2)

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

From: milos d <acerbitdrain(at)hotmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'
Date: 2009-02-12 11:50:23
Message-ID: COL115-W810E7D489AB499923CA917D5BB0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance
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.

Could this be a bug with ILIKE or am I missing something?

The table has ~ 4 million rows.
PostgreSQL 8.3.5 on Windows Vista, non C locale, DB encoding is LATIN1.

Thank you in advance,
Milos.
_________________________________________________________________
Want to marry your mail? Combine your email accounts here!
http://livelife.ninemsn.com.au/article.aspx?id=633386

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2009-02-12 12:04:16
Subject: Re: col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'
Previous:From: Guillaume CottenceauDate: 2009-02-12 08:23:56
Subject: Re: scheduling autovacuum at lean hours only.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group