Question :
What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ?
I was planning to create index on lower(column_name) and use lower(column_name) = value (value is lower case already) but it turns out that ORM I’m using doesn’t allow me to use lower() in a pretty way (but has convenient method for ilike). Will column_name ilike 'value' use lower(column_name) index?
Answer :
Measure it and see, using explain analyze on test data.
I would expect a significant difference between an expression index on lower(col) with lower(col) = lower('constant') vs use of col ilike 'constant', with the expression index on lower(col) the faster.
That’s because Pg can do a bitmap index scan with the expression index, but for the ilike it’ll have to do a seqscan. AFAIK a text_pattern_ops index (useful for LIKE) won’t do you any good for ILIKE.
The other question is whether the difference is worth caring about, and that depends on your data and workload. Sometimes it’s smarter to just choose the slower but easier option and spend yourtime/effort elsewhere.
Will column_name ilike ‘value’ use lower(column_name) index?
No. Nor will it use an index on lower(column_name) text_pattern_ops. AFAIK there is no way to index a case-insensitive pattern match (ILIKE or ~~*) on a case-sensitive text/varchar in PostgreSQL at this time.
I don’t think the citext type changes this, though it does make things more convenient.
If you build an index like:
CREATE INDEX idx_indexname
ON yourtable (LOWER(columnname) ASC NULLS LAST);
Lower will use index.
ILIKE will use index, but only in some case. I suggest starting here:
http://www.postgresql.org/docs/9.2/static/indexes-types.html