3 ms·
There's a few other features of Postgres indexing that are interesting: - Indexes over expressions (e.g. upper(value)) are very helpful. - You can add a WHERE
by AlisdairO 13y ago
There's a few other features of Postgres indexing that are interesting:
- Indexes over expressions (e.g. upper(value)) are very helpful.
- You can add a WHERE clause to an index to index only part of the table, which is fantastically useful.
- on a non-C locale, LIKE won't (ever) use indexes. varchar_pattern_ops can help you with this to an extent, but it's not perfect.
- einhverfr 13y agoI think LIKE can use indexes where the left is anchored with enough text for an index to be useful. LIKE 'this is fun%' will be more likely than LIKE 'a%' LIKE '%fun vacation' won't but if you have an index on reverse(mycolumn) and you have where reverse(mycolumn) like reverse('%fun vacation'), it might. You can also add pg_trgm as an extension and then create a GIN index. Not perfect at all but better than nothing. Also indexes over expressions can be used to precalculate interesting sets if you have expensive routines in the database (the typical hypothetical example is image recognition).
- AlisdairO 13y ago> I think LIKE can use indexes where the left is anchored with enough text for an index to be useful. LIKE 'this is fun%' will be more likely than LIKE 'a%' LIKE '%fun vacation' won't but if you have an index on reverse(mycolumn) and you have where reverse(mycolumn) like reverse('%fun vacation'), it might. That's the case for the C locale, but (as I understand it) not for other locales.
- einhverfr 13y agoReading a bit more it looks like you have to specify the text_pattern_ops on the index. This makes some sense actually, since it means that the index is ordered by the localized string order (so the index can be used).