opt: add node and region information to explain #59860
Labels
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
no-issue-activity
X-stale
Comments
awoods187
added
the
C-enhancement
craig bot
pushed a commit
that referenced
this issue
Feb 19, 2021
60550: sql: add nodes for each EXPLAIN ANALYZE operator r=RaduBerinde a=RaduBerinde
Show the cluster nodes involved in the execution of each operator.
Note that this info does not show up in the non-analyze EXPLAIN. It is
technically much more challenging to do that because of the indirect
way we do distsql planning. Once we have the new DistSQL exec factory,
we will be able to add it.
Note on the implementation: I started by trying to make execution set
`ComponentID.NodeID` in all cases, but I got stuck in `ProcessorBase`
where we only have a `SQLIDContainer` available. I don't fully
understand the new abstraction and whether the distsql components and
flows should really use SQLIDs instead of NodeIDs.
Unfortunately, there is not much we can do to test this currently
(other than manual testing). I will investigate making the
"deterministic" flag more fine-grained, so that we can hide truly
non-deterministic values (like timings) separately from those that
just vary with the configuration (query distribution).
Example:
```
movr> EXPLAIN ANALYZE SELECT * FROM rides JOIN vehicles ON rides.vehicle_id = vehicles.id;
info
--------------------------------------------
planning time: 158µs
execution time: 7ms
distribution: full
vectorized: true
hash join
│ cluster nodes: n1, n2, n3
│ actual row count: 500
│ equality: (vehicle_id) = (id)
│
├── scan
│ cluster nodes: n1, n2, n3
│ actual row count: 500
│ KV rows read: 500
│ KV bytes read: 86 KiB
│ missing stats
│ table: rides@primary
│ spans: FULL SCAN
│
└── scan
cluster nodes: n1, n2, n3
actual row count: 15
KV rows read: 15
KV bytes read: 2.3 KiB
missing stats
table: vehicles@primary
spans: FULL SCAN
```
Release note (sql change): EXPLAIN ANALYZE now includes the nodes
which were involved in the execution of each operator in the tree.
Informs #59860.
60748: sql: remove experimental setting for virtual columns r=RaduBerinde a=RaduBerinde
Informs #57608.
Release note (sql change): CockroachDB now supports VIRTUAL computed
columns (as opposed to STORED). These are computed columns that are
not stored in the primary index and are recomputed as necessary.
Co-authored-by: Radu Berinde <radu@cockroachlabs.com>
|
@RaduBerinde could you update here why we only added this to explain analyze? |
|
#60550 added per-operator node information for EXPLAIN ANALYZE. Adding this info to the regular EXPLAIN is currently difficult, because we don't generate the distsql plan directly. This will be easier once we do (with the distsql factory). |
|
The work that @RaduBerinde is mentioning is tracked here: #47473 |
|
We have marked this issue as stale because it has been inactive for |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Distributed SQL allows for queries to be distributed across multiple nodes, and, potentially multiple regions. Furthermore, as we enhance our support for multi-region, some data may not be located in every region. Developers will need to be able to write queries that go cross-node and even cross-region. We want to minimize cross-region queries wherever possible because of the expensive cross-region network hop that can dramatically increase latency. By adding regions involved in the query to EXPLAIN, developers can identify costly cross-region queries and take corrective action.
Cross-node queries are somewhat more complex as it is not always advantageous for a query to use multiple nodes. Sometimes using multiple nodes enhances query performance by sharing resources, and sometimes it increases latency due to the distance between nodes.
We need to include this information on a per-operator basis because otherwise developers would not be able to identify the problematic part of the query that travels cross-node or cross-region usage.
Epic: CRDB-9025
Jira issue: CRDB-3220
The text was updated successfully, but these errors were encountered: