Get and analyze AlloyDB explain plans

You can identify performance bottlenecks and optimize your AlloyDB for PostgreSQL database operations by getting and analyzing execution plans. An execution plan or EXPLAIN plan is a detailed representation of how your AlloyDB database engine intends to execute a SQL query. The execution plan comprises a tree of nodes that outlines the sequence of operations, such as table scans, joins, sorting, and aggregations, that the AlloyDB database performs to retrieve the requested data. Each step in this plan is referred to as a node.

An execution plan is obtained by using the EXPLAIN command, which returns the plan that the AlloyDB query planner generates for a given SQL statement. A query planner, also known as an optimizer, determines the most efficient way to execute a given SQL query.

Execution plans include the following components:

  • Plan nodes: these represent the different steps in the query execution, such as a scan, a join, or a sort operation.
  • Execution time: the EXPLAIN plan includes the estimated or actual execution time for each step, which helps you identify database bottlenecks.
  • Buffer usage: this shows how much data is read from disk versus the cache, which helps identify disk read issues.
  • Parameter settings: the plan shows the parameter settings that are effective during the query execution.

PostgreSQL, and by extension AlloyDB, supports execution plans for the following statements:

  • SELECT
  • INSERT
  • UPDATE
  • DECLARE CURSOR
  • CREATE AS
  • CREATE MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW
  • EXECUTE

Before you begin

You must have an AlloyDB cluster and instance. For more information, see Create a cluster and its primary instance.

Generate an execution plan

You generate an execution plan from a client application such as psql, pgAdmin or DBeaver. AlloyDB supports generating execution plans in text or JSON format.

To generate an execution plan, follow these steps:

  1. Connect a psql client to an instance.

  2. To generate an execution plan in text format, run the following command:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

  3. To generate an execution plan in JSON format, run the following command:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)

    The EXPLAIN command includes all available options—analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, and summary—to generate a detailed execution plan for a given query in text or JSON format. The analyze option means that the query is executed to provide actual runtime statistics as well as the query planner's estimates.

View and analyze EXPLAIN plan data

After you get an execution plan, you view and analyze the results.

By default, EXPLAIN output shows server-side query activity. To measure end-to-end round trip time, use the /timing option in psql and dump the results to /dev/null.

To see the execution plan that you generated, you use the EXPLAIN command before your SQL query.

  • EXPLAIN SELECT...: shows the plan that the optimizer would choose without running the query.
  • EXPLAIN ANALYZE SELECT...: executes the query and shows both the predicted plan and the actual execution statistics, including true run times and row counts.

EXPLAIN without ANALYZE

To show the query planner's estimated query costs, execute an EXPLAIN statement without the ANALYZE option .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

The plan output includes the following data:

  • cost = 0.00..1735481.00: the first number indicates the cost to retrieve the first row. The second number indicates the cost to retrieve the last row.
  • rows = 100000000: this is the estimated number of rows that the query returns.
  • width = 27: this is the estimated width of the returned row, which helps you understand accessed blocks.

ANALYZE option

To display actual execution statistics as well as execution estimates, add the ANALYZE option 

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)