Practical PostgreSQL

Explain

Shows the statement execution plan for a supplied query.

Synopsis

EXPLAIN [ VERBOSE ] query

Parameters

VERBOSE

The optional verbose output keyword, which results in extra information being returned about a query plan.

query

The query you intend to have explained.

Results

NOTICE: QUERY PLAN: plan

The notice which will be followed by an explicit query plan sent from the backend.

EXPLAIN

The message returned below the query plan, signifying that execution of the command is complete.

Description

Use the EXPLAIN command to view the execution plan for a query, generated by PostgreSQL’s planner component. The planner component is the part of PostgreSQL that attempts to determine the most efficient manner in which to execute a SQL query. The execution plan details how tables referenced within your query will be scanned by the database server. Depending on the circumstances, tables might be scanned sequentially, or through the use of an index. The plan will list output for each table involved in the execution plan.

The EXPLAIN command is useful for determining the relative cost of query execution plans. This cost is measured literally in disk page fetches. The more pages needed, the longer it takes a query to run.

PostgreSQL does not attempt to equate this number of fetches into a meaningful unit of time, as this will vary widely from machine to machine based on the hardware requirements and load of the operating system. The cost of a query execution plan is therefore only meaningful to the relative ...