Gonzalo Ortiz created CALCITE-6394:
--------------------------------------
Summary: Be able to use input names instead of $i in explain
Key: CALCITE-6394
URL: https://issues.apache.org/jira/browse/CALCITE-6394
Project: Calcite
Issue Type: Improvement
Reporter: Gonzalo Ortiz
As Apache Pinot committer, one of the request I've found from our users is to
make explains easier to read. The main complain here is that it requires some
training to be able to read them, specially because in explain names of the
inputs is index based ($0, $1, etc) but the rows itself are indirectly defined
by the each input. A person with enough experience may understand that the rows
generated by a join is formed by appending the input of the left hand side with
the input of the right hand side, but in complex queries it takes time to get
use to that and to picture these rows in your mind.
These complains are also based by the fact that other databases have solved
this issue long ago. For example, in Postgres, given a table `a` and `two_cols`
such as:
{{postgres=# \d a}}
{{ Table "public.a"}}
{{Column | Type | Collation | Nullable | Default }}
{{--------+---------+-----------+----------+---------}}
{{a | integer | | | }}
{{postgres=# \d two_cols}}
{{ Table "public.two_cols"}}
{{Column | Type | Collation | Nullable | Default }}
{{--------+---------+-----------+----------+---------}}
{{a | integer | | | }}
{{b | integer | | |}}
A join explain shows:
{{postgres=# explain select * from a join two_cols on a.a = two_cols.a;}}
{{ QUERY PLAN }}
{{------------------------------------------------------------------------}}
{{Merge Join (cost=338.29..781.81 rows=28815 width=12)}}
{{ Merge Cond: ({*}two_cols.a = a.a{*})}}
{{ -> Sort (cost=158.51..164.16 rows=2260 width=8)}}
{{ Sort Key: *two_cols.a*}}
{{ -> Seq Scan on two_cols (cost=0.00..32.60 rows=2260 width=8)}}
{{ -> Sort (cost=179.78..186.16 rows=2550 width=4)}}
{{ Sort Key: *a.a*}}
{{ -> Seq Scan on a (cost=0.00..35.50 rows=2550 width=4)}}
{{(8 rows)}}
In Postgres the explain even conserves the name of the aliases if used.
To be clear, I don't know if it is possible to do this right now. I didn't find
a way myself and after asking in the dev mail least I was invited to write a
Jira issue to discuss about the specific requirements.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
