Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

EXPLAIN

Name

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 cost of an alternative query.

Two numbers are associated with the cost, separated by two periods. The first number is the estimated cost of startup (the time spent before the first tuple can be returned). The second number is the estimated total cost that the query will incur to completely execute.

If you pass the VERBOSE keyword, EXPLAIN will display the internal representation of the plan tree. This is fairly indecipherable to the average user, and should only be used by developers familiar with the internal workings of PostgreSQL.

Example

The following example shows the results received when executing EXPLAIN for a query on the books table, in the booktown database:

booktown=# EXPLAIN SELECT * FROM books AS b (book_id)
booktown-#         NATURAL INNER JOIN editions;
NOTICE:  QUERY PLAN:

Merge Join  (cost=71.27..83.96 rows=150 width=64)
  ->  Sort  (cost=1.44..1.44 rows=15 width=24)
        ->  Seq Scan on books b  (cost=0.00..1.15 rows=15 width=24)
  ->  Sort  (cost=69.83..69.83 rows=1000 width=40)
        ->  Seq Scan on editions  (cost=0.00..20.00 rows=1000 width=40)

EXPLAIN

The next example shows a verbose explanation of a simpler query, with the VERBOSE keyword:

booktown=# EXPLAIN VERBOSE SELECT * FROM books;
NOTICE:  QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 1.15 :rows 15 :width 24 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}}
{ TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname title
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2}}
{ TARGETENTRY :resdom { RESDOM :resno 3 :restype 23 :restypmod -1 :resname
author_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold
1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 23 :restypmod
-1 :resname subject_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 4}}) :qpqual <> :lefttree <> :righttree <> :extprm ()
:locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

Seq Scan on books  (cost=0.00..1.15 rows=15 width=24)

EXPLAIN

 
 
  Published courtesy of O'Reilly Design by Interspire