EXPLAIN in PostgreSQL can be used to print the execution plan considered by the PostgreSQL optimizer for a given query. However, to see the actual cost and time consumed by SQL, we must use EXPLAIN ANALYZE. In this recipe, we will discuss how EXPLAIN can be used to identify an area where a query needs to be optimized.
Getting ready
In order to run EXPLAIN, we need to have a SQL statement that has its bind variables substituted with the actual values. Otherwise, EXPLAIN returns an error. In addition to that, while running EXPLAIN ANALYZE, we must always avoid running it on a SQL statement that is performing a DML or a DDL. This is because it executes the statement and prints the execution plan and actual cost information. In emergency situations, we may carefully open a transaction and run EXPLAIN ANALYZE on a statement and roll it back immediately.
How to do it...
EXPLAIN is available in PostgreSQL by default. The following are...