id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37237	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):

{{{#!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."	Cleanup/optimization	new	Database layer (models, ORM)	6.0	Normal		performance		Accepted	0	0	0	0	0	0