EXPLAIN is the statement that allows you to see how PostgreSQL is going to execute a specific query. You have to pass the statement you want to analyze to EXPLAIN, and the execution plan will be shown.
There are a few important things to know before using EXPLAIN:
- It will only show the best plan, which is the one with the lowest cost among all the evaluated plans.
- It will not execute the statement you are asking the plan for, therefore the EXPLAIN execution is fast and pretty much constant each time.
- It will present you with all the execution nodes that the executor will use to provide you with the dataset.
Let's see an example of EXPLAIN in action to better understand. Imagine we need to understand the execution plan of the SELECT * FROM categories statement. In this case, you need to prefix the statement with the EXPLAIN command, as follows:
forumdb=> EXPLAIN SELECT * FROM categories;
QUERY PLAN
--------...
