PostgreSQL: compile __in lookup to "= ANY(%s)" to avoid O(N) placeholder rewrite

Django Internals ORM
1

Hi all, would like to raise a problem and see if it echos with everyone else.

Problem

Django’s In lookup currently compiles to WHERE col IN (%s, %s, …, %s) with N placeholders, on every backend including PostgreSQL. For large __in lists this becomes an O(N) Python cost in the driver, independent of how fast Postgres executes the query.

A concrete case my team hit this week: a filter(id__in=ids) call with ~42,000 UUIDs. The generated SQL is ~170 KB. Postgres executes it in ~99 ms (verified via EXPLAIN ANALYZE), but the request spends ~3.2 s (41.9% of total request time) inside psycopg’s _split_quer.

This cost is the same on both psycopg2 and psycopg3, and on both client-side and server-side binding modes:

  • psycopg2’s _split_query regex-scans the SQL to substitute %s client-side.
  • psycopg3’s Cursor (server-side binding, extended query protocol) still has to rewrite %s → $1, $2, …, $N because that’s what the protocol speaks. Same O(N) scan. (source)
  • psycopg3’s _query2pg lru_cache is bypassed when len(query) > 4096 or len(vars) > 50 — a 42k IN clause blows past both on every call.

Proposal

Add In.as_postgresql that emits col = ANY(%s) (with appropriate type cast where needed, e.g. = ANY(%s::uuid[])) and binds the values as a single list parameter. psycopg2 and psycopg3 both adapt Python list to a PG array natively, so no driver changes are required.

This is the same shape of optimization as PR #18847: collapse N placeholders to one bound array param to skip per-placeholder driver work and let prepared-statement caching kick in.

Scope:

  • PostgreSQL only. SQLite/MySQL/Oracle unchanged.
  • Literal-iterable RHS only. filter(field__in=queryset) and filter(field__in=Subquery(...)) must still compile to IN (SELECT …) — the existing In.process_rhs branch already separates these.
  • Preserve the empty-list shortcut.
  • The PG planner is array-aware: col = ANY(array_literal) and col IN (list) produce equivalent plans on indexed columns.

Open questions for the proposal

  1. Type cast: bare = ANY(%s) occasionally trips PG planner type resolution for certain field types (UUID notably). Emitting = ANY(%s::<db_type>[]) using field.db_type(connection) seems to be the safe choice. Anyone seen a case where this falls down?

Let’s see if others face the same issue with postgres and may want a way out too!

2 Likes
2

Hello @yeung108 thanks for the suggestion that definitely look promising.

As we rolled out the usage of unnest for bulk-inserts we discovered a few limitations and I would expect a few more to be surfaced in this case.

We had to make adjustments for sized arrays, geometry fields, foreign keys, and made some distant related changes for insertion placeholders so we should cover for those and consider adding a Postgres DatabaseOperation method to detect whether or not a set of value can be turned into an array.

Other than that is seems like recent versions of Postgres treats IN (%s, ..,, %s) and = ANY(%s::type[]) the same way and we shouldn’t run into plan change issues. To answer your question, I think it’s a safe approach but one worth of mention in the release notes just to be sure.

Out of curiosity, did you manage to get a full successful test suite run already? I wonder if there are cases where the Postgres = operator coerced mixed values that might trip %s::type[] casting.

1 Like
3

recent versions of Postgres treats IN (%s, ..,, %s) and = ANY(%s::type[]) the same way and we shouldn’t run into plan change issues

FWIW my team is on Postgres 15. Haven’t checked whether the latest Postgres already solved this issue tho…

Out of curiosity, did you manage to get a full successful test suite run already

No I don’t…just try to raise idea in the forum and see anyone is on this already.

4

This sounds like a good idea to me. I think the next steps would be:

  • Open a ticket on code.djangoproject.com (if one doesn’t already exist)
  • Get the Django test suite passing with the proposed change
5

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

2 Likes