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?