Explain plan without execute the SQL in PostgreSQL

Check the Explain plan for the SQL Query in PostgreSQL

Explain plan is the analyze of the query which show us the how the table is scanned, It it using index or full scan going. It help to under stand that we need to build index on these column as fixed performance issue. It will give us detail of join algorithms used, cost , disk page usages etc.

Check the explain plan without executing the SQL Query:

testdb=# explain select * from actor where actor_id = 200;
                      QUERY PLAN
 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
   Filter: (actor_id = 200)
(2 rows)

With Verbose it will give more detail in execution plan:

testdb=# explain verbose select actor_id from actor where actor_id = 200;
                         QUERY PLAN
 Seq Scan on public.actor  (cost=0.00..4.50 rows=1 width=4)
   Output: actor_id
   Filter: (actor.actor_id = 200)
(3 rows)

Example of showing index usage in Explain plan:

testdb=# explain select * from film where film_id=33;
                               QUERY PLAN
 Index Scan using film_pkey on film  (cost=0.28..8.29 rows=1 width=384)
   Index Cond: (film_id = 33)
(2 rows)

Note: Insert, update or delete not having any effect if using only EXPLAIN in statment for check execution plan because it;s not actually executing the SQL query.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.