Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: SHOW DATABASES shouldn't have to iterate over schemas #49339

Closed
jordanlewis opened this issue May 20, 2020 · 0 comments
Closed

sql: SHOW DATABASES shouldn't have to iterate over schemas #49339

jordanlewis opened this issue May 20, 2020 · 0 comments
Assignees
@rohany
Labels
C-performance Perf of queries or internals. Solution not expected to change functional behavior.

Comments

@jordanlewis
Copy link
Member

SHOW DATABASES uses an internal table that has to iterate over schemas once per database. This is really unfortunate. SHOW DATABASES should be switched to use pg_catalog.databases or another table that doesn't have to do any per-database extra roundtrips.

This should live under the "too many roundtrips in DDL" work scope, IMO, so assigning to @rohany.
@jordanlewis jordanlewis added the C-performance Perf of queries or internals. Solution not expected to change functional behavior. label May 20, 2020
@rohany rohany mentioned this issue May 25, 2020
Merged
rohany added a commit to rohany/cockroach that referenced this issue Jun 4, 2020
@rohany
sql: make SHOW DATABASES not need to access schemas
dea66f8 
Fixes cockroachdb#49339.

This PR fixes a performance regression in the `SHOW DATABASES`
statement. The regression was caused by the table backing the show
databases statement performing a lookup of each databases' schemas.

Release note (performance improvement): Fix a performance regression in
the `SHOW DATABASES` command introduced in 20.1.

Release note (sql change): Add the `crdb_internal.databases` virtual
table.
@craig craig bot closed this as completed in a18b887 Jun 6, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rohany rohany

Labels
C-performance Perf of queries or internals. Solution not expected to change functional behavior.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jordanlewis @rohany