#21757: Allow modifying the SQL generated by lookups
-------------------------------------+-------------------------------------
Reporter: shmishleniy@… | Owner: nobody
Type: | Status: new
Cleanup/optimization | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 0
Keywords: postgresql like | Patch needs improvement: 0
ilike icontains | UI/UX: 0
Has patch: 0 |
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by mjtamlyn):
Reading your `EXPLAIN` output, the easiest thing to do will be for you to
add a PG functional index to that column on `UPPER(value)`. According to
the benchmarks in the old ticket (though it is possible PG's optimisations
have changed since then) this will actually come out faster than your
ILIKE query you have at the moment. You would need to create that index by
hand (or using South or db.migrations in 1.7). It would be interesting to
see an updated version of the benchmarks from #3575 as they may be
different with Postgres 9 than it was with whatever version of PG we had 6
years ago. I would still be nervous about changing the default though as
this could break optimisations other people have put in place.
A full benchmark would need to show:
- Performance of ILIKE without index
- Performance of LIKE UPPER without index
- Performance of ILIKE with the relevant index
- Performance of LIKE UPPER with the relevant index
At the moment I believe your tests are showing the second and third in
that list, which is a little unfair as one utilises an index and the other
does a sequential scan.
I do not believe the documentation needs to be updated - this kind of
optimisation is an advanced use case.
There are a couple of ideas in progress which will result in making this
kind of thing easier. One is custom lookups (#16187), the other is custom
indexes (can't remember the number).
--
Ticket URL: <https://code.djangoproject.com/ticket/21757#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/079.16573ab3c9439588d7fc8be2e2e33c3c%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.
