Method _index_query

lib/sqlalchemy/dialects/postgresql/base.py:4970–5121

(self)

    @util.memoized_property
    def _index_query(self):
        # NOTE: pg_index is used as from two times to improve performance,
        # since extraing all the index information from `idx_sq` to avoid
        # the second pg_index use leads to a worse performing query in
        # particular when querying for a single table (as of pg 17)
        # NOTE: repeating oids clause improve query performance

        # subquery to get the columns
        idx_sq = (
            select(
                pg_catalog.pg_index.c.indexrelid,
                pg_catalog.pg_index.c.indrelid,
                sql.func.unnest(pg_catalog.pg_index.c.indkey).label("attnum"),
                sql.func.unnest(pg_catalog.pg_index.c.indclass).label(
                    "att_opclass"
                ),
                sql.func.generate_subscripts(
                    pg_catalog.pg_index.c.indkey, 1
                ).label("ord"),
            )
            .where(
                ~pg_catalog.pg_index.c.indisprimary,
                pg_catalog.pg_index.c.indrelid.in_(bindparam("oids")),
            )
            .subquery("idx")
        )

        attr_sq = (
            select(
                idx_sq.c.indexrelid,
                idx_sq.c.indrelid,
                idx_sq.c.ord,
                # NOTE: always using pg_get_indexdef is too slow so just
                # invoke when the element is an expression
                sql.case(
                    (
                        idx_sq.c.attnum == 0,
                        pg_catalog.pg_get_indexdef(
                            idx_sq.c.indexrelid, idx_sq.c.ord + 1, True
                        ),
                    ),
                    # NOTE: need to cast this since attname is of type "name"
                    # that's limited to 63 bytes, while pg_get_indexdef
                    # returns "text" so its output may get cut
                    else_=pg_catalog.pg_attribute.c.attname.cast(TEXT),
                ).label("element"),
                (idx_sq.c.attnum == 0).label("is_expr"),
                # since it's converted to array cast it to bigint (oid are
                # "unsigned four-byte integer") to make it easier for
                # dialects to interpret
                idx_sq.c.att_opclass.cast(BIGINT),
            )
            .select_from(idx_sq)
            .outerjoin(
                # do not remove rows where idx_sq.c.attnum is 0
                pg_catalog.pg_attribute,
                sql.and_(
                    pg_catalog.pg_attribute.c.attnum == idx_sq.c.attnum,