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
37211	"PostgreSQL: compile __in lookup to ""= ANY(%s::type[])"" to avoid O(N) placeholder rewrite"	Jimmy Yeung		"This ticket follows up on a Django Forum discussion:
https://forum.djangoproject.com/t/postgresql-compile-in-lookup-to-any-s-to-avoid-o-n-placeholder-rewrite/45386

Django's In lookup compiles to `col IN (%s, %s, ..., %s)` on every backend.
On PostgreSQL, for large literal iterables this creates an O(N) Python
cost inside psycopg's `_split_query`: psycopg must scan the SQL string to
substitute each %s (client-side binding) or rewrite %s to $1..$N for the
extended query protocol (server-side binding). psycopg's `lru_cache` is
bypassed above MAX_CACHED_STATEMENT_LENGTH=4096 or
MAX_CACHED_STATEMENT_PARAMS=50, so large __in payloads pay the full
uncached cost on every call.

Concrete production case: filter(id__in=ids) with ~42,000 UUIDs. The
generated SQL is ~170 KB. Postgres itself executes in ~99 ms
(EXPLAIN ANALYZE), but the request spends ~3.2 s (41.9% of total request
time) inside `_split_query`. See psycopg issue for driver-side context:
https://github.com/psycopg/psycopg/discussions/628

PostgreSQL's parse analysis already normalizes `col IN (literal, ...)`
to `col = ANY(ARRAY[literal, ...])` (visible in EXPLAIN as
ScalarArrayOpExpr). Emitting `= ANY(%s::type[])` directly, with a single
bound Python list adapted as a PostgreSQL array, produces the same plan
and eliminates the per-placeholder driver cost.

This follows the same shape as #35936 (PR #18847, ""Used unnest for bulk
inserts on Postgres when possible"", merged Dec 2024): collapse N
placeholders to one bound array parameter to skip per-placeholder driver
work.

Simon Charette endorsed the direction on the forum thread and suggested
a DatabaseOperations method to gate arrayifiability per-field, matching
the `unnest` pattern.

Proposed scope:
* PostgreSQL only. Other backends unchanged.
* Literal-iterable RHS only. QuerySet / Subquery RHS still compiles to
  IN (SELECT ...).
* CompositePrimaryKey (ColPairs/tuple LHS) falls through.
* Fields with custom placeholders (contrib.postgres array/range, GIS
  geometry, hstore) fall through via the DatabaseOperations gate.
* Empty list still raises EmptyResultSet.

Benchmark (42k UUIDs, local Postgres, psycopg 3.3):
* baseline IN (%s, ...):     173 ms wall (84 ms self inside _split_query)
* branch = ANY(%s::uuid[]):   99 ms wall (_split_query not called)"	New feature	new	Database layer (models, ORM)	dev	Normal		postgres postgresql performance in lookup		Accepted	1	0	0	0	0	0