PostgreSQL Query Plan Visualizer

Postgres Query Planning

PostgreSQL provides advanced tooling to understand how it executes SQL queries. The execution of a query follows specific steps:

• Parsing
• Planning
• Optimization
• Execution

When it comes to the execution step, all Postgres does is follow the selected optimized plan. It is possible to ask Postgres for the query plan and inspect it, in order to better understand the execution of any query and then when necessary find a way to execute the same query faster.

There are several ways to make a query execute faster. The best known is through indexing. Postgres indexes provide an alternative access path to the data, which in some cases involves less work than accessing the rows on the heap storage of the tables directly.

Statistics are another important tool to use for making a query execute faster with Postgres. Postgres keeps stats about every table and their contents, and uses those statistics in the optimization step above. Having better statistics, either in quality, in quantity, and sometimes both, can help Postgres make better decisions about what an optimal query plan looks like.

Explain Plan Visualizer

To help with reading and understanding query plans, you can use the PEV tool, which is Open Source and easy enough to integrate in a website. So that’s what we did at Explain Plan Visualizer. You can paste your query and explain plan here and see a nice diagram with visual hints to better understand your query plans:

The PNG saving feature is experimental. It might even work on your browser and with your own query plan… sometimes!