[Django] #24088: Why is operator class used by default for PostgreSQL indexes on char & text field
Django
field
-------------------------------+--------------------
Reporter: djbug | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
[https://code.djangoproject.com/ticket/12234 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 type`WHERE name =
'abc'`
--
Ticket URL: <https://code.djangoproject.com/ticket/24088>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: djbug | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* needs_docs: => 0
* type: Uncategorized => New feature
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by djbug):
Replying to [comment:1 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:3>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by djbug):
Makes sense. I'll followup on the issue when I have more data.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:4>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by snopoke):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:5>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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).
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:6>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham):
#24082 is a related ticket that might be of interest.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:7>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by samupl):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:8>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tom Carrick):
Replying to [comment:8 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
--
Ticket URL: <https://code.djangoproject.com/ticket/24088#comment:9>