I'm using PostgreSQL 9.3.
Having already read articles and answers on DBA stackexchange:
and being aware of
EXPLAIN command I've crossed paths with performance issues in my DB.
As mentioned in documentation and answers above, it seems that when my database has a collation C
db=# SHOW LC_COLLATE; lc_collate ------------ C
I do not need to use special operator ie
text_pattern_ops on my indexes.
I've also tested how things work in my database and it seems that I:
- have collate C
- have table (assume it's named as
table) containing column named signature of type text
- have index on that table
idx_table_sign btree (upper(signature))
- also have index on that table
idx_table_sign_like btree (upper(signature) text_pattern_ops)
Now let's assume I have a row with
I Lm 837/32.
EXPLAIN SELECT * from table WHERE upper(signature) = 'I Lm 837/32'
Does search using index
Index Scan idx_table_sign on table ... Index Cond: ...
Which is OK considering our index, but let's see this:
EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%'; Index Scan idx_table_sign_like on table ... Index Cond: (... ~>=~ ...) AND (... ~<~ ...) Filter: ... ~~ ...
Which seems good as well, but as I've read when I do have collation
C i don't need to set
text_pattern_ops indexes, so imagine this:
DROP INDEX idx_table_sign_like; EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%';
Which only differs in operators (
~>=~ is now simply
Index Scan idx_table_sign_like on table ... Index Cond: (... >= ...) AND (... < ...) Filter: ... ~~ ...
Now, thank you for reading this and getting so far, here are questions:
- Do I need two indexes on this column with one using
text_pattern_ops? Doesn't collate
Cdo the trick? Also, is it possible that I have a table or a column that overwrites the collation of entire database, and because of that it might have been added? If yes, please tell me how do I check that.
- Are the operations
INSERT, UPDATE, DELETEslowed by this index (imagine I have 4 columns like this in a table containing 1 million rows - main point is that those indexes need to be rebuilt (as far as I know), which may be unnecessary - this question is about telling me whether or not queries also use this index with text_pattern_ops instead of just C collation and if that may result in lower performance - you can see the EXECUTION PLANS with that index
- Do queries like
select ... where upper(signature) like 'xxx%'- left-anchored patterns use index
idx_table_sign_likewhich is totally unnecessary considering my collation which they could be using instead? - please notice the difference in operators that planner is using depending on index (having it or not)
- Added after Craig Ringer answer. In my columns I'm having text and varchar which contain polish characters. What does it change in my specific case? I hope this is some kind of a hint for the answerer on topic of neediness of those indexes.
These questions are very important, as my database is pretty huge (> 1TB) and has more than a dozen tables containing such indexes on several columns, on which inserts and updates come on a daily basis.