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.