opt: explain doesn't show hash or merge join explicitly #35683

awoods187 · 2019-03-13T13:48:16Z

Describe the problem
Explain does not show that a hash join has occured:

root@localhost:26257/tpch> explain SELECT count(*) FROM lineitem INNER hash JOIN supplier ON l_suppkey = s_suppkey;
         tree         |       field        |        description
+---------------------+--------------------+---------------------------+
  group               |                    |
   │                  | aggregate 0        | count_rows()
   │                  | scalar             |
   └── render         |                    |
        └── join      |                    |
             │        | type               | inner
             │        | equality           | (l_suppkey) = (s_suppkey)
             │        | right cols are key |
             ├── scan |                    |
             │        | table              | lineitem@l_sk
             │        | spans              | ALL
             └── scan |                    |
                      | table              | supplier@s_nk
                      | spans              | ALL
(14 rows)

Time: 1.130645ms

Explain (opt) does show hash:

root@localhost:26257/tpch> explain (opt) SELECT count(*) FROM lineitem INNER hash JOIN supplier ON l_suppkey = s_suppkey;
                      text
+-----------------------------------------------+
  scalar-group-by
   ├── inner-join
   │    ├── flags: no-lookup-join;no-merge-join
   │    ├── scan lineitem@l_sk
   │    ├── scan supplier@s_nk
   │    └── filters
   │         └── l_suppkey = s_suppkey
   └── aggregations
        └── count-rows
(9 rows)

Time: 974.58µs

Explain (opt, verbose) does as well:

root@localhost:26257/tpch> explain (opt, verbose) SELECT count(*) FROM lineitem INNER hash JOIN supplier ON l_suppkey = s_suppkey;
                                                             text
+-----------------------------------------------------------------------------------------------------------------------------+
  scalar-group-by
   ├── columns: count:24
   ├── cardinality: [1 - 1]
   ├── stats: [rows=1]
   ├── cost: 6686720.04
   ├── key: ()
   ├── fd: ()-->(24)
   ├── prune: (24)
   ├── inner-join
   │    ├── columns: l_suppkey:3 s_suppkey:17
   │    ├── flags: no-lookup-join;no-merge-join
   │    ├── stats: [rows=5980880.01, distinct(3)=10034, null(3)=0, distinct(17)=10034, null(17)=0]
   │    ├── cost: 6626911.22
   │    ├── fd: (3)==(17), (17)==(3)
   │    ├── scan lineitem@l_sk
   │    │    ├── columns: l_suppkey:3
   │    │    ├── stats: [rows=6001215, distinct(3)=10034, null(3)=0]
   │    │    ├── cost: 6481312.21
   │    │    └── prune: (3)
   │    ├── scan supplier@s_nk
   │    │    ├── columns: s_suppkey:17
   │    │    ├── stats: [rows=10000, distinct(17)=10034, null(17)=0]
   │    │    ├── cost: 10600.01
   │    │    ├── key: (17)
   │    │    └── prune: (17)
   │    └── filters
   │         └── l_suppkey = s_suppkey [outer=(3,17), constraints=(/3: (/NULL - ]; /17: (/NULL - ]), fd=(3)==(17), (17)==(3)]
   └── aggregations
        └── count-rows
(29 rows)

Time: 1.787682ms

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

awoods187 · 2019-03-13T13:50:11Z

Lookup has the ideal ux:

root@localhost:26257/tpch> explain SELECT count(*) FROM lineitem INNER lookup JOIN supplier ON l_suppkey = s_suppkey;
          tree          |    field    |   description
+-----------------------+-------------+------------------+
  group                 |             |
   │                    | aggregate 0 | count_rows()
   │                    | scalar      |
   └── render           |             |
        └── lookup-join |             |
             │          | table       | supplier@primary
             │          | type        | inner
             └── scan   |             |
                        | table       | lineitem@l_sk
                        | spans       | ALL
(10 rows)

Time: 1.084726ms

While merge does not. You can figure it out in the next column, but it would be better to see it in the first like lookup.

root@localhost:26257/tpch> explain SELECT count(*) FROM lineitem INNER merge JOIN supplier ON l_suppkey = s_suppkey;
         tree         |     field      |        description
+---------------------+----------------+---------------------------+
  group               |                |
   │                  | aggregate 0    | count_rows()
   │                  | scalar         |
   └── render         |                |
        └── join      |                |
             │        | type           | inner
             │        | equality       | (l_suppkey) = (s_suppkey)
             │        | mergeJoinOrder | +"(l_suppkey=s_suppkey)"
             ├── scan |                |
             │        | table          | lineitem@l_sk
             │        | spans          | ALL
             └── scan |                |
                      | table          | supplier@primary
                      | spans          | ALL
(14 rows)

awoods187 · 2019-03-13T13:52:21Z

Also, for explain (opt, verbose) we should match hash to merge and lookup:

root@localhost:26257/tpch> explain (opt, verbose) SELECT count(*) FROM lineitem INNER lookup JOIN supplier ON l_suppkey = s_suppkey;
                                                text
+--------------------------------------------------------------------------------------------------+
  scalar-group-by
   ├── columns: count:24
   ├── cardinality: [1 - 1]
   ├── stats: [rows=1]
   ├── cost: 43464681.9
   ├── key: ()
   ├── fd: ()-->(24)
   ├── prune: (24)
   ├── inner-join (lookup supplier)
   │    ├── columns: l_suppkey:3 s_suppkey:17
   │    ├── flags: no-merge-join;no-hash-join
   │    ├── key columns: [3] = [17]
   │    ├── stats: [rows=5980880.01, distinct(3)=10034, null(3)=0, distinct(17)=10034, null(17)=0]
   │    ├── cost: 43404873
   │    ├── fd: (3)==(17), (17)==(3)
   │    ├── scan lineitem@l_sk
   │    │    ├── columns: l_suppkey:3
   │    │    ├── stats: [rows=6001215, distinct(3)=10034, null(3)=0]
   │    │    ├── cost: 6481312.21
   │    │    └── prune: (3)
   │    └── filters (true)
   └── aggregations
        └── count-rows
(23 rows)

Time: 1.06538ms

root@localhost:26257/tpch> explain (opt, verbose) SELECT count(*) FROM lineitem INNER merge JOIN supplier ON l_suppkey = s_suppkey;
                                                text
+--------------------------------------------------------------------------------------------------+
  scalar-group-by
   ├── columns: count:24
   ├── cardinality: [1 - 1]
   ├── stats: [rows=1]
   ├── cost: 6672642
   ├── key: ()
   ├── fd: ()-->(24)
   ├── prune: (24)
   ├── inner-join (merge)
   │    ├── columns: l_suppkey:3 s_suppkey:17
   │    ├── flags: no-lookup-join;no-hash-join
   │    ├── left ordering: +3
   │    ├── right ordering: +17
   │    ├── stats: [rows=5980880.01, distinct(3)=10034, null(3)=0, distinct(17)=10034, null(17)=0]
   │    ├── cost: 6612833.18
   │    ├── fd: (3)==(17), (17)==(3)
   │    ├── scan lineitem@l_sk
   │    │    ├── columns: l_suppkey:3
   │    │    ├── stats: [rows=6001215, distinct(3)=10034, null(3)=0]
   │    │    ├── cost: 6481312.21
   │    │    ├── ordering: +3
   │    │    ├── prune: (3)
   │    │    └── interesting orderings: (+3)
   │    ├── scan supplier
   │    │    ├── columns: s_suppkey:17
   │    │    ├── stats: [rows=10000, distinct(17)=10034, null(17)=0]
   │    │    ├── cost: 11600.01
   │    │    ├── key: (17)
   │    │    ├── ordering: +17
   │    │    ├── prune: (17)
   │    │    └── interesting orderings: (+17)
   │    └── filters (true)
   └── aggregations
        └── count-rows
(34 rows)

Time: 1.278399ms

root@localhost:26257/tpch> explain (opt, verbose) SELECT count(*) FROM lineitem INNER hash JOIN supplier ON l_suppkey = s_suppkey;
                                                             text
+-----------------------------------------------------------------------------------------------------------------------------+
  scalar-group-by
   ├── columns: count:24
   ├── cardinality: [1 - 1]
   ├── stats: [rows=1]
   ├── cost: 6686720.04
   ├── key: ()
   ├── fd: ()-->(24)
   ├── prune: (24)
   ├── inner-join
   │    ├── columns: l_suppkey:3 s_suppkey:17
   │    ├── flags: no-lookup-join;no-merge-join
   │    ├── stats: [rows=5980880.01, distinct(3)=10034, null(3)=0, distinct(17)=10034, null(17)=0]
   │    ├── cost: 6626911.22
   │    ├── fd: (3)==(17), (17)==(3)
   │    ├── scan lineitem@l_sk
   │    │    ├── columns: l_suppkey:3
   │    │    ├── stats: [rows=6001215, distinct(3)=10034, null(3)=0]
   │    │    ├── cost: 6481312.21
   │    │    └── prune: (3)
   │    ├── scan supplier@s_nk
   │    │    ├── columns: s_suppkey:17
   │    │    ├── stats: [rows=10000, distinct(17)=10034, null(17)=0]
   │    │    ├── cost: 10600.01
   │    │    ├── key: (17)
   │    │    └── prune: (17)
   │    └── filters
   │         └── l_suppkey = s_suppkey [outer=(3,17), constraints=(/3: (/NULL - ]; /17: (/NULL - ]), fd=(3)==(17), (17)==(3)]
   └── aggregations
        └── count-rows
(29 rows)

Time: 1.362778ms

RaduBerinde · 2019-03-13T14:00:01Z

The EXPLAIN OPT output is currently mostly for our benefit (when debugging) so I'm not crazy about considering "usability" improvements. The output closely mirrors the internal structure of our expressions (which is subject to change): a join without any qualification is a hash join, other joins have an added specifier like (merge) or (lookup ..). I am hesitant to add hash because in some contexts (e.g normalization tests) the join represents the "logical" join and not any particular execution algorithm. This might change if we separate the "logical" join from hash join.

The regular EXPLAIN output has been like this forever, but I agree it can be improved. I don't see a problem with renaming join to hash-join or merge-join in the output.

awoods187 · 2019-03-13T14:09:36Z

I think we definitely should fix explain. I could probably be persuaded on explain (opt) and explain (opt, verbose). If explain is good enough then we wouldn't need that. If we find that users are diving deeper in to explain (opt) and explain (opt, verbose) post-release then we should revisit that portion.

Can we match merge and hash to lookup for explain for 19.1?

RaduBerinde · 2019-03-13T14:27:29Z

Yes. If users seem to need explain (opt) we should figure out what's missing from the regular explain.

I edited the title because this doesn't have much to do with hints as far as I can tell.

Replace `join` with `hash-join` or `merge-join` in `EXPLAIN` output. Fixes cockroachdb#35683. Release note (sql change): EXPLAIN tree now uses `hash-join` or `merge-join` instead of `join`.

35688: sql: specify hash or merge join in EXPLAIN r=RaduBerinde a=RaduBerinde Replace `join` with `hash-join` or `merge-join` in `EXPLAIN` output. Fixes #35683. Release note (sql change): EXPLAIN tree now uses `hash-join` or `merge-join` instead of `join`. 35719: roachpb: prevent data race on Transaction r=nvanbenschoten a=nvanbenschoten Fixes #34241. This PR starts off with a series of small cleanups related to ownership of `roachpb.Transaction` objects and the use of deep and shallow clones. This makes up the first 5 commits. Next, the 6th commit removes redundant calls to `Transaction.UpdateObservedTimestamp`, reducing it down to have just a single caller that is easy to reason about. The 7th commit is what actually fixes the referenced issue. It adds in the proto clone that was missing from `BatchResponse_Header.combine` and allowing a `BatchRequest` to reference the same `Transaction` object as a `BatchResponse`. I confirmed a number of times that this stops the assertion from firing, so the commit also re-enables the skipped roachtest and removes the assertion. ~The final two commits are the two that we might consider waiting on and not including in this release, but they're also the most exciting. By making `Transaction.ObservedTimestamps` immutable (which it almost already was), we can prohibit all interior mutability of references within `Transaction`, give it value semantics, and eliminate the distinction between "shallow" and "deep" object cloning. This reduces the cost of a clone to a single straightforward allocation and makes working with the object easier to think about.~ EDIT: these two were removed from this PR. Past this point, I think the only other change we might want to make here is making a clone of `ba.Txn` in `internalClientAdapter` and then declare that the `Batch` handler goroutine has exclusive ownership over its `ba.Txn`. This would allow us to avoid a few conservative clones that would no longer be necessary, like [here](https://github.com/cockroachdb/cockroach/blob/57e825a7940495b67e0cc7213a5fabc24e12be0e/pkg/storage/store.go#L2827) and [here](https://github.com/cockroachdb/cockroach/blob/57e825a7940495b67e0cc7213a5fabc24e12be0e/pkg/storage/replica.go#L1309). I did not make this change here. 35736: opt: catch all pgerror.Error in optbuilder r=RaduBerinde a=RaduBerinde We now catch all `pgerror.Error`s in optbuilder, which means that we don't need to use the `buildError` wrapper with them. The wrapper still exists when external calls (e.g. for name resolution) return a generic error. The main motivation is that optbuilder calls into the factory which can panic internally. We will want to switch those panics to assertion errors as well, but they are in different packages. The existing approach would have required a shared, exported wrapper. Release note: None Co-authored-by: Radu Berinde <[email protected]> Co-authored-by: Nathan VanBenschoten <[email protected]>

awoods187 assigned RaduBerinde Mar 13, 2019

awoods187 added the A-sql-optimizer SQL logical planning and optimizations. label Mar 13, 2019

awoods187 added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Mar 13, 2019

awoods187 added this to the 19.1 milestone Mar 13, 2019

awoods187 changed the title ~~Explain doesn't show hash when using hints specifying hash~~ opt: explain doesn't show hash when using hints specifying hash Mar 13, 2019

RaduBerinde changed the title ~~opt: explain doesn't show hash when using hints specifying hash~~ opt: explain doesn't show hash or merge join explicitly Mar 13, 2019

RaduBerinde mentioned this issue Mar 13, 2019

sql: specify hash or merge join in EXPLAIN #35688

Merged

craig bot closed this as completed in #35688 Mar 14, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

opt: explain doesn't show hash or merge join explicitly #35683

opt: explain doesn't show hash or merge join explicitly #35683

awoods187 commented Mar 13, 2019

awoods187 commented Mar 13, 2019

awoods187 commented Mar 13, 2019

RaduBerinde commented Mar 13, 2019

awoods187 commented Mar 13, 2019 •

edited

Loading

RaduBerinde commented Mar 13, 2019

opt: explain doesn't show hash or merge join explicitly #35683

opt: explain doesn't show hash or merge join explicitly #35683

Comments

awoods187 commented Mar 13, 2019

awoods187 commented Mar 13, 2019

awoods187 commented Mar 13, 2019

RaduBerinde commented Mar 13, 2019

awoods187 commented Mar 13, 2019 • edited Loading

RaduBerinde commented Mar 13, 2019

awoods187 commented Mar 13, 2019 •

edited

Loading