This page categorizes all of the relational 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 Operator | 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 | Distinct | ||
| OrderedDistinct | ||||
| ORDER BY | sort | sortAllProcessor | ||
| sortTopKProcessor | ||||
| sortChunksProcessor | ||||
| GROUP BY | aggregate | orderedAggregator | ||
| hashAggregator | ||||
| LIMIT | limit | limit | ||
| OFFSET | offset | offset | ||
| OVER / PARTITION BY | window | windower | ||