sql: asyncpg type introspection query (WITH RECURSIVE typeinfo_tree) takes 65-165 ms on an empty cluster and 340-525 ms on Cloud Standard

Aperta
#176,167 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

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
Stack tecnologico
postgresql, python, sql, sqlalchemy

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

A-sql-vtables A-tools-asyncpg C-performance O-support T-sql-queries X-move-internal

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

  1. cockroach start-single-node --insecure --store=type=mem,size=2GiB. Leave it empty, so defaultdb has 465 rows in pg_type and 398 in pg_class on v26.4.0-beta.1.
  2. pip install asyncpg==0.31.0.
  3. 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_types call. It also runs EXPLAIN ANALYZE on the introspection query with $1 replaced by ARRAY[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 cte node 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 of pg_class@primary (398 rows each) for the composite-type subqueries (attrtypoids and attrnames). Both run even though those subqueries sit behind CASE 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 cte node takes about 326 ms, while the virtual-table scans under it report about 20 ms in total. Every pg_catalog virtual table shows missing 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 ANALYZE output are at the bottom of this issue.
  • The query text is INTRO_LOOKUP_TYPES_13 in 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:

  1. Is there a fix path for this query shape, such as virtual-index lookups on pg_type/pg_class inside the recursive step, or not planning the typtype = 'c' branches for non-composite types? Would the in-flight PushJoinIntoApplyJoin work help?
  2. Is there a session or cluster setting that reduces the cost in the meantime?
  3. 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

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di cockroachdb/cockroach

Tutte le issue di cockroachdb/cockroach

Issue simili

Altre issue su Go

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.