sql: pg_catalog.obj_description is very slow
timgraham opened this issue · 2 comments
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
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 #87947)
- @DrewKimball (commented on #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.
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.