Introduction to PostgreSQL EXPLAIN statement

TheEXPLAIN statement returns the execution plan which PostgreSQL planner generates for a given statement.

TheEXPLAIN shows how tables involved in a statement will be scanned by index scan or sequential scan, etc., and if multiple tables are used, what kind of join algorithm will be used.

The most important and useful information that theEXPLAIN statement returns are start-cost before the first row can be returned and the total cost to return the complete result set.

The following shows the syntax of theEXPLAIN statement:

EXPLAIN [ (option [,...] ) ] sql_statement;

whereoption can be one of the following:

ANALYZE [boolean ]VERBOSE [boolean ]COSTS [boolean ]BUFFERS [boolean ]TIMING [boolean ]SUMMARY [boolean ]FORMAT { TEXT| XML| JSON| YAML }

Theboolean specifies whether the selected option should be turned on or off. You can useTRUE,ON, or1 to enable the option, andFALSE,OFF, or0 to disable it. If you omit the boolean, it defaults toON.

ANALYZE

TheANALYZE option causes thesql_statement to be executed first and then actual run-time statistics in the returned information including total elapsed time expended within each plan node and the number of rows it returned.

TheANALYZE statement executes the SQL statement and discards the output information, therefore, if you want to analyze any statement such asINSERT,UPDATE, orDELETE without affecting the data, you should wrap theEXPLAIN ANALYZE in a transaction, as follows:

BEGIN;    EXPLAIN ANALYZE sql_statement;ROLLBACK;

VERBOSE

TheVERBOSE parameter allows you to show additional information regarding the plan. This parameter sets toFALSE by default.

COSTS

TheCOSTS option includes the estimated startup and total costs of each plan node, as well as the estimated number of rows and the estimated width of each row in the query plan. TheCOSTS defaults toTRUE.

BUFFERS

This parameter adds information to the buffer usage.BUFFERS only can be used whenANALYZE is enabled. By default, theBUFFERS parameter set toFALSE.

TIMING

This parameter includes the actual startup time and time spent in each node in the output. TheTIMING defaults toTRUE and it may only be used whenANALYZE is enabled.

SUMMARY

TheSUMMARY parameter adds summary information such as total timing after the query plan. Note that whenANALYZE option is used, the summary information is included by default.

FORMAT

Specify the output format of the query plan such asTEXT,XML,JSON, andYAML. This parameter is set toTEXT by default.

PostgreSQL EXPLAIN examples

The following statement shows the plan for a simple query on the  film table:

EXPLAIN SELECT * FROM film;

The output is as follows:

The following example shows the plan for a query that returns a film by a specificfilm_id.

EXPLAIN SELECT * FROM film WHERE film_id = 100;

Here is the output:

Because thefilm_id is indexed, the statement returned a different plan. In the output, the planner used an index scan instead of a sequential scan on thefilm table.

To suppress the cost, you can use theCOSTS option:

EXPLAIN (COSTSFALSE) SELECT    *FROM    filmWHERE    film_id = 100;

The following example displays the plan for a query that uses an aggregate function:

EXPLAIN SELECT COUNT(*)FROM film;

The output is:

The following example returns a plan for a statement that joins multiple tables:

EXPLAINSELECT    f.film_id,    title,    name category_nameFROM    film f    INNER JOIN film_category fc        ON fc.film_id = f.film_id    INNER JOIN category c        ON c.category_id = fc.category_idORDER BY    title;

The output is:

To add the actual runtime statistics to the output, you need to execute the statement using theANALYZE option:

EXPLAIN ANALYZE    SELECT        f.film_id,        title,        name category_name    FROM        film f        INNER JOIN film_category fc            ON fc.film_id = f.film_id        INNER JOIN category c            ON c.category_id = fc.category_id    ORDER BY        title;

Here is the output:

In this tutorial, you have learned how to use the PostgreSQLEXPLAIN statement to show the query’s plan for a specific SQL statement.

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL EXPLAIN statement
• PostgreSQL EXPLAIN examples