sql: Django introspection query performance regression

[timgraham]

Describe the problem

A regression in 072ec8d is causing the following query (which Django's test suite runs quite often) to take several seconds (instead of milliseconds).

To Reproduce

            SELECT
                c.relname,
                CASE
                    WHEN c.relispartition THEN 'p'
                    WHEN c.relkind IN ('m', 'v') THEN 'v'
                    ELSE 't'
                END
            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 v23.1.0-alpha.1-168-g61f7c81657
• Build Time: 2022/12/19 23:09:39

Jira issue: CRDB-22612

[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)

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.}

[timgraham]

@rafiss I'd guess #90649 is at fault.

[rafiss]

In my tests, this query takes ~450ms in a database that has 38 tables.

Also, it seems to me that the filter should be updated to something more like:

n.nspname NOT IN ('pg_catalog', 'pg_toast', 'pg_extension', 'information_schema', 'crdb_internal')

[timgraham]

You can use ./tests/runtests.py --settings=test_roach inspectdb to reproduce the slowdown. Excluding those additional namespaces doesn't seem to make much (any?) speed difference.

[timgraham]

This query still takes 1-2 seconds with 23.1 alpha 8 and the current nightly and the full Django test suite runtime is up from ~40 minutes with 22.2.x to ~1.5 hrs (though I'm not sure it's entirely attributable to this query). Do you want to reopen this issue or track it in another one? (I saw some discussion at #96960 (comment).)

Edit: I created #100871 for this follow up.