sql: asyncpg type introspection query (WITH RECURSIVE typeinfo_tree) takes 65-165 ms on an empty cluster and 340-525 ms on Cloud Standard
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 44/100
- Issue type
- Bug
- Clarity
- Mostly clear
- Activity status
- Active
- Domain
- databases, performance
Research direction
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.
Written by the indexing model from the issue text.
Description
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
- Dominant language
- Go
- Stars
- 32.6k
- Forks
- 4.2k
- PR merge metrics
- No merged PRs in 30d
Getting set up
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day