[Django] #37237: Slow SQL query with a large list of values

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Type:
| Cleanup/optimization
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When we call something like `queryset.filter(id__in=large_list)`, the
query runs several times slower than the same query executed manually,
even if there are no rows returned.

Example (function added in `tests/basic/tests.py`, testing on Postgres):

{{{#!python
def test_id_in(self):
import time

values = list(range(-100000, 0))

t = time.perf_counter()
print(Article.objects.filter(id__in=values))
print(f'{(time.perf_counter() - t):.3f} s')

t = time.perf_counter()
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM basic_article WHERE id =
ANY(%s)', [values])
print(cursor.fetchall())
print(f'{(time.perf_counter() - t):.3f} s')
}}}

Results (`python runtests.py basic -k id_in`):

{{{
<QuerySet []>
0.584 s
[]
0.176 s
}}}

Note that I'm using `ANY` as
[https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#you-cannot-use-
in-s-with-a-tuple recommended by psycopg], but the same happens with a
manually constructed query with `IN`.

Another situation when this slowdown appears is removing a large number of
items from a M2M relation.
--
Ticket URL: <https://code.djangoproject.com/ticket/37237>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------

Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* keywords: => performance
* stage: Unreviewed => Accepted

Comment:

Thanks for the bench. I think we should tentatively accept this for
investigation. I suggested opening a ticket when looking at another
profile, but it might have gotten lost in the shuffle.. (My concern at the
time with the
[https://github.com/django/django/pull/20779#discussion_r3415248102 PR]
was that we weren't optimizing the right thing, but I'm glad to look at a
new approach.)
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:1>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Could this be a duplicate of #37211?
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:2>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Adam Sołtysik):

Indeed we're talking about the same thing, and performance is much better
with that PR applied. Nice to see it being worked on.

However, the manually executed query is still noticably faster. In my
benchmark, we get from 0.584 s to 0.235 s, which is ~1.3x slower than the
0.176 s.

That could be considered reasonable as an ORM abstraction cost, but a
bigger slowdown remains with M2M, which I also mentioned. Here's a
benchmark:

{{{#!python
def test_m2m_remove(self):

import time

values = list(range(-100000, 0))

article = Article.objects.create()

t = time.perf_counter()
article.publications.remove(*values)
# or (same time):
# Article.publications.through.objects.filter(article=article,
publication__in=values).delete()

print(f'{(time.perf_counter() - t):.3f} s')

t = time.perf_counter()
with connection.cursor() as cursor:

cursor.execute('''
DELETE FROM many_to_many_article_publications
WHERE article_id = %s AND publication_id = ANY(%s)
''', [article.id, values])

print(f'{(time.perf_counter() - t):.3f} s')
}}}

Best results:
* 0.638 s for ORM on `main`
* 0.314 s for ORM with the PR
* 0.143 s for `cursor.execute`

So ~2.2x slower here, if I didn't miss anything.

On one hand, this discussion could be potentially continued in
https://github.com/django/django/pull/21605, but on the other hand, this
could be a different matter than the compilation to `ANY`, so maybe it's
worth keeping this issue separate?
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:3>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> So ~2.2x slower here, if I didn't miss anything.

There could be other factors at play but without profiling the execution
it's hard to tell what's to blame. Since you already have a setup for all
branches would you mind providing some profiling details against the MR
for #37211 and share them here an [https://forum.djangoproject.com/t
/postgresql-compile-in-lookup-to-any-s-to-avoid-o-n-placeholder-
rewrite/45386 on the forum thread]? I suspect
[https://forum.djangoproject.com/t/postgresql-compile-in-lookup-to-any-s
-to-avoid-o-n-placeholder-rewrite/45386 the creation] of the `OrderedSet`
to exclude `None` plays a role in the slowdown.
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:4>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:5>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------

Reporter: Adam Sołtysik | Owner: Vishy
Type: | Status: assigned

Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Vishy):

* owner: (none) => Vishy
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:6>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------

Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new

Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: Vishy => (none)
* status: assigned => new

Comment:

There's nothing to assign the ticket to you Vishy, can't you see there is
an active discussion on what should be done next?
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:7>

[Django]

#37237: Slow SQL query with a large list of values
-------------------------------------+-------------------------------------
Reporter: Adam Sołtysik | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: performance | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Vishy):

My bad. I assumed there's work to be done despite
[https://github.com/django/django/pull/21605 #21605], since you mentioned
profiling to find other performance related factors.
--
Ticket URL: <https://code.djangoproject.com/ticket/37237#comment:8>