server: refactor index recommendations for the DatabaseDetails API #93909

THardy98 · 2022-12-19T15:18:02Z

Currently, the DatabaseDetails API on the admin server fetches index recommendations incredibly inefficiently.

Currently for each table of the database, it queries a 4-way join (index_usage_statistics, table_indexes, pg_index, and pg_indexes) including a RPC fanout. Then it sums each table-level result to get the number of index recommendations for the database. Instead, we can issue a single query to fetch the index recommendations at the database-level and can likely consolidate the query to avoid joining on 4 tables.

Jira issue: CRDB-22598

The text was updated successfully, but these errors were encountered:

Resolves: cockroachdb#93909 Previously, the DatabaseDetails API would fetch the index recommendations of the database by executing an expensive query for **each table of the database**, then coalesce the results of each table to get the database-level result. This was needlessly expensive and impractical, particularly so for large schemas. This change ensures that only a single query is executed **per database** to fetch its index recommendations. Release note (performance improvement): Refactored the query logic when fetching database index recommendations for the DatabaseDetails API endpoint, greatly reducing the query time and cost, particularly for large schemas.

93644: sql: better `SHOW RANGES` and changes to `crdb_internal.ranges{,_no_leases}` r=ecwall a=knz Fixes #93617. Fixes #80906. Fixes #93198. Epic: CRDB-22701 The output of `crdb_internal.ranges{,_no_leases}` and `SHOW RANGES` was irreparably broken by the introduction of range coalescing (ranges spanning multiple tables/databases). Moreover, the start/end keys of SHOW RANGES were often empty or NULL due to incorrect/excessive truncation. This commit fixes this by introducing a new design for SHOW RANGES and tweaking the definition of `crdb_internal.ranges{,_no_leases}`. Note: THIS IS A BREAKING CHANGE. See the "backward-incompatible change" release notes below for suggestions on updating client code. ---- **Short documentation.** <details> The revised syntax is now: ``` SHOW CLUSTER RANGES [WITH <options>] SHOW RANGES [FROM DATABASE <dbname> | FROM CURRENT_CATALOG] [ WITH <options> ] SHOW RANGES FROM TABLE <tablename> [ WITH <options> ] SHOW RANGES FROM INDEX <idxname> [ WITH <options> ] <options> is a combination of TABLES, INDEXES, KEYS, DETAILS and EXPLAIN. ``` New syntax: `SHOW CLUSTER RANGES`, `SHOW RANGES` with no `FROM`, `FROM CURRENT_CATALOG`, `WITH` clause. In summary, we have: - `SHOW CLUSTER RANGES` which includes all ranges, including those not belonging to any table. - `SHOW RANGES [FROM DATABASE | FROM CURRENT_CATALOG]` which includes only ranges overlapping with any table in the target db. Note: `SHOW RANGES` without target (NEW!) is an alias for `SHOW RANGES FROM CURRENT_CATALOG`. - `SHOW RANGES FROM TABLE` selects only ranges that overlap with the given table. - `SHOW RANGES FROM INDEX` selects only ranges that overlap with the given index. Then: - if `WITH TABLES` is specified, the rows are duplicated to detail each table included in each range (1 row per range-table intersection). - if `WITH INDEXES` is specified, the rows are duplicated to detail each index included in each range (1 row per range-index intersection). - otherwise, there is just 1 row per range. In summary: | Statement | Row identity | Before | After | |----------------------------------------------------|----------------------------------|----------------------------|----------------------------------| | `SHOW RANGES FROM DATABASE` | rangeID | Includes schema/table name | (CHANGE) No schema/table name | | `SHOW RANGES FROM TABLE` | rangeID | Includes index name | (CHANGE) No index name | | `SHOW RANGES FROM INDEX` | rangeID | Includes index name | Unchanged | | `SHOW RANGES FROM DATABASE ... WITH TABLES` (NEW) | rangeID, schema/table name | N/A | Includes schema/table name | | `SHOW RANGES FROM DATABASE ... WITH INDEXES` (NEW) | rangeID, schema/table/index name | N/A | Includes schema/table/index name | | `SHOW RANGES FROM TABLE ... WITH INDEXES` (NEW) | rangeID, index name | N/A | Includes index name | | `SHOW CLUSTER RANGES` (NEW) | rangeID | N/A | | | `SHOW CLUSTER RANGES WITH TABLES` (NEW) | rangeID, schema/table name | N/A | Includes db/schema/table name | | `SHOW CLUSTER RANGES WITH INDEXES` (NEW) | rangeID, schema/table/index name | N/A | Includes db/sch/table/index name | | Statement | Start/end key column, before | Start/end key column, after | |----------------------------------------------------|------------------------------|---------------------------------------| | `SHOW RANGES FROM DATABASE` | Truncates table/index IDs | (CHANGE) Includes table/index ID | | `SHOW RANGES FROM TABLE` | Truncates table/index IDs | (CHANGE) Includes table/index ID | | `SHOW RANGES FROM INDEX` | Truncates table/index IDs | Unchanged | | `SHOW RANGES FROM DATABASE ... WITH TABLES` (NEW) | N/A | Includes table/index ID | | `SHOW RANGES FROM DATABASE ... WITH INDEXES` (NEW) | N/A | Includes table/index ID | | `SHOW RANGES FROM TABLE ... WITH INDEXES` (NEW) | N/A | Truncates table ID, includes index ID | | `SHOW CLUSTER RANGES` (NEW) | N/A | Includes table/index ID | | `SHOW CLUSTER RANGES WITH TABLES` (NEW) | N/A | Includes table/index ID | | `SHOW CLUSTER RANGES WITH INDEXES` (NEW) | N/A | Includes table/index ID | In any case, all the columns from `crdb_internal.ranges_no_leases` are included. By default, the start/end key boundaries are pretty-printed as in previous versions. Then: - if `WITH KEYS` is specified, the raw key bytes are exposed alongside the pretty-printed key boundaries. - if `WITH DETAILS` is specified, extra _expensive_ information is included in the result, as of `crdb_internal.ranges`. (requires more roundtrips; makes the operation slower overall) Then: - if `WITH EXPLAIN` is specified, the statement simply returns the text of the SQL query it would use if `WITH EXPLAIN` was not specified. This can be used for learning or troubleshooting. </details> See text of release notes below for more details; also the explanatory comment at the top of `pkg/sql/delegate/show_ranges.go`. ---- **Example use.** <details> To test this, use for example the following setup: ``` > -- Enable merge of adjacent ranges with same zone config. > set cluster setting spanconfig.host_coalesce_adjacent.enabled = true; > -- Table t has two indexes with some split points. > create table t(x int primary key, y int); > create index sec_idx on t(y); > alter index t@primary split at values(3); > alter index t@sec_idx split at values(3); > -- Tables u and v share a range with t@sec_idx. > create table u(x int); > create table v(x int); > -- Make some other tables with forced split points due to different > -- zone configs. > create schema otherschema; > create table otherschema.w(x int); > create table otherschema.z(x int); > alter table otherschema.w configure zone using num_replicas = 5; > alter table otherschema.z configure zone using num_replicas = 7; ``` Example output for `SHOW RANGES FROM DATABASE`: ``` > show ranges from database defaultdb; -- 1 row per range > show ranges from current_catalog; -- implicit db from session start_key | end_key | range_id | ... -----------------+----------------+----------+---- /Table/104 | /Table/104/1/3 | 56 | ... /Table/104/1/3 | /Table/104/2 | 57 | ... /Table/104/2 | /Table/104/2/3 | 55 | ... /Table/104/2/3 | /Table/108 | 58 | ... /Table/108 | /Table/109 | 59 | ... /Table/109 | /Max | 60 | ... ``` New syntax: `WITH TABLES` / `WITH INDEXES`: ``` > show ranges from database defaultdb with tables; -- 1 row per range/table intersection start_key | end_key | range_id | schema_name | table_name | table_start_key | table_end_key | ... -----------------+----------------+----------+-------------+------------+-----------------+----------------------+---- /Table/104 | /Table/104/1/3 | 56 | public | t | /Table/104 | /Table/105 | ... /Table/104/1/3 | /Table/104/2 | 57 | public | t | /Table/104 | /Table/105 | ... /Table/104/2 | /Table/104/2/3 | 55 | public | t | /Table/104 | /Table/105 | ... /Table/104/2/3 | /Table/108 | 58 | public | t | /Table/104 | /Table/105 | ... /Table/104/2/3 | /Table/108 | 58 | public | u | /Table/105 | /Table/106 | ... /Table/104/2/3 | /Table/108 | 58 | public | v | /Table/106 | /Table/107 | ... /Table/108 | /Table/109 | 59 | otherschema | w | /Table/108 | /Table/109 | ... /Table/109 | /Max | 60 | otherschema | z | /Table/109 | /Table/109/PrefixEnd | ... ``` ``` > show ranges from database defaultdb with indexes; -- 1 row per range/index intersection start_key | end_key | range_id | schema_name | table_name | index_name | index_start_key | index_end_key | ... -----------------+----------------+----------+-------------+------------+------------+-----------------+---------------+---- /Table/104 | /Table/104/1/3 | 56 | public | t | t_pkey | /Table/104/1 | /Table/104/2 | ... /Table/104/1/3 | /Table/104/2 | 57 | public | t | t_pkey | /Table/104/1 | /Table/104/2 | ... /Table/104/2 | /Table/104/2/3 | 55 | public | t | sec_idx | /Table/104/2 | /Table/104/3 | ... /Table/104/2/3 | /Table/108 | 58 | public | t | sec_idx | /Table/104/2 | /Table/104/3 | ... /Table/104/2/3 | /Table/108 | 58 | public | u | u_pkey | /Table/105/1 | /Table/105/2 | ... /Table/104/2/3 | /Table/108 | 58 | public | v | v_pkey | /Table/106/1 | /Table/106/2 | ... /Table/108 | /Table/109 | 59 | otherschema | w | w_pkey | /Table/108/1 | /Table/108/2 | ... /Table/109 | /Max | 60 | otherschema | z | z_pkey | /Table/109/1 | /Table/109/2 | ... ``` Example output for `SHOW RANGES FROM TABLE`: ``` > show ranges from table t; start_key | end_key | range_id | ... ---------------+--------------------+----------+---- …/<TableMin> | …/1/3 | 56 | ... …/1/3 | …/2 | 57 | ... …/2 | …/2/3 | 55 | ... …/2/3 | <after:/Table/108> | 58 | ... ``` ``` > show ranges from table u; start_key | end_key | range_id | ... --------------------------+--------------------+----------+---- <before:/Table/104/2/3> | <after:/Table/108> | 58 | ... ``` ``` > show ranges from table otherschema.w; start_key | end_key | range_id | ... ---------------+--------------+----------+---- …/<TableMin> | …/<TableMax> | 59 | ... ``` New syntax: `SHOW RANGES FROM TABLE ... WITH INDEXES`: ``` > show ranges from table t with indexes; start_key | end_key | range_id | index_name | index_start_key | index_end_key | ... ---------------+--------------------+----------+------------+-----------------+---------------+---- …/<TableMin> | …/1/3 | 56 | t_pkey | …/1 | …/2 | ... …/1/3 | …/<IndexMax> | 57 | t_pkey | …/1 | …/2 | ... …/<IndexMin> | …/2/3 | 55 | sec_idx | …/2 | …/3 | ... …/2/3 | <after:/Table/108> | 58 | sec_idx | …/2 | …/3 | ... ``` ``` > show ranges from table u with indexes; start_key | end_key | range_id | index_name | index_start_key | index_end_key | ... --------------------------+--------------------+----------+------------+-----------------+---------------+---- <before:/Table/104/2/3> | <after:/Table/108> | 58 | u_pkey | …/1 | …/2 | ... ``` ``` > show ranges from table otherschema.w with indexes; start_key | end_key | range_id | index_name | index_start_key | index_end_key | ... ---------------+--------------+----------+------------+-----------------+---------------+---- …/<TableMin> | …/<TableMax> | 59 | w_pkey | …/1 | …/2 | ... ``` Example output for `SHOW RANGES FROM INDEX`: ``` > show ranges from index t@t_pkey; start_key | end_key | range_id | ... ---------------+--------------+----------+---- …/<TableMin> | …/3 | 56 | ... …/3 | …/<IndexMax> | 57 | ... ``` ``` > show ranges from index t@sec_idx; start_key | end_key | range_id | ... ---------------+--------------------+----------+---- …/<IndexMin> | …/3 | 55 | ... …/3 | <after:/Table/108> | 58 | ... ``` ``` > show ranges from index u@u_pkey; start_key | end_key | range_id | ... --------------------------+--------------------+----------+---- <before:/Table/104/2/3> | <after:/Table/108> | 58 | ... ``` ``` > show ranges from index otherschema.w@w_pkey; start_key | end_key | range_id | ... ---------------+--------------+----------+---- …/<TableMin> | …/<TableMax> | 59 | ... ``` See release notes below for details. </details> ---- ![attention banner](https://media.tenor.com/-AeK-iJpxuoAAAAM/pay-attention-warning.gif) **Backward-incompatible changes.** Release note (backward-incompatible change): CockroachDB now supports sharing storage ranges across multiple indexes/tables. As a result, there is no more guarantee that there is at most one SQL object (e.g. table/index/sequence/materialized view) per storage range. Therefore, the columns `table_id`, `database_name`, `schema_name`, `table_name` and `index_name` in `crdb_internal.ranges` and `.ranges_no_leases` have become nonsensical: a range cannot be attributed to a single table/index any more. As a result: - The aforementioned columns in the `crdb_internal` virtual tables have been removed. Existing code can use the SHOW RANGES statement instead, optionally using WITH KEYS to expose the raw start/end keys. - `SHOW RANGES FROM DATABASE` continues to report one row per range, but stops returning the database / schema / table / index name. - `SHOW RANGES FROM TABLE` continues to report one row per range, but stops returning the index name. Suggested replacements: - Instead of `SELECT range_id FROM crdb_internal.ranges WHERE table_name = 'x'` Use: `SELECT range_id FROM [SHOW RANGES FROM TABLE x]` - Instead of `SELECT range_id FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2` (variable / unpredictable table name or ID) Use: `SELECT range_id FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES] WHERE table_name = $1 OR table_id = $2` - Instead of `SELECT start_key FROM crdb_internal.ranges WHERE table_name = 'x'` Use: `SELECT raw_start_key FROM [SHOW RANGES FROM TABLE x WITH KEYS]` - Instead of `SELECT start_key FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2` (unpredictable / variable table name or ID) Use: `SELECT raw_start_key FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES, KEYS] WHERE table_name = $1 OR table_id = $2` Release note (backward-incompatible change): The format of the columns `start_key` and `end_key` for `SHOW RANGES FROM DATABASE` and `SHOW RANGES FROM TABLE` have been extended to include which table/index the key belong to. This is necessary because a range can now contain data from more than one table/index. Release note (backward-incompatible change): The format of the columns `start_key` and `end_key` for `SHOW RANGE ... FOR ROW` has been changed to stay consistent with the output of `SHOW RANGES FROM INDEX`. Release note (backward-incompatible change): The output of `SHOW RANGES` does not include `range_size`, `range_size_mb`, `lease_holder` and `lease_holder_localities` any more by default. This ensures that `SHOW RANGES` remains fast in the common case. Use the (NEW) option `WITH DETAILS` to include these columns. ---- **Other changes.** Release note (bug fix): In some cases the start/end key columns of the output of `SHOW RANGES` was missing. This was corrected. Release note (sql change): Two new virtual tables `crdb_internal.index_spans` and `.table_spans` have been introduced, which list the logical keyspace used by each index/table. ---- **New features.** Release note (sql change): The following new statements are introduced: - `SHOW RANGES FROM CURRENT_CATALOG` and `SHOW RANGES` without parameter: alias for `SHOW RANGES FROM DATABASE` on the session's current database. - `SHOW RANGES FROM DATABASE ... WITH TABLES` Reports at least one row per table. It's possible for the same range ID to be repeated across multiple rows, when a range spans multiple tables. - `SHOW RANGES FROM DATABASE ... WITH INDEXES` Reports at least one row per index. It's possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes. - `SHOW RANGES FROM TABLE ... WITH INDEXES` Reports at least one row per index. It's possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes. - `SHOW CLUSTER RANGES [ WITH { INDEXES | TABLES } ]` Reports ranges across the entire cluster, including ranges that don't contain table data. The behavior of `WITH INDEXES` and `WITH TABLES` is the same as for `SHOW RANGES FROM DATABASE`. Additionally, the following new options have been added to the `SHOW RANGES` statement: - `WITH KEYS`: produce the raw bytes of the start/end key boundaries. - `WITH DETAILS`: produce more details, using computations that require extra network roundtrips. Makes the operation slower overall. - `WITH EXPLAIN`: produce the text of the SQL query used to run the statement. 93657: ui: Populate database filter dropdown in stmts page with `SHOW DATABASES` sql-over-http call r=gtr a=gtr Fixes: #70461. Previously, the databases filter dropdown was populated by the `StatementsResponse` API call.This would result in some databases for which we do not receive any stmts to be ignored.According to above issue, the database filter - drop down should always be populated with cluster databases even when there are no statements or transactions for them.This commit populates the database filter dropdown using the `getDatabasesList()` API call which itself executes the`SHOW DATABASES` SQL query. Creating a new empty database from the SQL shell: <img width="960" alt="Screen Shot 2022-12-19 at 10 48 04 AM" src="https://user-images.githubusercontent.com/35943354/208500315-dd3de725-cfa2-4303-aeeb-3491a98a89d7.png"> Clicking the "Databases" dropdown: <img width="259" alt="Screen Shot 2022-12-19 at 10 54 36 AM" src="https://user-images.githubusercontent.com/35943354/208500338-6021a805-130d-48f7-846c-c7c6d7782773.png"> Release note(ui change): The databases filter dropdown in the stmts page now uses the `getDatabasesList()` API call, resulting in all cluster databases showing up. 93937: server: refactor database index recommendations for DatabaseDetails API r=THardy98 a=THardy98 Resolves: #93909 Previously, the DatabaseDetails API would fetch the index recommendations of the database by executing an expensive query for **each table of the database**, then coalesce the results of each table to get the database-level result. This was needlessly expensive and impractical, particularly so for large schemas. This change ensures that only a single query is executed **per database** to fetch its index recommendations. ----- **SHORT DEMOS** Short demos of the change in latency before/after running `demo` on db-console. Notably, `movr` is the only database that we check for index recommendations. **Before** https://www.loom.com/share/fc7ca49e4f9c46738831c23742112069 **After** https://www.loom.com/share/be6e4711ca1d43409774995dece0673b Noted Improvements: - The latency on fetching stats for `movr` improves from ~250ms to ~60ms - Not shown in the videos above but the number of query calls improves from 45 to 4. Release note (performance improvement): Refactored the query logic when fetching database index recommendations for the DatabaseDetails API endpoint, greatly reducing the query time and cost, particularly for large schemas. 94156: roachtest: update version map for 22.2.1 r=absterr08 a=absterr08 links epic https://cockroachlabs.atlassian.net/browse/REL-228 Release note: none Co-authored-by: Raphael 'kena' Poss <knz@thaumogen.net> Co-authored-by: gtr <gerardo@cockroachlabs.com> Co-authored-by: Thomas Hardy <thardy@cockroachlabs.com> Co-authored-by: Abby Hersh <abby@cockroachlabs.com>

Resolves: cockroachdb#93909 Previously, the DatabaseDetails API would fetch the index recommendations of the database by executing an expensive query for **each table of the database**, then coalesce the results of each table to get the database-level result. This was needlessly expensive and impractical, particularly so for large schemas. This change ensures that only a single query is executed **per database** to fetch its index recommendations. Release note (performance improvement): Refactored the query logic when fetching database index recommendations for the DatabaseDetails API endpoint, greatly reducing the query time and cost, particularly for large schemas.

THardy98 added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-observability labels Dec 19, 2022

THardy98 self-assigned this Dec 19, 2022

kevin-v-ngo added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Dec 19, 2022

THardy98 mentioned this issue Dec 19, 2022

server: refactor database index recommendations for DatabaseDetails API #93937

Merged

craig bot closed this as completed in 5786a71 Dec 22, 2022

THardy98 mentioned this issue Jan 9, 2023

release-22.2: server: refactor database index recommendations for DatabaseDetails API #94923

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

server: refactor index recommendations for the DatabaseDetails API #93909

server: refactor index recommendations for the DatabaseDetails API #93909

THardy98 commented Dec 19, 2022 •

edited by cockroach-jira-scripts

Loading

server: refactor index recommendations for the DatabaseDetails API #93909

server: refactor index recommendations for the DatabaseDetails API #93909

Comments

THardy98 commented Dec 19, 2022 • edited by cockroach-jira-scripts Loading

THardy98 commented Dec 19, 2022 •

edited by cockroach-jira-scripts

Loading