Slow SQL query with a large list of values

[Adam Sołtysik]

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):

    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 ​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.

[Jacob Walls]

Thanks for the bench. I think we should tentatively accept this for investigation. I suggested opening a ticket for the issue of large collections of values when looking at another profile, but it might have gotten lost in the shuffle.. (My concern at the time with the ​PR was that we weren't optimizing the right thing, but I'm glad to look at a new approach.)

[Simon Charette]

Could this be a duplicate of #37211?

[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:

    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?