is there any way asyncpg could be configured to not do a giant query for PG custom types when you prepare

[zzzeek]

hey there -

as you know, SQLAlchemy relies upon knowing what the names of columns will be in result sets, and for asycnpg that forces us to use PreparedStatements for statements that we want to fetch rows from.

When the preparedstatement has a custom type like an ENUM inside of it, asyncpg does a giant query up front to cache information about the datatype, here it is from my test case below inside my PG SQL log:

2023-09-17 10:04:41.545 EDT [1728595] LOG:  execute __asyncpg_stmt_3__: WITH RECURSIVE typeinfo_tree(
        oid, ns, name, kind, basetype, elemtype, elemdelim,
        range_subtype, attrtypoids, attrnames, depth)
    AS (
        SELECT
            ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
            ti.elemtype, ti.elemdelim, ti.range_subtype,
            ti.attrtypoids, ti.attrnames, 0
        FROM
                (
            SELECT
                t.oid                           AS oid,
                ns.nspname                      AS ns,
                t.typname                       AS name,
                t.typtype                       AS kind,
                (CASE WHEN t.typtype = 'd' THEN
                    (WITH RECURSIVE typebases(oid, depth) AS (
                        SELECT
                            t2.typbasetype      AS oid,
                            0                   AS depth
                        FROM
                            pg_type t2
                        WHERE
                            t2.oid = t.oid

                        UNION ALL

                        SELECT
                            t2.typbasetype      AS oid,
                            tb.depth + 1        AS depth
                        FROM
                            pg_type t2,
                            typebases tb
                        WHERE
                           tb.oid = t2.oid
                           AND t2.typbasetype != 0
                   ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)

                   ELSE NULL
                END)                            AS basetype,
                t.typelem                       AS elemtype,
                elem_t.typdelim                 AS elemdelim,
                COALESCE(
                    range_t.rngsubtype,
                    multirange_t.rngsubtype)    AS range_subtype,
                (CASE WHEN t.typtype = 'c' THEN
                    (SELECT
                        array_agg(ia.atttypid ORDER BY ia.attnum)
                    FROM
                        pg_attribute ia
                        INNER JOIN pg_class c
                            ON (ia.attrelid = c.oid)
                    WHERE
                        ia.attnum > 0 AND NOT ia.attisdropped
                        AND c.reltype = t.oid)

                    ELSE NULL
                END)                            AS attrtypoids,
                (CASE WHEN t.typtype = 'c' THEN
                    (SELECT
                        array_agg(ia.attname::text ORDER BY ia.attnum)
                    FROM
                        pg_attribute ia
                        INNER JOIN pg_class c
                            ON (ia.attrelid = c.oid)
                    WHERE
                        ia.attnum > 0 AND NOT ia.attisdropped
                        AND c.reltype = t.oid)

                    ELSE NULL
                END)                            AS attrnames
            FROM
                pg_catalog.pg_type AS t
                INNER JOIN pg_catalog.pg_namespace ns ON (
                    ns.oid = t.typnamespace)
                LEFT JOIN pg_type elem_t ON (
                    t.typlen = -1 AND
                    t.typelem != 0 AND
                    t.typelem = elem_t.oid
                )
                LEFT JOIN pg_range range_t ON (
                    t.oid = range_t.rngtypid
                )
                LEFT JOIN pg_range multirange_t ON (
                    t.oid = multirange_t.rngmultitypid
                )
        )
     AS ti
        WHERE
            ti.oid = any($1::oid[])

        UNION ALL

        SELECT
            ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
            ti.elemtype, ti.elemdelim, ti.range_subtype,
            ti.attrtypoids, ti.attrnames, tt.depth + 1
        FROM
                (
            SELECT
                t.oid                           AS oid,
                ns.nspname                      AS ns,
                t.typname                       AS name,
                t.typtype                       AS kind,
                (CASE WHEN t.typtype = 'd' THEN
                    (WITH RECURSIVE typebases(oid, depth) AS (
                        SELECT
                            t2.typbasetype      AS oid,
                            0                   AS depth
                        FROM
                            pg_type t2
                        WHERE
                            t2.oid = t.oid

                        UNION ALL

                        SELECT
                            t2.typbasetype      AS oid,
                            tb.depth + 1        AS depth
                        FROM
                            pg_type t2,
                            typebases tb
                        WHERE
                           tb.oid = t2.oid
                           AND t2.typbasetype != 0
                   ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)

                   ELSE NULL
                END)                            AS basetype,
                t.typelem                       AS elemtype,
                elem_t.typdelim                 AS elemdelim,
                COALESCE(
                    range_t.rngsubtype,
                    multirange_t.rngsubtype)    AS range_subtype,
                (CASE WHEN t.typtype = 'c' THEN
                    (SELECT
                        array_agg(ia.atttypid ORDER BY ia.attnum)
                    FROM
                        pg_attribute ia
                        INNER JOIN pg_class c
                            ON (ia.attrelid = c.oid)
                    WHERE
                        ia.attnum > 0 AND NOT ia.attisdropped
                        AND c.reltype = t.oid)

                    ELSE NULL
                END)                            AS attrtypoids,
                (CASE WHEN t.typtype = 'c' THEN
                    (SELECT
                        array_agg(ia.attname::text ORDER BY ia.attnum)
                    FROM
                        pg_attribute ia
                        INNER JOIN pg_class c
                            ON (ia.attrelid = c.oid)
                    WHERE
                        ia.attnum > 0 AND NOT ia.attisdropped
                        AND c.reltype = t.oid)

                    ELSE NULL
                END)                            AS attrnames
            FROM
                pg_catalog.pg_type AS t
                INNER JOIN pg_catalog.pg_namespace ns ON (
                    ns.oid = t.typnamespace)
                LEFT JOIN pg_type elem_t ON (
                    t.typlen = -1 AND
                    t.typelem != 0 AND
                    t.typelem = elem_t.oid
                )
                LEFT JOIN pg_range range_t ON (
                    t.oid = range_t.rngtypid
                )
                LEFT JOIN pg_range multirange_t ON (
                    t.oid = multirange_t.rngmultitypid
                )
        )
     ti,
            typeinfo_tree tt
        WHERE
            (tt.elemtype IS NOT NULL AND ti.oid = tt.elemtype)
            OR (tt.attrtypoids IS NOT NULL AND ti.oid = any(tt.attrtypoids))
            OR (tt.range_subtype IS NOT NULL AND ti.oid = tt.range_subtype)
            OR (tt.basetype IS NOT NULL AND ti.oid = tt.basetype)
    )

    SELECT DISTINCT
        *,
        basetype::regtype::text AS basetype_name,
        elemtype::regtype::text AS elemtype_name,
        range_subtype::regtype::text AS range_subtype_name
    FROM
        typeinfo_tree
    ORDER BY
        depth DESC

2023-09-17 10:04:41.545 EDT [1728595] DETAIL:  parameters: $1 = '{2732440}'

This is I assume once per connection/type, but we still are getting performance concerns about it as we see in #10356. SQLAlchemy uses a connection pool by default however this is still an upfront cost and some folks don't use the pool.

I would assume the purpose of this query has to do with get_attributes() having all the information about the oid for the type.

We don't actually need the "type" part of get_attributes(), just the names (we already know the types on our end). Is there a possibility asyncpg could have some kind of connection parameter, or prepared statement parameter, that is something to the effect use_varchar_for_custom_type or omit_custom_type or something such that this giant query on prepare can be skipped?

Demo that produces the query in question:

import asyncio

import asyncpg

async def main():
    conn = await asyncpg.connect(
        user="scott", password="tiger", database="test"
    )

    await conn.execute("DROP TABLE IF EXISTS mood_test")
    await conn.execute(
        "DROP TYPE IF EXISTS mood"
    )
    await conn.execute(
        "CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')"
    )

    await conn.execute(
        "CREATE TABLE mood_test (id SERIAL primary key, current_mood mood)"
    )

    await conn.fetch("SELECT 'right before the prepare'")
    pp = await conn.prepare("SELECT id, current_mood FROM mood_test")

    await conn.fetch("SELECT 'right after the prepare'")

    cols = pp.get_attributes()

    await conn.fetch("SELECT 'right after the get_attributes'")

    print(f"attributes: {cols}")

    await conn.close()

asyncio.run(main())

[elprans]

The issue is the Postgres JIT that gets erroneously invoked on the query. Turning jit off makes it run under a millisecond. I have a patch that explicitly disables jit for introspection queries. Will push soon.

[zzzeek]

oh i see, it would still run the query but the PG server side of it will just push it through without trying to optimize it, IIUC

[elprans]

oh i see, it would still run the query but the PG server side of it will just push it through without trying to optimize it, IIUC

Yes. The fact that Postgres is even trying to JIT-compile this query is arguably a Postgres cost estimation bug, because the cost of compilation here far outweighs any runtime benefits. The query is plenty fast as-is and there isn't any heavy expression computation that might benefit from compiling it to machine code.

[elprans]

Also, to better answer your original question, the introspection query is necessary because Postgres does not send any type information in the course of normal query flow other than type OIDs. In order to actually be able to decode data bytes, asyncpg must know what it is decoding, so the introspection query is ran whenever there is a previously unseen type OID (neither a standard type nor a previously introspected one).

You can remove the need for introspection by pre-populating the type OID mapping via set_type_codec() or set_builtin_type_codec(), however that requires you to know which types are what kind ahead of time.

[zzzeek]

by "decode" I assume you mean on the result fetching side, does that mean this same introspection occurs when using conn.fetch() without a prepared statement? Other drivers seem to be willing to let unknown OIDs be passed through as bytes and/or bytes that they somehow guess can be treated as strings, but I haven't confirmed they aren't sneaking in OID instrospection in there somehow

[zzzeek]

that is, we have some such codecs set up in our impl for this, like this:

        await asyncpg_connection.set_type_codec(
            "cidr",
            encoder=lambda s: s,
            decoder=lambda s: s,
            schema="pg_catalog",
            format="text",
        )

I guess other drivers do essentially that for "unknown" oids. If we had that ability here, I dont think it would cause problems for us, since we dont expect custom types besides ENUM to have any particular behaviors.

doesn't matter much, if the query's time is reduced then we wont get more complaints :)