How to Get Execution Plan from a Query in ksqlDB

To get the execution plan of a query, use the EXPLAIN command:

EXPLAIN
  SELECT
    subreddit,
    MAX(activeUserCount)
  FROM reddit WINDOW TUMBLING (SIZE 1 HOUR)
  GROUP BY subreddit
  EMIT CHANGES;

You can also pass the ID of a persistent query if you have one:

EXPLAIN query_id;

EXPLAIN is very useful to understand how ksqlDB parses your query. It returns information such as what type of query is it PUSH or PULL, the type of selected fields and expressions, as well as what streams or tables act as sources.

ID                   : transient_REDDIT_4484078336090288353
Query Type           : PUSH
SQL                  :   SELECT
    subreddit,
    MAX(activeUserCount)
  FROM reddit WINDOW TUMBLING (SIZE 1 HOUR)
  GROUP BY subreddit
  EMIT CHANGES;
 Field      | Type
-------------------------------------
 SUBREDDIT  | VARCHAR(STRING)  (key)
 SUBREDDIT  | VARCHAR(STRING)
 KSQL_COL_0 | INTEGER
-------------------------------------
Sources that this query reads from:
-----------------------------------
REDDIT

Besides the execution plan, you also get the Kafka Streams topology that ksqlDB runs for the query.

Execution plan
--------------
 > [ PROJECT ] | Schema: SUBREDDIT STRING KEY, SUBREDDIT STRING, KSQL_COL_0 INTEGER | Logger: transient_REDDIT_4484078336090288353.Aggregate.Project
         > [ AGGREGATE ] | Schema: SUBREDDIT STRING KEY, SUBREDDIT STRING, ACTIVEUSERCOUNT INTEGER, KSQL_AGG_VARIABLE_0 INTEGER, WINDOWSTART BIGINT, WINDOWEND BIGINT | Logger: transient_REDDIT_4484078336090288353.Aggregate.Aggregate
                 > [ GROUP_BY ] | Schema: SUBREDDIT STRING KEY, SUBREDDIT STRING, ACTIVEUSERCOUNT INTEGER | Logger: transient_REDDIT_4484078336090288353.Aggregate.GroupBy
                         > [ PROJECT ] | Schema: SUBREDDIT STRING, ACTIVEUSERCOUNT INTEGER | Logger: transient_REDDIT_4484078336090288353.Aggregate.Prepare
                                 > [ SOURCE ] | Schema: SUBREDDIT STRING, ACTIVEUSERCOUNT INTEGER, ROWTIME BIGINT, ROWPARTITION INTEGER, ROWOFFSET BIGINT | Logger: transient_REDDIT_4484078336090288353.KsqlTopic.Source
Processing topology
-------------------
Topologies:
   Sub-topology: 0
    Source: KSTREAM-SOURCE-0000000000 (topics: [reddit.statistics.json])
      --> KSTREAM-TRANSFORMVALUES-0000000001
    Processor: KSTREAM-TRANSFORMVALUES-0000000001 (stores: [])
      --> Aggregate-Prepare
      <-- KSTREAM-SOURCE-0000000000
    Processor: Aggregate-Prepare (stores: [])
      --> KSTREAM-FILTER-0000000003
      <-- KSTREAM-TRANSFORMVALUES-0000000001
    Processor: KSTREAM-FILTER-0000000003 (stores: [])
      --> Aggregate-GroupBy
      <-- Aggregate-Prepare
    Processor: Aggregate-GroupBy (stores: [])
      --> Aggregate-GroupBy-repartition-filter
      <-- KSTREAM-FILTER-0000000003
    Processor: Aggregate-GroupBy-repartition-filter (stores: [])
      --> Aggregate-GroupBy-repartition-sink
      <-- Aggregate-GroupBy
    Sink: Aggregate-GroupBy-repartition-sink (topic: Aggregate-GroupBy-repartition)
      <-- Aggregate-GroupBy-repartition-filter
  Sub-topology: 1
    Source: Aggregate-GroupBy-repartition-source (topics: [Aggregate-GroupBy-repartition])
      --> KSTREAM-AGGREGATE-0000000005
    Processor: KSTREAM-AGGREGATE-0000000005 (stores: [Aggregate-Aggregate-Materialize])
      --> Aggregate-Aggregate-ToOutputSchema
      <-- Aggregate-GroupBy-repartition-source
    Processor: Aggregate-Aggregate-ToOutputSchema (stores: [])
      --> Aggregate-Aggregate-WindowSelect
      <-- KSTREAM-AGGREGATE-0000000005
    Processor: Aggregate-Aggregate-WindowSelect (stores: [])
      --> Aggregate-Project
      <-- Aggregate-Aggregate-ToOutputSchema
    Processor: Aggregate-Project (stores: [])
      --> KTABLE-TOSTREAM-0000000012
      <-- Aggregate-Aggregate-WindowSelect
    Processor: KTABLE-TOSTREAM-0000000012 (stores: [])
      --> KSTREAM-FOREACH-0000000013
      <-- Aggregate-Project
    Processor: KSTREAM-FOREACH-0000000013 (stores: [])
      --> none
      <-- KTABLE-TOSTREAM-0000000012

Was this article helpful?