[Django] #37211: PostgreSQL: compile __in lookup to "= ANY(%s::type[])" to avoid O(N) placeholder rewrite

[Django]

#37211: PostgreSQL: compile __in lookup to "= ANY(%s::type[])" to avoid O(N)
placeholder rewrite
-------------------------------------+-------------------------------------
Reporter: Jimmy Yeung | Type: New
| feature
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Normal
Keywords: postgres postgresql | Triage Stage:
performance in lookup | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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)
--
Ticket URL: <https://code.djangoproject.com/ticket/37211>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37211: PostgreSQL: compile __in lookup to "= ANY(%s::type[])" to avoid O(N)
placeholder rewrite
-------------------------------------+-------------------------------------

Reporter: Jimmy Yeung | Owner: (none)

Type: New feature | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgres postgresql | Triage Stage:
performance in lookup | Unreviewed

Has patch: 1 | Needs documentation: 0

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

Changes (by Jimmy Yeung):

* has_patch: 0 => 1

Comment:

PR: https://github.com/django/django/pull/21602
--
Ticket URL: <https://code.djangoproject.com/ticket/37211#comment:1>

[Django]

#37211: PostgreSQL: compile __in lookup to "= ANY(%s::type[])" to avoid O(N)
placeholder rewrite
-------------------------------------+-------------------------------------
Reporter: Jimmy Yeung | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgres postgresql | Triage Stage: Accepted
performance in lookup |

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

Changes (by Simon Charette):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/37211#comment:2>

[Django]

#37211: PostgreSQL: compile __in lookup to "= ANY(%s::type[])" to avoid O(N)
placeholder rewrite
-------------------------------------+-------------------------------------

Reporter: Jimmy Yeung | Owner: Jimmy
| Yeung
Type: New feature | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres postgresql | Triage Stage: Accepted
performance in lookup |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* owner: (none) => Jimmy Yeung
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37211#comment:3>