I have some sympathy for this issue as I'm trying to make the createcachetable management command use SchemaEditor rather than some custom SQL construction logic*. The related problem I ran into is that the primary key column (a CharField) uses unique=True which means those undesired opclasses indexes are created. I couldn't find a way to prevent that index besides filtering it out of the list of SQL statements.
As for your proposal, how would you handle the upgrade path for existing projects? I imagine we could provide a script to run upon upgrade to remove all such existing indexes. No doubt some users won't run it. Would you keep around the code in Django's SchemaEditor like https://github.com/django/django/blob/53d229ff632c2a3e547f2820a94239f38ba4d4ac/django/db/backends/postgresql/schema.py#L178-L180 that assumes those indexes exist? If Django stops creating those indexes, it could create a somewhat murky situation for some developers as they try to figure out the state of indexes in their database. It might depend on which version of Django was in use when certain migrations ran. Some developers might be left debugging performance issues if those indexes are removed. It could be helpful to gives some numbers as to the possible performance impact on applications if these indexes are removed without a developer realizing it. Third-party apps that need it could add an Index with opclasses but then they'd face the issue of duplicate opclasses indexes if their app is used on an older version of Django. * https://github.com/django/django/pull/12590 On Sunday, April 12, 2020 at 10:30:50 AM UTC-4, Hannes Ljungberg wrote: > > Hi all, > > I would like to continue the discussion started in this very old thread: > https://groups.google.com/d/msg/django-developers/H2QFcQYsbo8/RmRb-8FVypwJ > > I’m sorry if I should've continued the discussion in that thread but it > felt a bit wrong to bring a 5 year old thread back to life :-) > > Anyway, as previously described in that thread the implicit creation of > the `*_pattern_ops` index when creating indexes on `CharField` and > `TextField` with `db_index=True` is not ideal. > > In my experience `LIKE` expressions is not that common that it warrants to > always create an index to cover this. > > For very large tables this can become a problem where insertion/update > performance is negatively affected and disc space usage is twice what > really is needed if no `LIKE` queries are used. > > And even if one would like to use `LIKE` expressions it’s not obvious that > the `*_pattern_ops` is the correct index. For leading wildcard patters as > `LIKE %foo` one has to use a GIN/GiST index with the `*_trgm_ops` opclass. > With the current implementation we would end up with 3 indexes when 2 would > be sufficient for this use case (the regular b-tree and the trigram). > > One could also argue that we’re not consistent with these implicit > indexes. The `iexact`/`icontains` lookups require an expression index on > `(UPPER(col))` but that’s not created. > > One important detail is that this implicit index is _not_ created when > using the class based `Index` . In my opinion it’s not very clear that one > needs to handle the creation of a `*_pattern_ops` index manually when using > it. > > This is the only documentation that I’ve been able to find about the > creation of these implicit indexes: > https://docs.djangoproject.com/en/dev/ref/databases/#indexes-for-varchar-and-text-columns > > My proposal is to remove the implicit index created by `db_index=True` and > add documentation that one should use `Index.opclasses` to utilise indexes > for `LIKE` queries on PostgresSQL. This would give a more consistent > behaviour and being more explicit about this would help people tune their > indexes. > > If there’s a consensus on this I would like to give this a shot. If > there’s not an agreement on removing the implicit index I think we should > at least make the documentation around this a bit more clear about the > gotchas of `Index` vs `db_index=True`. > > Here’s two tickets which have previously discussed this: > https://code.djangoproject.com/ticket/24507 > https://code.djangoproject.com/ticket/24088 > > Stay safe! > > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d95c53af-a2df-46a6-8786-c10861e58ced%40googlegroups.com.
