Analyzing Queries with EXPLAIN

This section describes how to optimize queries using YSQL's EXPLAIN and EXPLAIN ANALYZE statements.

The EXPLAIN Statement

Using the EXPLAIN statement, you can obtain the query execution plan generated by YSQL for a given SQL statement. In addition to the plan, EXPLAIN returns the following information:

• The so-called startup cost that represents the estimated query cost before the first row is returned.
• The total cost to run the query to completion.

You can use the EXPLAIN statement in conjunction with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. The EXPLAIN statement has the following syntax:

The option and its values are described in the following table. The most important option is ANALYZE.

Option	Value	Description
ANALYZE	boolean	Returns additional run-time statistics, such as time spent within each plan node, number of processed rows, and so on, by executing sql_statement (as opposed to just creating the plan, as EXPLAIN without ANALYZE does). The output of sql_statement is discarded. To perform analysis of any data-modifying statement (such as INSERT, UPDATE, and DELETE) without affecting the data, you must wrap EXPLAIN ANALYZE in a transaction using the following syntax: BEGIN; EXPLAIN ANALYZE sql_statement; ROLLBACK;
VERBOSE	boolean	Displays detailed information about the query plan. The default value is FALSE.
COSTS	boolean	Provides the estimated initial and total costs of each plan node. In addition, estimates the number of rows and the width of each row in the query plan. The default value is TRUE.
BUFFERS	boolean	Provides information about the most input-output intensive parts of the query. The default value is FALSE. You can only use this option when ANALYZE is set to TRUE.
TIMING	boolean	Provides information about the actual startup time and the time spent in each node of the output. The default value is TRUE. You can only use this option when ANALYZE is set to TRUE.
SUMMARY	boolean	Provides additional information, such as the total time after the query plan. The value of this option is TRUE when ANALYZE is set to TRUE.
FORMAT	{ TEXT | XML | JSON | YAML }	Allows you to define the query plan output format. The default value is TEXT.

Examples

Typically, you start by creating a table in YugabyteDB and inserting rows into it.

To create a table called employees, execute the following:

To insert table rows, execute the following:

To check the query plan for simple select, execute the following:

The following output displays the query execution cost estimate:

QUERY PLAN
----------------------------------------------------------------
Foreign Scan on employees  (cost=0.00..112.50 rows=1000 width=44)
(1 row)

To check the execution plan for select with a complex condition that requires filtering, execute the following:

The following output displays the cost estimate based on the filtered result:

QUERY PLAN
----------------------------------------------------------------
Foreign Scan on employees  (cost=0.00..125.00 rows=1000 width=44)
   Filter: (floor(((k2)::numeric + 1.5)) = (v1)::numeric)
(2 rows)

By enabling the ANALYZE option and wrapping it to preserve data integrity, you can trigger the query execution, as follows:

In addition to the cost estimates from the query planner, EXPLAIN ANALYZE displays the server output produced during the statement execution, as shown in the following example:

QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop Left Join  (cost=0.00..15202.50 rows=5000 width=88) (actual time=2.853..2.885 rows=3 loops=1)
   Join Filter: (a.* = b.*)
   Rows Removed by Join Filter: 6
   ->  Seq Scan on employees_k a  (cost=0.00..100.00 rows=1000 width=112) (actual time=1.747..1.749 rows=3 loops=1)
   ->  Materialize  (cost=0.00..105.00 rows=1000 width=112) (actual time=0.155..0.157 rows=3 loops=3)
         ->  Seq Scan on employees_k b  (cost=0.00..100.00 rows=1000 width=112) (actual time=0.450..0.454 rows=3 loops=1)
 Planning Time: 0.072 ms
 Execution Time: 2.938 ms
(8 rows)

The server output from the preceding example includes the number of rescans (loops) each node performed, the number of milliseconds passed before the first row was returned, total time before the last tuple was returned by each execution node, and the number of tuples returned by each execution node.

EXPLAIN, on the other hand, does not provide this additional information, as shown in the following examples:

QUERY PLAN
----------------------------------------------------------------------
Nested Loop Left Join  (cost=0.00..15202.50 rows=5000 width=88)
   Join Filter: (a.* = b.*)
   ->  Seq Scan on employees_k a  (cost=0.00..100.00 rows=1000 width=112)
   ->  Materialize  (cost=0.00..105.00 rows=1000 width=112)
         ->  Seq Scan on employees_k b  (cost=0.00..100.00 rows=1000 width=112)
(5 rows)

Real world example

The following example is drawn from a real-world scenario, using the EXPLAIN statement to view query plans, and then optimizing those queries by adding indexes and adjusting tables.

Optimize SELECT COUNT using an index

The following table is representative of the customer's data.

                                    Table "public.contacts"
Column          | Type                           | Collation | Nullable | Default
----------------+--------------------------------+-----------+----------+--------
id              | bigint                         |           | not null |
account_id      | integer                        |           |          |
email           | character varying              |           |          |
first_name      | character varying              |           |          |
last_name       | character varying              |           |          |
address_line_1  | character varying              |           |          |
address_line_2  | character varying              |           |          |
address_city    | character varying              |           |          |
address_state   | character varying              |           |          |
address_postal  | character varying              |           |          |
created_at      | timestamp(6) without time zone |           | not null |
updated_at      | timestamp(6) without time zone |           | not null |
is_over_charged | boolean                        |           |          | false
is_paid         | boolean                        |           |          | false
data_source     | character varying              |           |          |

Indexes:
    "contacts_pkey" PRIMARY KEY, lsm (id HASH)

Running the following queries with EXPLAIN output shows the query execution plan generated by YSQL for a given SQL statement.

QUERY PLAN
----------------------------------------------
Aggregate (cost=107.50..107.51 rows=1 width=8)
    Seq Scan on contacts (cost=0.00..105.00 rows=1000 width=0)
        Filter: (is_paid AND is_over_charged AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone) AND (account_id = 1234))
(3 rows)

QUERY PLAN
----------------------------------------------
Aggregate (cost=107.50..107.51 rows=1 width=8)
    Seq Scan on contacts (cost=0.00..105.00 rows=1000 width=0)
        Filter: (is_paid AND is_over_charged AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone) AND (account_id = 5678))
(3 rows)

QUERY PLAN
----------------------------------------------
Aggregate (cost=107.50..107.51 rows=1 width=8)
    Seq Scan on contacts (cost=0.00..105.00 rows=1000 width=0)
        Filter: ((NOT is_paid) AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone) AND (account_id =1234))
(3 rows)

In each case, the queries do a Seq Scan (sequential scan) on the tables. This operation requires scanning the entire table to retrieve the desired columns. Even using the partition keys to do the lookup, it still needs to do a lot of scanning.

Avoid SELECT COUNT(*) queries in most cases, as they can require a full scan of the table to get the results. This can cause query degradation, and in some cases cause the query to not return at all.

Because most of the queries above use account_id as the main qualifier, you can avoid a sequential scan by creating a direct index on that column, and then using the INCLUDE feature to cover the other columns that you also want in the index. Indexing is a powerful tool that can speed up queries with higher latencies. When creating an index, consider the column cardinality, as well as the different index types.

Create the index as follows:

With the index in place, the queries now do an index rather than sequential scan to get the data, significantly improving performance.

QUERY PLAN
------------------------------------------
Aggregate (cost=5.30..5.31 rows=1 width=8)
    Index Scan using contacts_account_id on contacts (cost=0.00..5.28 rows=10 width=0)
    Index Cond: (account_id = 1234)
        Filter: (is_paid AND is_over_charged AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone))
(4 rows) 

Time: 57.208 ms
Previous run time: 194 seconds

QUERY PLAN
------------------------------------------
Aggregate (cost=5.30..5.31 rows=1 width=8)
    Index Scan using contacts_account_id on contacts (cost=0.00..5.28 rows=10 width=0)
    Index Cond: (account_id = 5678)
        Filter: (is_paid AND is_over_charged AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone))
(4 rows)

Time: 11.923 ms
Previous run time 188 seconds

QUERY PLAN
------------------------------------------
Aggregate (cost=5.30..5.31 rows=1 width=8)
    Index Scan using contacts_account_id on contacts (cost=0.00..5.28 rows=10 width=0)
    Index Cond: (account_id = 8060)
        Filter: ((NOT is_paid) AND (updated_at > '2021-04-12 12:00:00'::timestamp without time zone))
(4 rows)

Time: 46.658 ms
Previous run time: 147 seconds

Optimize SELECT by changing table sorting

The following query retrieves data from an account table where some indexes are already defined.

Table definition:

                                    Table "public.accounts"
Column        | Type                        | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+--------
id            | bigint                      | For       | not null | nextval('accounts_id_seq'::regclass)
company_name  | character varying(125)      |           | not null |
status        | character varying(25)       |           |          |
first_name    | character varying(55)       |           |          |
last_name     | character varying(55)       |           |          |
phone         | character varying(25)       |           |          |
created_at    | timestamp without time zone |           | not null |
updated_at    | timestamp without time zone |           | not null |
product_id    | integer                     |           |          |
business_type | character varying(55)       |           |          |
sales_rep     | character varying           |           |          |

Indexes:
    "accounts_pkey" PRIMARY KEY, lsm (id HASH)
    "index_on_company_name" lsm (company_name HASH)

EXPLAIN output for the query is as follows:

QUERY PLAN
----------------------------------------------
Limit (cost=128.22..128.28 rows=25 width=1642)
    Sort (cost=128.22..130.72 rows=1000 width=1642)
        Sort Key: id DESC
            Seq Scan on accounts (cost=0.00..100.00 rows=1000 width=1642)

In this case, a sort is run first, which adds extra time to the query, before running a sequential scan of the table, which also degrades performance.

To optimize this query, adjust the sorting of the table by the primary key to be DESC rather than HASH.

Now, the query no longer does a sequential scan; instead it uses an index scan, cutting execution time from 430ms to 3ms.

QUERY PLAN
------------------------------------------
Limit (cost=0.00..2.85 rows=25 width=1642)
    Index Scan using accounts_pkey on accounts (cost=0.00..114.00 rows=1000 width=1642)

Time: 2.994 ms
Previous run time: 426.627 ms

Optimize SELECT using an index

The query in the following example runs a sequential scan, which you can fix by adding an index.

                                    Table "public.account_type"
Column      | Type                           | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+--------
id          | bigint                         |           | not null | nextval('account_domains_id_seq'::regclass)
account_id  | integer                        |           | For      |
type        | character varying(55)          |           |          |
url         | character varying              |           |          |
is_valid    | boolean                        |           |          |
created_at  | timestamp(6) without time zone |           | not null |
updated_at  | timestamp(6) without time zone |           | not null |
verified_at | timestamp without time zone    |           |          |

Indexes:
    "account_type_pkey" PRIMARY KEY, lsm (id HASH)

QUERY PLAN
----------------------------------------------
Sort (cost=152.33..154.83 rows=1000 width=237)
    Sort Key: url
        Seq Scan on account_type (cost=0.00..102.50 rows=1000 width=237)
            Filter: (account_id = 6873)

The query runs a sequential scan on the account_type table, so adding an index on account_id prevents the full scan of the table, as follows:

With the new index, the query scans the index rather than the larger main table, significantly improving performance.

QUERY PLAN
----------------------------------------
Sort (cost=5.39..5.42 rows=10 width=237)
    Sort Key: url
        Index Scan using account_id on account_type (cost=0.00..5.22 rows=10 width=237)
            Index Cond: (account_id = 6873)
    
Time: 71.757 ms
Previous runtime: 460 ms