4

I'm using PostgreSQL 9.3.

Having already read articles and answers on DBA stackexchange:

Why would you index text pattern ops on a text column

PostgreSQL documentation: Indexes - Opclass

Pattern matching with LIKE/SIMILAR TO/Regular expressions

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:

  1. have collate C
  2. have table (assume it's named as table) containing column named signature of type text
  3. have index on that table idx_table_sign btree (upper(signature))
  4. 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 signature value 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 >= and ~<~ is <)

    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:

  1. Do I need two indexes on this column with one using text_pattern_ops? Doesn't collate C do 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.
  2. Are the operations INSERT, UPDATE, DELETE slowed by this index (imagine I have 4 columns like this in a table containing 1 milion 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
  3. Do queries like select ... where upper(signature) like 'xxx%' - left-anchored patterns use index idx_table_sign_like which 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)
  4. 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.

  • I'll let someone else answer this; responding to your follow up comments (on my deleted answer) will take longer than I have. – Craig Ringer Oct 2 '14 at 1:06
  • Thanks though for your effort. I'll edit my question to make it more specific about what I'm asking for. – Kamil Gosciminski Oct 2 '14 at 6:13
  • Most experienced people read both/all sites, following tags of interest like postgresql. You haven't got answers because your question is long and confusing, and it isn't quite clear what you want / what about the docs doesn't already explain what you need. It's also a multi-question where you try to ask a bunch of different things that aren't really all that closely related, which tends to put people off. – Craig Ringer Oct 2 '14 at 9:16
  • I will try to edit/improve my question. – Kamil Gosciminski Oct 2 '14 at 9:17
  • 1
    For what its worth, I think perhaps you're misunderstanding the effects of C collation. It ignores natural language sorting and sorts by byte order. It's generally not desirable if your DB contains human-readable text as things will sort in the wrong order as far as your language is concerned. – Craig Ringer Oct 2 '14 at 9:35
2

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.