cockroachdb / cockroachdb/cockroach

sql: table inspection query is suboptimal

Open
#117,334 2 comments 0 reactions 0 assignees View on GitHub
C-enhancement O-support P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Using a command like the following: `\d tablename` allows the user to view details about a particular table. The query that implements this command looks like this:
```
SELECT c.oid,
n.nspname,
c.relname,
c.relkind,
c.relpersistence,
c.relchecks > 0,
c.relhasindex,
EXISTS(SELECT 1 FROM pg_catalog.pg_constraint WHERE conrelid = c.oid AND contype = 'f') AS relhasfkey,
EXISTS(SELECT 1 FROM pg_catalog.pg_constraint WHERE confrelid = c.oid AND contype = 'f') AS relhasifkey,
EXISTS(SELECT 1 FROM pg_catalog.pg_statistic_ext WHERE stxrelid = c.oid)
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE %[1]s
ORDER BY 2,3
```
Unfortunately, even though we only need to check if the `SELECT 1 FROM pg_catalog.pg_statistic_ext WHERE stxrelid = c.oid` subquery returns at least one row, we end up scanning the entire virtual table due to lack of a suitable index (or any index). The table can become quite large; in the case of one customer, about 200k rows. This causes the query to become very slow, on the order of several minutes.

We should either optimize the query so we can use a virtual index scan, or find some way to avoid referencing this table altogether.

Jira issue: CRDB-35123

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.