cockroachdb / cockroachdb/cockroach
sql: table inspection query is suboptimal
- 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
Assessment
This issue has not been assessed yet.