Analyzing Query Plans with EXPLAIN

When you ask PostgreSQL for data, it doesn't just grab it. It first figures out the best way to get that data. This 'best way' is called a query plan.

Understanding query plans is crucial for database performance tuning. It helps you see how PostgreSQL executes your queries and, more importantly, why some queries are slow.

PostgreSQL provides the EXPLAIN command to show you the execution plan for any SQL statement. It's like peeking behind the curtain to see the database's strategy.

EXPLAIN doesn't actually run your query. Instead, it generates an estimated plan based on table statistics. This plan includes details like the chosen access methods (how data is read) and join orders.

Let's see EXPLAIN in action with a simple query. We'll create a small table and then ask for its plan.

Notice the Seq Scan, meaning 'sequential scan,' which reads every row of the table.

The output from EXPLAIN can seem complex, but two key metrics are cost and rows:

• cost: An estimated measure of the query's execution expense. It has two numbers: {startup_cost}..{total_cost}. Startup cost is before the first row is returned; total cost is for the entire operation. These are relative, not absolute time units.
• rows: The estimated number of rows that the plan node will output.

Lower costs are generally better, but always consider the estimated rows for accuracy.

One of the first things to look for in a query plan is the scan type. This tells you how PostgreSQL accesses the data:

• Sequential Scan (Seq Scan): Reads every single row in the table, one by one. This is efficient for small tables or when you need most of the table's data.
• Index Scan: Uses an index to quickly locate specific rows. This is usually much faster for queries filtering a small subset of a large table.

The choice depends on your query and table structure.

While EXPLAIN provides estimates, EXPLAIN ANALYZE actually runs the query and collects real-world statistics. This is incredibly powerful for identifying performance bottlenecks.

It adds actual execution times and row counts to the plan, allowing you to compare estimates with reality. Large discrepancies often point to outdated table statistics or a poorly chosen plan.

Let's create a larger table and run EXPLAIN ANALYZE on a query that will likely perform a sequential scan. Pay attention to the actual time and rows.

We'll create 1000 users and query for a specific age range.

Now, let's create an index on the age column from our users table. Then, we'll re-run the same EXPLAIN ANALYZE query.

You should see a significant change in the plan, likely from a Seq Scan to an Index Scan, and a reduction in actual execution time.

Query plans can get very complex with multiple operations. Here are tips for reading them:

• Read Bottom-Up: PostgreSQL typically processes the innermost (bottom) operations first.
• Look for Indentation: Indentation shows nested operations. An inner operation provides input to its parent.
• Identify Expensive Nodes: Look for nodes with high total_cost or actual time. These are your bottlenecks.
• Compare Estimates vs. Actuals: Significant differences in rows between estimated and actual can indicate bad statistics or a misleading plan.

Consider the following EXPLAIN output for a query on a table named orders. What does it tell us about how the query will be executed?

Seq Scan on orders  (cost=0.00..10.50 rows=5 width=100)
  Filter: (amount > 100)

Congratulations! You've taken a deep dive into analyzing PostgreSQL query plans.

• EXPLAIN shows an estimated query plan.
• EXPLAIN ANALYZE runs the query and shows actual execution statistics.
• Look for scan types (Seq Scan, Index Scan) to understand data access.
• Compare estimated vs. actual costs and rows to find bottlenecks.
• Read plans bottom-up to follow the execution flow.

Mastering EXPLAIN is a cornerstone of PostgreSQL performance tuning. Keep practicing!