sql: asyncpg type introspection query (WITH RECURSIVE typeinfo_tree) takes 65-165 ms on an empty cluster and 340-525 ms on Cloud Standard
Nessuno ha ancora preso questa issue.
Valutazione
- Difficoltà
- 4/5
- Tempo stimato
- 3-5 giorni
- Idoneità per principianti
- 44/100
- Tipo di issue
- Bug
- Chiarezza
- Abbastanza chiara
- Stato di attività
- Attiva
- Ambito
- databases, performance
Direzione di ricerca
Start with the INTRO_LOOKUP_TYPES_13 query in asyncpg’s introspection.py, linked in the issue, and compare its recursive plan with the supplied EXPLAIN ANALYZE output. Focus on the reported full scans of pg_type and pg_class and the recursive CTE’s unexplained time. Done means new connections pay substantially less introspection latency; the issue does not identify a specific CockroachDB file or test.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Descrizione
Describe the problem
asyncpg runs a catalog introspection query (WITH RECURSIVE typeinfo_tree ..., introspection.INTRO_LOOKUP_TYPES_13 in asyncpg 0.31.0) the first time a connection sees a result type outside its built-in codec set, for example uuid[], varchar[] or int8[]. The result is cached per connection, so every new pooled connection pays the cost again.
On CockroachDB this query takes 65–165 ms on an empty single-node cluster. On PostgreSQL 16, the same call takes 8–14 ms. A direct pg_type lookup for the same OIDs takes under 1 ms on CockroachDB. The result is 2 rows.
This was tracked in #113292, which was closed by #117499 in December 2024. It is faster than the 2–7 s measured there, but still several times slower than PostgreSQL, and the v26.4 introspection improvements from #173076 do not change it (see below).
Production impact
A CockroachDB Cloud Standard cluster on v26.2.7 runs this query in 340–525 ms per call, according to crdb_internal.statement_statistics. The application uses SQLAlchemy 2.0 with asyncpg 0.31.0 and recycles pooled connections every 300 s. On one day, one database ran the introspection query 1,218 times, for about 493 s of cumulative latency. A single-row primary-key lookup on a table with uuid[] columns has p50 16 ms and p75 447 ms, because the first such query on each new connection waits for the introspection query. The customer is mitigating on the client side (a longer connection lifetime and a per-process introspection cache). That reduces how often the query runs, not what each run costs.
To Reproduce
cockroach start-single-node --insecure --store=type=mem,size=2GiB. Leave it empty, sodefaultdbhas 465 rows inpg_typeand 398 inpg_classon v26.4.0-beta.1.pip install asyncpg==0.31.0.- Run the script below (
python repro.py <dsn> <label>). It opens 7 new connections per type, runs the first array-returning query on each, and times only asyncpg's_introspect_typescall. It also runsEXPLAIN ANALYZEon the introspection query with$1replaced byARRAY[2951]::oid[](uuid[]).
Median introspection time per new connection (local arm64 Mac, in-memory store):
| Version | uuid[] |
varchar[] |
int8[] |
Direct execution of the query |
|---|---|---|---|---|
| v26.2.7 | 83 ms | 113 ms | 65 ms | 63 ms |
| v26.3.2 | 80 ms | 136 ms | 85 ms | 83 ms |
| v26.4.0-beta.1 | 88 ms | 102 ms | 164 ms | 129 ms |
| PostgreSQL 16.15 (Docker) | 14 ms | 8 ms | 8 ms | n/a |
Once the types are cached, the same query on the same connection takes under 1 ms on every version.
What the plan shows (v26.4.0-beta.1, full output below)
- Execution time is 90 ms, of which the
recursive ctenode reports 79 ms (34 ms SQL CPU). The operators under it add up to only a few milliseconds, so most of the CTE's time isn't attributed to any operator. One possibility is that the recursive step is re-planned on each iteration. - The recursive step does a full scan of
pg_type@primary(465 rows), and two full scans ofpg_class@primary(398 rows each) for the composite-type subqueries (attrtypoidsandattrnames). Both run even though those subqueries sit behindCASE WHEN t.typtype = 'c'and the looked-up type is an array. - The customer's production plan on v26.2.7 shows the same pattern at a larger scale. The
recursive ctenode takes about 326 ms, while the virtual-table scans under it report about 20 ms in total. Everypg_catalogvirtual table showsmissing stats, with estimates far from the actual counts (for example, 38,724 estimated vs 2 actual). Timed alone, the type-description subquery took about 130 ms and runs once per recursion step, about 3 times per call.
Expected behavior
Introspection close to PostgreSQL's cost, or at least close to the cost of a direct pg_type lookup by OID, so that each new connection doesn't add about 100–500 ms to the first query that returns one of these types.
Additional data / screenshots
- The repro script and the full
EXPLAIN ANALYZEoutput are at the bottom of this issue. - The query text is
INTRO_LOOKUP_TYPES_13in asyncpg/introspection.py. Upstream report: MagicStack/asyncpg#1158. - The customer also has a statement diagnostics bundle from an empty local v26.2.6 node. It's available through support and not attached here.
Environment:
- CockroachDB v26.2.7 (Cloud Standard, customer production); v26.2.7, v26.3.2 and v26.4.0-beta.1 (local repro)
- Server OS: CockroachDB Cloud (customer); macOS arm64 (repro)
- Client app: Python asyncpg 0.31.0, through SQLAlchemy 2.0.51 (customer) and directly (repro)
Additional context
Related:
- #113292: the same query, closed by #117499.
- #173076: Django pg_catalog introspection, fixed for v26.4. The remaining-work note there says the cost model treats every virtual-table lookup as a possible full scan, which looks relevant here.
- CRDB-37145: composite-type introspection slowness through
pg_class. - #62930: virtual indexes for all virtual tables.
Questions for the team:
- Is there a fix path for this query shape, such as virtual-index lookups on
pg_type/pg_classinside the recursive step, or not planning thetyptype = 'c'branches for non-composite types? Would the in-flight PushJoinIntoApplyJoin work help? - Is there a session or cluster setting that reduces the cost in the meantime?
- What is the recommended client-side setup for asyncpg on CockroachDB?
repro.py
"""ZD32819 repro: time asyncpg type introspection on a fresh connection.
Usage: python repro.py <dsn> <label>
"""
import asyncio
import statistics
import sys
import time
import asyncpg
from asyncpg import introspection
RUNS = 7
CASES = [
("uuid[]", "SELECT ARRAY[gen_random_uuid()]"),
("varchar[]", "SELECT ARRAY['a'::varchar]"),
("int8[]", "SELECT ARRAY[1]::int8[]"),
]
class TimedConn(asyncpg.Connection):
"""Records how long each _introspect_types call takes."""
intro_ms = []
async def _introspect_types(self, typeoids, timeout):
t = time.perf_counter()
try:
return await super()._introspect_types(typeoids, timeout)
finally:
TimedConn.intro_ms.append((list(typeoids), (time.perf_counter() - t) * 1000))
async def main(dsn, label):
conn = await asyncpg.connect(dsn)
version = await conn.fetchval("SELECT version()")
await conn.close()
print(f"### {label}: {version.split('(')[0].strip()}")
for name, sql in CASES:
first, warm, intro = [], [], []
for _ in range(RUNS):
TimedConn.intro_ms.clear()
c = await asyncpg.connect(dsn, connection_class=TimedConn)
t = time.perf_counter()
await c.fetch(sql)
first.append((time.perf_counter() - t) * 1000)
intro.append(sum(ms for _, ms in TimedConn.intro_ms))
oids = [o for o, _ in TimedConn.intro_ms]
t = time.perf_counter()
await c.fetch(sql)
warm.append((time.perf_counter() - t) * 1000)
await c.close()
print(
f"{name:10} oids={oids} first_query_median={statistics.median(first):7.1f}ms "
f"introspection_median={statistics.median(intro):7.1f}ms "
f"(min {min(intro):.1f}, max {max(intro):.1f}) warm_median={statistics.median(warm):5.1f}ms"
)
# Direct timing of the exact introspection statement, plus EXPLAIN ANALYZE.
c = await asyncpg.connect(dsn)
q = introspection.INTRO_LOOKUP_TYPES_13
ts = []
for _ in range(RUNS):
t = time.perf_counter()
await c.fetch(q, [2951])
ts.append((time.perf_counter() - t) * 1000)
print(f"direct INTRO_LOOKUP_TYPES_13 oid 2951: median {statistics.median(ts):.1f}ms min {min(ts):.1f} max {max(ts):.1f}")
rows = await c.fetch("EXPLAIN ANALYZE " + q.replace("$1::oid[]", "ARRAY[2951]::oid[]"))
for r in rows[:12]:
print(" ", r[0])
await c.close()
asyncio.run(main(sys.argv[1], sys.argv[2]))
EXPLAIN ANALYZE output on v26.4.0-beta.1 (uuid[], OID 2951)
planning time: 4ms
execution time: 90ms
distribution: local
plan type: generic, re-optimized
• distinct
│ sql nodes: n1
│ execution time: 31µs
│ sql cpu time: 31µs
│ actual row count: 2
│ distinct on: oid, ns, name, kind, basetype, elemtype, elemdelim, range_subtype, attrtypoids, attrnames, depth
│ order key: depth
│
└── • render
│ execution time: 66µs
│ sql cpu time: 66µs
│ actual row count: 2
│
└── • sort
│ sql nodes: n1
│ execution time: 28µs
│ sql cpu time: 28µs
│ actual row count: 2
│ order: -depth
│
└── • render
│
└── • recursive cte
│ sql nodes: n1
│ execution time: 79ms
│ sql cpu time: 34ms
│ actual row count: 2
│
└── • render
│ execution time: 68µs
│ sql cpu time: 68µs
│ actual row count: 1
│
└── • render
│ execution time: 4µs
│ sql cpu time: 5µs
│ actual row count: 1
│
└── • group (hash)
│ sql nodes: n1
│ execution time: 19µs
│ sql cpu time: 19µs
│ actual row count: 1
│ group by: rownum
│
└── • sort
│ sql nodes: n1
│ execution time: 11µs
│ sql cpu time: 11µs
│ actual row count: 1
│ order: +attnum
│
└── • merge join (left outer)
│ sql nodes: n1
│ execution time: 18µs
│ sql cpu time: 18µs
│ actual row count: 1
│ equality: (oid) = (reltype)
│
├── • render
│ │ execution time: 8µs
│ │ sql cpu time: 8µs
│ │ actual row count: 1
│ │
│ └── • group (hash)
│ │ sql nodes: n1
│ │ execution time: 21µs
│ │ sql cpu time: 21µs
│ │ actual row count: 1
│ │ group by: rownum
│ │
│ └── • sort
│ │ sql nodes: n1
│ │ execution time: 18µs
│ │ sql cpu time: 18µs
│ │ actual row count: 1
│ │ order: +attnum
│ │
│ └── • merge join (left outer)
│ │ sql nodes: n1
│ │ execution time: 1ms
│ │ sql cpu time: 1ms
│ │ actual row count: 1
│ │ equality: (oid) = (reltype)
│ │
│ ├── • ordinality
│ │ │ sql nodes: n1
│ │ │ execution time: 2µs
│ │ │ sql cpu time: 2µs
│ │ │ actual row count: 1
│ │ │
│ │ └── • merge join (left outer)
│ │ │ sql nodes: n1
│ │ │ execution time: 42µs
│ │ │ sql cpu time: 42µs
│ │ │ actual row count: 1
│ │ │ equality: (oid) = (rngtypid)
│ │ │
│ │ ├── • merge join
│ │ │ │ sql nodes: n1
│ │ │ │ execution time: 30µs
│ │ │ │ sql cpu time: 30µs
│ │ │ │ actual row count: 1
│ │ │ │ equality: (oid) = (typnamespace)
│ │ │ │
│ │ │ ├── • virtual table
│ │ │ │ sql nodes: n1
│ │ │ │ execution time: 8µs
│ │ │ │ sql cpu time: 8µs
│ │ │ │ actual row count: 5
│ │ │ │ table: pg_namespace@pg_namespace_oid_idx
│ │ │ │
│ │ │ └── • sort
│ │ │ │ sql nodes: n1
│ │ │ │ execution time: 9µs
│ │ │ │ sql cpu time: 9µs
│ │ │ │ actual row count: 1
│ │ │ │ order: +typnamespace
│ │ │ │
│ │ │ └── • hash join (right outer)
│ │ │ │ sql nodes: n1
│ │ │ │ execution time: 158µs
│ │ │ │ sql cpu time: 158µs
│ │ │ │ actual row count: 1
│ │ │ │ equality: (oid) = (typelem)
│ │ │ │ pred: typlen = -1
│ │ │ │
│ │ │ ├── • virtual table
│ │ │ │ sql nodes: n1
│ │ │ │ execution time: 3ms
│ │ │ │ sql cpu time: 309µs
│ │ │ │ actual row count: 465
│ │ │ │ table: pg_type@primary
│ │ │ │ virtual table filter
│ │ │ │
│ │ │ └── • virtual table
│ │ │ sql nodes: n1
│ │ │ execution time: 25µs
│ │ │ sql cpu time: 13µs
│ │ │ actual row count: 1
│ │ │ table: pg_type@pg_type_oid_idx
│ │ │ spans: [/2951 - /2951]
│ │ │
│ │ └── • filter
│ │ │ sql nodes: n1
│ │ │ execution time: 1µs
│ │ │ sql cpu time: 1µs
│ │ │ actual row count: 0
│ │ │ filter: rngtypid = 2951
│ │ │
│ │ └── • virtual table
│ │ sql nodes: n1
│ │ execution time: 12µs
│ │ sql cpu time: 9µs
│ │ actual row count: 0
│ │ table: pg_range@primary
│ │
│ └── • virtual table lookup join
│ │ sql nodes: n1
│ │ execution time: 23µs
│ │ sql cpu time: 23µs
│ │ actual row count: 0
│ │ table: pg_attribute@pg_attribute_attrelid_idx
│ │ equality: (oid) = (attrelid)
│ │ pred: (attnum > 0) AND (NOT attisdropped)
│ │
│ └── • filter
│ │ sql nodes: n1
│ │ execution time: 8µs
│ │ sql cpu time: 8µs
│ │ actual row count: 0
│ │ filter: reltype = 2951
│ │
│ └── • virtual table
│ sql nodes: n1
│ execution time: 2ms
│ sql cpu time: 286µs
│ actual row count: 398
│ table: pg_class@primary
│
└── • render
│ execution time: 2µs
│ sql cpu time: 2µs
│ actual row count: 0
│
└── • virtual table lookup join
│ sql nodes: n1
│ execution time: 34µs
│ sql cpu time: 34µs
│ actual row count: 0
│ table: pg_attribute@pg_attribute_attrelid_idx
│ equality: (oid) = (attrelid)
│ pred: (attnum > 0) AND (NOT attisdropped)
│
└── • filter
│ sql nodes: n1
│ execution time: 7µs
│ sql cpu time: 7µs
│ actual row count: 0
│ filter: reltype = 2951
│
└── • virtual table
sql nodes: n1
execution time: 2ms
sql cpu time: 317µs
actual row count: 398
table: pg_class@primary
Jira issue: CRDB-70351
- Lingua principale
- Go
- Stelle
- 32.6k
- Fork
- 4.2k
- Metriche di merge delle PR
- Nessuna PR unita negli ultimi 30g
Preparare l'ambiente
- Nessun Dockerfile né file Docker Compose
- Nessun modello di pull request
- Leggi la guida per i contributori
Come iniziare
- Leggi tutta la issue e poi la guida ai contributi del progetto.
- Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
- Fai un fork del repository e lavora su un branch.
- Apri una pull request che faccia riferimento al numero della issue.
Altre issue di cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
cockroachdb/cockroach#176245 · 1 commento ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinForse già presa @1AbdullahButt l’ha presa 14 giorni fa. ApertaC-bug O-community X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
cockroachdb/cockroach#176228 · 1 commento ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEForse di nuovo libera @darshpatel1052 l’ha presa 18 giorni fa e non c’è nessuna pull request aperta. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 62/100
cockroachdb/cockroach#176223 · 2 commenti ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutForse già presa @dajiaohuang l’ha presa 14 giorni fa. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 1/5 Meno di un'ora Idoneità per principianti 84/100
cockroachdb/cockroach#176210 · 1 commento ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
cockroachdb/cockroach#176150 · 1 commento ·
Tutte le issue di cockroachdb/cockroach
Issue simili
-
☢️ Bug v3
Difficoltà 2/5 1-3 ore Idoneità per principianti 82/100
I maintainer di solito rispondono entro 1 giorno
-
area/import-export area/projects concern/ux
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
go-vikunja/vikunja#4455 · 1 commento ·
I maintainer di solito rispondono entro 2 giorni
-
Dependabot PRs no longer merge automaticallyForse già presa @dwisiswant0 l’ha presa oggi. Aperta
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
projectdiscovery/urlfinder#160 ·
I maintainer di solito rispondono entro 1 giorno
-
database
Difficoltà 2/5 1-3 ore Idoneità per principianti 72/100
chnm/legal-modernism#450 ·
I maintainer di solito rispondono entro 1 giorno
-
tech-debt
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
I maintainer di solito rispondono entro 1 giorno