This page outlines the relationship between SQL syntax clauses, relational and operational ("physical") operators in CockroachDB's SQL implementation.
It might be helpful to look at this to understand what all of the words mean in an EXPLAIN plan - and help you decode what's an efficient plan and what isn't.
| SQL Syntax clause | Logical Operator (planNode, how it appears in EXPLAIN) | Physical Operator (DistSQL Processor, how it appears in EXPLAIN(DISTSQL)) | Notes | |
|---|---|---|---|---|
| SELECT | scan | TableReader | ||
| revscan | TableReader | |||
| index-join | IndexJoiner | Necessary when an index being selected from doesn't contain all of the columns that the SELECT needs. Jumps back to the primary index to retrieve the other necessary data. | ||
| JOIN (inner, left/right/full outer, natural) | join | HashJoiner | Less efficient than merge join - has to buffer an entire side in memory. | |
| MergeJoiner | Efficient - can "stream rows" from each side. | |||
| JoinReader (lookup join) | ||||
| ZigZagJoiner | ||||
| subquery aka (SELECT... ) | subquery | |||
| DISTINCT, DISTINCT ON | distinct | Distinct | ||
| OrderedDistinct | ||||
| ORDER BY | sort | sortAllProcessor | ||
| sortTopKProcessor | ||||
| sortChunksProcessor | ||||
| GROUP BY | aggregate | orderedAggregator | ||
| hashAggregator | ||||
| LIMIT | limit | limit | ||
| OFFSET | offset | offset | ||
| OVER / PARTITION BY | window | windower | ||