Initial explain by KochetovNicolai · Pull Request #11873 · ClickHouse/ClickHouse

KochetovNicolai · 2020-06-22T18:58:51Z

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):
Initial implementation of EXPLAIN query. Syntax: EXPLAIN SELECT .... This fixes #1118.

Usage:

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

`EXPLAIN AST`

Dump query AST.

EXPLAIN AST SELECT 1

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Literal UInt64_1

`EXPLAIN SYNTAX`

Return query after syntax optimisations.

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c

SELECT 
    `--a.number` AS `a.number`,
    `--b.number` AS `b.number`,
    number AS `c.number`
FROM 
(
    SELECT 
        number AS `--a.number`,
        b.number AS `--b.number`
    FROM system.numbers AS a
    CROSS JOIN system.numbers AS b
) AS `--.s`
CROSS JOIN system.numbers AS c

`EXPLAIN PLAN`

Dump query plan steps.

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4

Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      Aggregating
        Expression (Before GROUP BY)
          ReadFromStorage (Read from SystemNumbers)

Settings:

header - print output header for step. Default: 0.
description - print step description. Default: 1.
actions - print detailed information about step actions. Default: 0.

`EXPLAIN PIPELINE`

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4

(Union)
Converting
  (Expression)
  ExpressionTransform
    (Expression)
    ExpressionTransform
      (Aggregating)
      Resize 32 → 1
        AggregatingTransform × 32
          (Expression)
          ExpressionTransform × 32
            (ReadFromStorage)
            NumbersMt × 32 0 → 1

Settings:

header - print header for each output port. Default: 0.
graph - use DOT graph description language. Default: 0.
compact - print graph in compact mode if graph is enabled. Default: 1.

KochetovNicolai · 2020-06-22T18:59:52Z

:) explain select sum(number) from numbers(10) union all select sum(number) from numbers(10) order by sum(number)

EXPLAIN SELECT sum(number)
FROM numbers(10)
UNION ALL
SELECT sum(number)
FROM numbers(10)
ORDER BY sum(number) ASC
FORMAT TSV

Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      Aggregating
        Expression (Before GROUP BY)
          ReadFromStorage (Read from SystemNumbers)
  Expression (Projection)
    MergingSorted (Merge sorted streams before ORDER BY)
      MergeSorting (Merge sorted blocks before ORDER BY)
        PartialSorting (Sort each block before ORDER BY)
          Expression (Before ORDER BY and SELECT)
            Aggregating
              Expression (Before GROUP BY)
                ReadFromStorage (Read from SystemNumbers)

alexey-milovidov · 2020-06-22T20:19:50Z

MergingSorted (Merge sorted streams before ORDER BY)
MergeSorting (Merge sorted blocks before ORDER BY)
PartialSorting (Sort each block before ORDER BY)

Actually not before ORDER BY but for ORDER BY.

filimonov · 2020-06-23T05:09:24Z

BTW how about command to show processors pipeline graph in a graphviz format? I saw it in pipeline stuck exception, can it also be shown on demand?

KochetovNicolai · 2020-06-23T06:28:19Z

BTW how about command to show processors pipeline graph in a graphviz format? I saw it in pipeline stuck exception, can it also be shown on demand?

Yes, I will add it too.

alexey-milovidov · 2020-06-29T15:43:42Z

Integration tests

Broken in #11903

azat · 2020-07-02T08:32:50Z

src/Parsers/ParserExplainQuery.cpp

+        else if (s_syntax.ignore(pos, expected))
+            kind = ASTExplainQuery::ExplainKind::AnalyzedSyntax;
+        else if (s_pipeline.ignore(pos, expected))
+            kind = ASTExplainQuery::ExplainKind::QueryPipeline;


Usage:
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

Looks like PLAN is not supported during parsing, or it wasn't not intended to be supported?

I've forgotten about it indeed.

azat · 2020-07-02T08:38:57Z

@KochetovNicolai thoughts on showing max_threads/max_distributed_connections/.. somehow?

i.e.:

SET max_distributed_connections = 10;
explain select * from remote('127.{1,2,3}', system.one);

Union
  Expression (Projection)
    ReadFromStorage x 10 (Read from Distributed)

There were some issues with these settings, when calculation was incorrect will help with debugging and will be useful in tests too

KochetovNicolai · 2020-07-02T08:45:44Z

thoughts on showing max_threads/max_distributed_connections/.. somehow?

Nice idea!
I don't know where to show this information though.
Maybe print it before plan, under another one option. @azat what do you think?

azat · 2020-07-02T08:58:33Z

Maybe print it before plan, under another one optio

I was thinking about showing it in the form of ReadFromStorage x 10 (Read from Distributed) (x10 part), but showing it before, under option looks good too!

alexey-milovidov · 2020-07-05T16:54:19Z

src/Processors/QueryPlan/IQueryPlanStep.h

+    {
+        WriteBuffer & out;
+        size_t offset = 0;
+        const size_t ident = 2;


indent, not ident

alexey-milovidov · 2020-07-05T17:14:23Z

src/Parsers/ASTExplainQuery.h

+    ASTPtr ast_settings;

-    static String toString(ExplainKind kind)
+    static String toString(ExplainKind kind, bool old_syntax)


We can safely remove the old AST query. It's not for users and no backward compatibility is needed.

I agree about 'we don' t care about backward compatibility fot that'. At the same time Ast and analyze are useful in some (rare) cases (for example to look on rewritten query with joins and lot a of aliases) it if possible it better to keep them.

AFAICS It is used only for printing formatted query

ANALYZE = EXPLAIN SYNTAX

AST = EXPLAIN AST

ANALYZE = EXPLAIN SYNTAX
AST = EXPLAIN AST

It's correct.

I mainly kept old syntax because it was used in tests. I probably may rewrite all that tests and remove compatibility. I would rather do it in a separate pr.

alexey-milovidov · 2020-07-07T22:58:45Z

01193_metadata_loading

Flaky test.

azat · 2020-07-21T15:37:47Z

src/Processors/QueryPlan/FilterStep.cpp

+    bool first = true;
+    for (const auto & action : expression->getActions())
+    {
+        settings.out << prefix << (first ? "Actions: "


@KochetovNicolai EXPLAIN actions=1 ... you can see row-level policies, while regular user cannot query them w/o access_management:

EXPLAIN actions = 1 SELECT * FROM prewhere_filter ┌─explain───────────────────────────────────────────────────┐ │ Union │ │ Expression (Projection) │ │ Actions: PROJECT a, b │ │ Expression (Before ORDER BY and SELECT) │ │ Actions: PROJECT a, b │ │ Filter (Row-level security filter) │ │ Filter column: equals(b, 1) │ │ Actions: PROJECT a, b │ │ ADD 1 UInt8 Const(UInt8) │ │ FUNCTION equals(b, 1) UInt8 = equals(b, 1) │ <-- this │ REMOVE 1 │ │ ReadFromStorage (Read from MergeTree) │ └───────────────────────────────────────────────────────────┘

It can be considered normal.
The user can see how data was filtered and the fact that data is being filtered but they cannot see the data that was filtered out.

Ok, but then what is the purpose of restricting access to the system.row_policies/show row policies ?

It prevents to show policies for all other users?

You are right, ok then

nudles · 2023-08-03T09:34:59Z

src/Processors/printPipeline.cpp

+        size_t count;
+    };
+
+    using Edge = std::vector<EdgeData>;


@KochetovNicolai , does it mean that there could be multiple edges (lines) connecting two nodes in the graph? Do you have such an example query? Thank you!

Parser for Explain

8b2232d

blinkov added doc-alert pr-feature Pull request with new product feature labels Jun 22, 2020

KochetovNicolai added 12 commits June 27, 2020 12:30

Add description setting.

b282a17

Fix explain formatting.

4ed499c

Add initial explain.

e1481cc

Header for explain.

47d9f4a

Update explain settings.

8408c03

Added actions setting for Explain query.

d88e19b

More actions description.

ca01094

Add explain pipeline.

5a678d7

Header for pipeline.

79bcc83

Graph for pipeline.

f4e944c

Compact pipeline.

2c02bfa

More comments.

f1f98d7

KochetovNicolai force-pushed the initial-explain branch from 5f124e4 to f1f98d7 Compare June 27, 2020 09:34

KochetovNicolai added 2 commits June 27, 2020 12:45

Merge branch 'master' into initial-explain

893a75b

Refactor some code.

5b1596c

KochetovNicolai force-pushed the initial-explain branch from 255d8f8 to 5b1596c Compare June 27, 2020 14:02

KochetovNicolai marked this pull request as ready for review June 27, 2020 14:03

KochetovNicolai added 3 commits June 27, 2020 17:18

Refactor some code.

ff47f83

Fix build.

90fb571

Fix tests.

897ed2f

KochetovNicolai added 2 commits June 29, 2020 23:23

Update arrows.

b3a7a27

Merge branch 'master' into initial-explain

8587c20

azat reviewed Jul 2, 2020

View reviewed changes

Allow to parse EXPLAIN PLAN.

924f1df

alexey-milovidov added 2 commits July 5, 2020 19:26

Update SortDescription.h

a3f0ba1

Update AggregateDescription.cpp

6a013b9

alexey-milovidov self-assigned this Jul 5, 2020

alexey-milovidov requested changes Jul 5, 2020

View reviewed changes

alexey-milovidov reviewed Jul 5, 2020

View reviewed changes

KochetovNicolai added 2 commits July 7, 2020 22:51

Rename ident to indent.

6ed2908

Merge branch 'master' into initial-explain

fd7fcb2

KochetovNicolai requested a review from alexey-milovidov July 7, 2020 19:53

alexey-milovidov added 2 commits July 8, 2020 01:58

Update test

454b0a2

Merge branch 'master' into initial-explain

3a1ed56

alexey-milovidov merged commit b4c2c49 into master Jul 7, 2020

alexey-milovidov deleted the initial-explain branch July 7, 2020 22:59

alexey-milovidov approved these changes Jul 7, 2020

View reviewed changes

azat reviewed Jul 21, 2020

View reviewed changes

adevyatova mentioned this pull request Dec 2, 2020

DOCSUP-4193: Document the EXPLAIN statement #17734

Merged

nudles reviewed Aug 3, 2023

View reviewed changes

clubanderson mentioned this pull request Mar 13, 2026

🌱 clickhouse: feature request: EXPLAIN output kubestellar/console-kb#994

Merged

Conversation

KochetovNicolai commented Jun 22, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

EXPLAIN AST

EXPLAIN SYNTAX

EXPLAIN PLAN

EXPLAIN PIPELINE

Uh oh!

KochetovNicolai commented Jun 22, 2020

Uh oh!

alexey-milovidov commented Jun 22, 2020

Uh oh!

filimonov commented Jun 23, 2020

Uh oh!

KochetovNicolai commented Jun 23, 2020

Uh oh!

alexey-milovidov commented Jun 29, 2020

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

azat commented Jul 2, 2020

Uh oh!

KochetovNicolai commented Jul 2, 2020 • edited by alexey-milovidov Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

azat commented Jul 2, 2020

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

alexey-milovidov commented Jul 7, 2020

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

KochetovNicolai commented Jun 22, 2020 •

edited

Loading

`EXPLAIN AST`

`EXPLAIN SYNTAX`

`EXPLAIN PLAN`

`EXPLAIN PIPELINE`

KochetovNicolai commented Jul 2, 2020 •

edited by alexey-milovidov

Loading