The first step in tuning PostgreSQL queries is to understand how to read the execution plans generated by the EXPLAIN command. The EXPLAIN command shows the execution plan of a statement and how data from tables is scanned; for example: a table might be scanned using an index or sequential scan. Also, it shows how tables are joined, the join method, and the estimated number of rows.
The EXPLAIN command also has several options; the ANALYZE option causes the statement to be executed and returns the actual time and number of rows. Finally, the EXPLAIN command can give insights into a buffer's usage and caching. The synopsis for the EXPLAIN command is as follows, where the option can be one of the following: ...
