Environment

• YugabyteDB (YSQL)

Issue

Understanding and analysing EXPLAIN plans to debug query performance issues in YugabyteDB.

Resolution

Overview

YugabyteDB supports the EXPLAIN and EXPLAIN ANALYZE statements to view the planned and actual query execution strategy.

• EXPLAIN: This statement provides the execution plan for a query without executing it. It shows how the database will execute the query, including the order of operations and the methods used to access data.
• EXPLAIN ANALYZE: This statement executes the query and returns the actual execution plan, including runtime statistics.

WARNING If you are using the EXPLAIN ANALYZE statement, please note that it will execute the query and return the actual execution plan. If you are running DML queries, such as INSERT, UPDATE, or DELETE, this will modify the data in the database. You should run EXPLAIN ANALYZE within a transaction and roll it back to avoid any unintended changes to the data. For example: BEGIN; -- Your DML query here ROLLBACK;

Additional Options

• DIST - Display additional runtime statistics related to the distributed storage layer as seen at the query layer.
• DEBUG - Display low-level runtime metrics related to Cache and storage subsystems (default: FALSE).

You can read more about the EXPLAIN and EXPLAIN ANALYZE statements in the YugabyteDB documentation.

Collecting EXPLAIN Plans

Connect to the database: Use the ysqlsh command-line tool to connect to your YugabyteDB instance.

ysqlsh #Add your connection parameters here

Redirect output to a file: Use the \o command in ysqlsh to redirect the output to a file. For example:

\o /path/to/output_file.txt

Run the EXPLAIN command: Execute the EXPLAIN or EXPLAIN ANALYZE command on your query. For example:

EXPLAIN (ANALYZE, DIST, DEBUG) <your_query>;

Stop redirecting output: Use the \o command again without any arguments to stop redirecting output to the file.

\o

View the output file: Open the output file to view the EXPLAIN plan. You can use any text editor or command-line tool to view the file.

cat /path/to/output_file.txt

Uploading EXPLAIN Plans to Support

** Upload the output file: On support.yugabyte.com, open a new or existing ticket and upload the output file containing the EXPLAIN plan. Make sure to provide the queries you ran and any relevant context about the performance issue you are experiencing.