[Django] #32485: Django ORM icontains Operator Performance and Response Problem
Django
-------------------------------------+-------------------------------------
Reporter: Mesut | Owner: nobody
Öncel |
Type: | Status: new
Cleanup/optimization |
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: Django ORM,
Severity: Normal | icontains, ilike, performance
Triage Stage: | tuning
Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
There are 2 major disadvantages of using UPPER instead of buttonhole in
Django ORM "icontains" operator. First of all, I would like to talk about
the performance problem. If you add an expression to an index set for the
"like" operator, index will not be used.
**Query performance when we create a standard index for the like / ilike
operator and do not use upper.**
{{{
postgres=# CREATE INDEX trgm_idx_test_name ON test USING gin (name
gin_trgm_ops);
CREATE INDEX
postgres=# explain analyze select name from test where name ilike
'%aXxA%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=37.16..574.45 rows=150 width=16) (actual
time=0.314..0.469 rows=27 loops=1)
Recheck Cond: ((name)::text ~~* '%aXxA%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=28
-> Bitmap Index Scan on trgm_idx_test_name (cost=0.00..37.12 rows=150
width=0) (actual time=0.268..0.269 rows=28 loops=1)
Index Cond: ((name)::text ~~* '%aXxA%'::text)
Planning Time: 0.374 ms
Execution Time: 0.507 ms
(8 rows)
}}}
**Query performance also when using Upper method.**
{{{
postgres=# explain analyze select name from test where UPPER(name) like
'%AAXXXA%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..18532.00 rows=480 width=16) (actual
time=260.902..262.631 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test (cost=0.00..17484.00 rows=200 width=16)
(actual time=253.644..253.644 rows=0 loops=3)
Filter: (upper((name)::text) ~~ '%AAXXXA%'::text)
Rows Removed by Filter: 500000
Planning Time: 0.082 ms
Execution Time: 262.653 ms
(8 rows)
}}}
**If the UPPER method is desired to be used, the result of expression
index query performance.**
{{{
postgres=# CREATE INDEX trgm_idx_test_name ON test USING gin (UPPER(name)
gin_trgm_ops);
CREATE INDEX
postgres=# explain analyze select name from test where UPPER(name) like
'%AAXXXA%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=71.72..1610.71 rows=480 width=16) (actual
time=0.459..0.460 rows=0 loops=1)
Recheck Cond: (upper((name)::text) ~~ '%AAXXXA%'::text)
-> Bitmap Index Scan on trgm_idx_test_name (cost=0.00..71.60 rows=480
width=0) (actual time=0.457..0.457 rows=0 loops=1)
Index Cond: (upper((name)::text) ~~ '%AAXXXA%'::text)
Planning Time: 0.340 ms
Execution Time: 0.497 ms
(6 rows)
}}}
These analyzes show that when using the "icontains" operator, "ilike"
should be used instead of "UPPER like". Those who use such information
outside the framework of the database should not use "ilike". Database
administrators will not create an index specific to this structure.
Because they need to create both expression index and standard index. This
causes both cost and performance problems.
The other problem is that the results are wrong because it uses UPPER. I
would like to prove this with a query like this.
{{{
postgres=# select name from test where UPPER(name) like
UPPER('%istanbul%');
name
------
(0 rows)
postgres=# select name from test where name ilike '%istanbul%';
name
----------
İstanbul
(1 row)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32485>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Django ORM, | Triage Stage:
icontains, ilike, performance | Unreviewed
tuning |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
For
https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L103
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32485#comment:1>
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Django ORM, | Triage Stage:
icontains, ilike, performance | Unreviewed
tuning |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> For
> https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L103
New description:
[https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L103]
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32485#comment:2>
Django
-------------------------------------+-------------------------------------
Type: | Öncel
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Django ORM, | Triage Stage:
icontains, ilike, performance | Unreviewed
tuning |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Mesut Öncel
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32485#comment:3>
Django
-------------------------------------+-------------------------------------
| Öncel
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: Django ORM, | Triage Stage:
icontains, ilike, performance | Unreviewed
tuning |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* type: Cleanup/optimization => New feature
* resolution: => wontfix
Comment:
Thanks for this proposition, please take a look at #3575 which changed
`ilike` to `UPPER()` on PostgreSQL.
We should reach a consensus on DevelopersMailingList before we will move
it forward. Please start a discussion on DevelopersMailingList where
you'll also reach a wider audience.
Closing as wontfix, for now.
--
Ticket URL: <https://code.djangoproject.com/ticket/32485#comment:4>
Django
-------------------------------------+-------------------------------------
| Öncel
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Django ORM, | Triage Stage:
icontains, ilike, performance | Unreviewed
tuning |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
[https://groups.google.com/g/django-developers/c/buSJ6A07rxE/m/coQpjG-
cAAAJ Discussion on the django-developers mailing list]
--
Ticket URL: <https://code.djangoproject.com/ticket/32485#comment:5>