Lower() vs ilike on postgresql
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.