sql: severe regression in ORM query performance {django} #96218
Description
I was investigating ORM query performance in CRDB 22.2.2 as well as latest (g69dd453d0e) and noticed some major regressions for Django's table introspection query when testing on Roachprod.
Steps to repro:
- Create two separate 9 node multi region roachprod cluster using 22.2.2 and latest
roachprod create rp-test -n 9 --gce-zones 'us-east4-a','us-east4-a','us-east4-a','us-west2-a','us-west2-a','us-west2-a','us-central1-a','us-central1-a','us-central1-a' && roachprod stage rp-test release v22.2.2 && roachprod start rp-test:1-9
- Create lots of tables. (15k)
for i in $(seq 1 15000); do echo "CREATE TABLE t_$i (i INT PRIMARY KEY, j INT);" >> out.sql;
cat out.sql | roachprod sql rp-test
- Run the following query in both clusters:
SELECT
c.relname,
CASE
WHEN c.relispartition THEN 'p'
WHEN c.relkind IN ('m', 'v') THEN 'v'
ELSE 't'
END,
obj_description(c.oid, 'pg_class')
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);
- Observe that latest (g69dd453d0e) takes a lot longer than 22.2.2 and the query plans are different.
- Gist for 22.2.2 - takes 127 seconds.
- Gist for latest (
g69dd453d0e) - takes 694 seconds
Jira issue: CRDB-24010