Opened 5 years ago

Closed 5 years ago

Last modified 8 months ago

#24088 closed New feature (needsinfo)

text/varchar_pattern_ops on PostgreSQL could be optional

Reported by: djbug Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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'

Change History (9)

comment:1 Changed 5 years ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: Why is operator class used by default for PostgreSQL indexes on char & text fieldtext/varchar_pattern_ops on PostgreSQL could be optional
Type: UncategorizedNew feature

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.

comment:2 in reply to:  1 Changed 5 years ago by 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.

comment:3 Changed 5 years ago by Tim Graham

Resolution: needsinfo
Status: newclosed

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.

comment:4 in reply to:  3 Changed 5 years ago by djbug

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

comment:5 Changed 4 years ago by 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.

comment:6 Changed 3 years ago by 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).

comment:7 Changed 3 years ago by Tim Graham

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

comment:8 Changed 21 months ago by 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.

comment:9 in reply to:  8 Changed 8 months ago by 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

Note: See TracTickets for help on using tickets.
Back to Top