sql: augment EXPLAIN to explicitly distinguish between hash and merge joins

[rjnn]

Right now, when you create indexes to do a merge join, EXPLAIN tells you (in a roundabout fashion):

+-------+--------+----------------+-----------------------------------------+
| Level |  Type  |     Field      |               Description               |
+-------+--------+----------------+-----------------------------------------+
|     0 | render |                |                                         |
|     1 | join   |                |                                         |
|     1 |        | type           | inner                                   |
|     1 |        | equality       | (id) = (dst)                            |
|     1 |        | mergeJoinOrder | +"(id=dst)"                             |
|     2 | scan   |                |                                         |
|     2 |        | table          | nodes@primary                           |
|     2 |        | spans          | ALL                                     |
|     2 | scan   |                |                                         |
|     2 |        | table          | edges@edges_auto_index_fk_dst_ref_nodes |
|     2 |        | spans          | ALL                                     |
+-------+--------+----------------+-----------------------------------------+

But if you accidentally mess up (say you create an index slightly inaccurately, which in my experience I've found is a very easy mistake to make), then cockroach happily does a hash join, telling you nothing, e.g.:

root@:26257/graphs> explain select nodes.id,dst from nodes join edges on edges.dst=nodes.id;
+-------+--------+----------+---------------+
| Level |  Type  |  Field   |  Description  |
+-------+--------+----------+---------------+
|     0 | render |          |               |
|     1 | join   |          |               |
|     1 |        | type     | inner         |
|     1 |        | equality | (id) = (dst)  |
|     2 | scan   |          |               |
|     2 |        | table    | nodes@primary |
|     2 |        | spans    | ALL           |
|     2 | scan   |          |               |
|     2 |        | table    | edges@primary |
|     2 |        | spans    | ALL           |
+-------+--------+----------+---------------+

We should augment EXPLAIN to explicitly indicate the JOIN strategy. This will make giving performance guidance (cc @Amruta-Ranade who is currently working on that) easier.

[rjnn]

cc @jordanlewis, @knz I would like your opinion on this before proceeding with attempting a fix.

[jordanlewis]

Seems like it would be reasonable to add a strategy field alongside type and equality.

[rjnn]

Thank you, I just wanted your seal of approval on that.

[knz]

👍

[RaduBerinde]

This was fixed in #35688. We now show hash-join or merge-join or lookup-join.