PostgreSQL EXPLAIN command

The EXPLAIN command in Postgres is a very useful tool, it gives insights into how Postgres will perform queries, which means it helps us understand and compare queries for optimization purposes. In this post i'll explain how to use EXPLAIN command.

Why?

As previously mentioned, the EXPLAIN command will help us understand and optimize queries, to further understand why this is needed we need to look at SQL.
SQL is what is known as a 'Declarative language', which means we Declare what we want, and the database figures out the best way to execute instructions to return the results of our query.
A special module called the Planner is responsible for figuring out the best 'plan' to execute the query, and the EXPLAIN command allows us to see that plan.

Usage

Using the EXPLAIN command is as simple as prepending it to your normal query, for example:

EXPLAIN SELECT * FROM <database_name>;

will return the estimated plan and cost in plain text (though you can change the output format as we will see next).

ANALYZE can also be prepended with EXPLAIN to run the query and output the actual run statistics.

• Note: This will also run your query, so be careful not to alter the database!

Interpreting the Output

The output generated by the EXPLAIN command contains details about the sequence of steps that PostgreSQL will take to retrieve the data.

Some of the components we see in the output are:

1. Node Types: Node types represent the different operations performed during the query execution.
2. Common node types include: "Seq Scan" (sequential scan), "Index Scan" (index scan), "Nested Loop", ...etc.
3. Join Types: If the query involves joining multiple tables, you will see the type of join used.
4. join types include: "Nested Loop Join", "Hash Join", and "Merge Join".
5. Relation Information: For each node, you'll find details about the involved relations or tables (their names, aliases, and access methods, ...etc).
6. Filter Conditions: Filter conditions applied during the execution.
7. Costs: The cost of each operation during query execution, it reflects the computational effort required, and it helps the planner choose the most efficient execution plan.
8. Execution Order: The order in which nodes are processed.

Performance optimizations

Here are some tips for optimizing queries once you have the output of EXPLAIN:

1. Look for Sequential Scans: Sequential scans can be slow on large tables. These can be mitigated by creating indexes on columns frequently used in WHERE clauses or JOIN conditions to speed up queries.
2. Large Sort Operations: Sorting large datasets can be resource-intensive and is best avoided. Ensure you have indexes on columns used in ORDER BY clauses. Consider Caching: If you notice repetitive queries in the output, caching might be beneficial to reduce database load.

References

1. https://www.crunchydata.com/blog/get-started-with-explain-analyze