Overview

For every SQL query that PostgreSQL executes, the PostgreSQL query planner or optimizer creates a plan for how to execute it.

Using the PostgreSQL EXPLAIN command for a query shows you which query plan was used to execute your query. Reading an EXPLAIN plan shows which indexes (if any) a query uses and can point to how to improve query performance.

Example plan:

$ psql> EXPLAIN select * from items;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on items  (cost=0.00..1.02 rows=8 width=52)
(1 row)

EXPLAIN Plans

The Postgres Monitor agent automatically collects EXPLAIN plans for slow queries that appear in your PostgreSQL logs.

For Heroku, the logged slow queries are controlled by the log_min_duration_statement setting (which defaults to 2 seconds). This setting can be modified in the Heroku PostgreSQL database settings.

You can view these EXPLAIN plans through the Queries dashboard.

Read more on how to understand EXPLAIN plans.

ANALYZE Statistics

The query planner uses statistics about tables and indexes that are generated whenever ANALYZE is run for a table or the autovacuum process runs for a table. If ANALYZE has not run recently for a table, a suboptimal plan may be selected for a query.

Read more about the ANALYZE command or how to configure the autovacuum process for Heroku PostgreSQL servers.