text/varchar_pattern_ops on PostgreSQL could be optional

[djbug]

Bug report 12234 modified the index for char & text fields in PostgreSQL to use operator class varchar_pattern_ops & text_pattern_ops respectively. This makes the index usable for LIKE queries.

However there isn't any discussion on the additional cost associated with making this as the default option. It would have been a good optional feature. Consider the username column in user table. How often do we need to do LIKE queries on this.

But if this needs to be maintained for backwards compatibility, there should be an option to get a vanilla index (without *_pattern_op class) if the column will be used for simple lookups of the typeWHERE name = 'abc'

[Tim Graham]

Have you experienced performance problems because of this? I'd want to avoid building a flag that doesn't have much utility if the overhead of these indexes is negligible for most use cases.

[djbug]

Replying to timgraham:

Have you experienced performance problems because of this? I'd want to avoid building a flag that doesn't have much utility if the overhead of these indexes is negligible for most use cases.

Sorry, as of now, I don't have enough data to give a concrete reply to this. My question is from a theoretical standpoint. Usually the database is good at choosing the default index type. I couldn't find sufficient logic to make the switch in bug report 12234, hence I raised this issue.

[Tim Graham]

In general, design decisions are handled on the DevelopersMailingList. You'll be more likely to get an answer there than on this ticket tracker. I'll close this for now and we can reopen if a use case for disabling these indexes comes up.

[djbug]

Makes sense. I'll followup on the issue when I have more data.

[Simon Kelly]

Another point to note related to this is that the additional index only gets added when creating the table initially. If you add a column to the table after it has been created then the 'like' index is not created.

This is a bit inconsistent.

[Pavel Tyslacki]

Unused indexes can eat db space, for example _like indexes eat about 5% in my case and I really no need most of them (varchar identifiers).

[Tim Graham]

#24082 is a related ticket that might be of interest.

[Jakub Szafrański]

I think I have a valid use-case.

I have a table with a large amount of rows - around 180,000,000, and increasing. I have one field that's considered a "grouping" field, but happens to be a VARCHAR field.

I will never make a LIKE query on this field, because it contains mostly IDs (only stored as VARCHAR). But I am making a lot of WHERE xxx = something and WHERE xxx in (something1, something2, ...). Therefore, the varchar_pattern_ops index will never be used in my case, but it takes a considerable amount of time to build up, when I'm mass-importing new data into this table.

I hope this qualifies as a valid use-case - if it does, please reopen this issue.

[Tom Carrick]

Replying to Jakub Szafrański:

I think I have a valid use-case.

I have a table with a large amount of rows - around 180,000,000, and increasing. I have one field that's considered a "grouping" field, but happens to be a VARCHAR field.

I will never make a LIKE query on this field, because it contains mostly IDs (only stored as VARCHAR). But I am making a lot of WHERE xxx = something and WHERE xxx in (something1, something2, ...). Therefore, the varchar_pattern_ops index will never be used in my case, but it takes a considerable amount of time to build up, when I'm mass-importing new data into this table.

I hope this qualifies as a valid use-case - if it does, please reopen this issue.

I assume this covers your use case now? ​https://docs.djangoproject.com/en/2.2/ref/models/indexes/#opclasses