Tag Info

New answers tagged


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


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 ...

Top 50 recent answers are included