sql: pg_catalog.obj_description is very slow

[timgraham]

Describe the problem

A new feature in Django 4.2 (to be released in April) adds obj_description to an introspection query. The new query takes on the order of ten seconds when running Django's test suite.

To Reproduce

SELECT
    c.relname,
    CASE
        WHEN c.relispartition THEN 'p'
        WHEN c.relkind IN ('m', 'v') THEN 'v'
        ELSE 't'
    END,
    obj_description(c.oid)
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
    AND pg_catalog.pg_table_is_visible(c.oid)

Environment:

• CockroachDB version v22.2.2 and v22.1.13. (I haven't been unable to test nightly due to another slowdown).

Additional context

The run time of Django's test suite went from ~1 hour to ~7 or more hours.

#87947 may be related, however, Django's introspection query that uses col_description doesn't appear to be slow.

Jira issue: CRDB-23305

Epic CRDB-23454

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-sessions (found keywords: pg_,django)
• @rafiss (commented on pg_catalog.col_description is very slow #87947)
• @DrewKimball (commented on pg_catalog.col_description is very slow #87947)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for the report! We should be able to improve the performance on master, but it depends on other changes that might make it impossible to improve in v22.2 and v22.1.

Just for your awareness, the 1-parameter form of obj_description is deprecated in PostgreSQL. A similar concern applies to CockroachDB, though we haven't documented this. https://www.postgresql.org/docs/15/functions-info.html

obj_description ( object oid ) → text

Returns the comment for a database object specified by its OID alone. This is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned.