Indexes vs Collation vs Pattern Ops

Question

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

Answer 1

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.