Cloud

EXPLAIN

The EXPLAIN statement shows diagnostic information about the Redpanda SQL query planner. Use it to see the query planner options for your session. You can also use it to see how long each planning stage takes for a query.

The EXPLAIN statement is a diagnostic tool. Use its output to troubleshoot slow queries. The output can change between releases.

Redpanda SQL EXPLAIN is different from PostgreSQL EXPLAIN. The forms on this page do not show a query plan. They do not support PostgreSQL options such as ANALYZE.

Syntax

EXPLAIN;

EXPLAIN CONFIG;

EXPLAIN TIMING select_statement;

  • CONFIG: Lists the query planner options for the current session, with their values and descriptions.

  • TIMING: Plans the query and lists how long each planning stage takes. Redpanda SQL does not run the query.

  • select_statement: The SELECT query that TIMING plans.

With no option, EXPLAIN lists its forms and what each form does. The statement also returns this list if it does not recognize the option. It also returns the list if you add a query to CONFIG, or leave the query out of TIMING. For example, EXPLAIN SELECT …​ returns the list, not information about the query.

explain() function

You can also run EXPLAIN TIMING and EXPLAIN CONFIG with the explain() table function. The function returns the same table as the statement. Use its output like any other table to filter, sort, or limit the output with SQL.

SELECT * FROM explain('select_statement', 'timing');

SELECT * FROM explain('config');

Write the query as a string literal. If the query contains a single quote, write it twice, such as 'SELECT * FROM orders WHERE customer = ''alice'''.

Output

Each form of EXPLAIN returns a table. The columns differ by form.

EXPLAIN

Column Description

query

The form of EXPLAIN.

description

What the form does, with an example.

EXPLAIN CONFIG

Column Description

option

The name of the query planner option, such as oxla.query_planner.optimization.constant_folding.

value

The value of the option in the current session.

description

What the option does.

EXPLAIN TIMING

Column Description

measurement

The planning stage.

attribution

Where the time goes: work in the query planner, calls to an external service, or waiting.

invocations

The number of times the stage ran.

elapsed

The time that the stage takes, in seconds.

Permissions

To run EXPLAIN TIMING on a query, you need the privileges to run that query.

Examples

List the forms of EXPLAIN:

EXPLAIN;

List the query planner options for the current session:

EXPLAIN CONFIG;

Show how long each planning stage takes for a query on a topic-backed table:

EXPLAIN TIMING SELECT count(*) FROM default_redpanda_catalog=>orders;

Show the three planning stages that take the longest for the same query:

SELECT measurement, elapsed
FROM explain('SELECT count(*) FROM default_redpanda_catalog=>orders', 'timing')
ORDER BY elapsed DESC
LIMIT 3;

Suggested reading