Understanding Postgres Sql Plan

Introduction

Understanding SQL plan is mandatory if we are aiming to optimize an SQL query. The plan provides us with some interesting information that we are going to explore together in this article.

Situation

Consider that we have a table salary_adjustments defined like this:

-- Create table
CREATE TABLE salary_adjustments (
    adjustment_id INTEGER PRIMARY KEY,    
    employee_id INTEGER,                  
    previous_salary_id INTEGER,           
    amount DECIMAL(10,2),
    effective_date DATE
);

-- Create index on employee_id
CREATE INDEX idx_salary_adj_emp 
ON salary_adjustments(employee_id);

Which of those is the most performant query?

SELECT 
    sa.employee_id as emp,
    COUNT(sa.adjustment_id) as salary_adjustment_count
FROM salary_adjustments sa
GROUP BY sa.employee_id
HAVING COUNT(sa.adjustment_id) > 1;

SELECT 
    sa.employee_id as emp,
    COUNT(*) as salary_adjustment_count
FROM salary_adjustments sa
GROUP BY sa.employee_id
HAVING COUNT(*) > 1;

To answer this, let’s examine the query plan.

Execution Plan

To run an execution plan you can execute one of these commands:

EXPLAIN:

• To quickly see how PostgreSQL plans to execute the query
• To verify index usage
• When you don’t want to impact the database (heavy query)

EXPLAIN ANALYZE:

• To see actual performance metrics
• To identify estimation discrepancies
• For performance debugging
• To measure optimization impact
• Actually executes the query, while EXPLAIN only shows the plan

Comparing the executions plan of our queries

Version with COUNT(adjustment_id) :

GroupAggregate  (cost=140239785.58..193956412.94 rows=158293619 width=16) (actual time=334528.646..411045.462 rows=134377 loops=1)
  Group Key: employee_id
  Filter: (count(adjustment_id) > 1)
  Rows Removed by Filter: 503475932
  ->  Sort  (cost=140239785.58..141500120.46 rows=504133952 width=16) (actual time=281905.337..333116.802 rows=504035748 loops=1)
        Sort Key: employee_id
        Sort Method: external merge  Disk: 12824480kB
        ->  Seq Scan on salary_adjustments sa  (cost=0.00..62076736.20 rows=504133952 width=16) (actual time=0.011..112818.425 rows=504035748 loops=1)

Planning Time: 0.092 ms
Execution Time: 413822.092 ms

Version with COUNT(*) :

GroupAggregate  (cost=0.57..109723958.14 rows=163294901 width=16) (actual time=86675.662..206504.852 rows=134377 loops=1)
  Group Key: employee_id
  Filter: (count(*) > 1)
  Rows Removed by Filter: 503475932
  ->  Index Only Scan using idx_salary_adj_emp on salary_adjustments sa  (cost=0.57..55712632.70 rows=506419104 width=8) (actual time=0.101..118494.131 rows=506246363 loops=1)
        Heap Fetches: 788294

Planning Time: 1.882 ms
Execution Time: 206511.080 ms

According to the execution plans, we can clearly answer the question: the version with COUNT(*) is more performant. It took half the time of the version with COUNT(adjustment_id), but why?

As we can see, in the version with COUNT(*), the Executor performed an Index Only Scan while it performed a Seq Scan with an external sort that used about 12.8 GB of disk in the other version.

The version with COUNT(*) needs to access only the index because the index on employee_id contains all the information we need to perform our operations — we’re just counting rows per employee_id. Whereas, in the version with COUNT(adjustment_id), we need to access both employee_id and adjustment_id columns, and we don’t have an index that contains both of them. This forces PostgreSQL to read the entire table (Seq Scan) and sort the data to perform the grouping operation.

Different types of Scan

There are several types of scans, let’s explain three of them:

Seq Scan: performs as if you have a book and you read it page by page sequentially.

Index Scan: performs as if you look into the index to see which chapter interests you more, then you look at the corresponding page.

Index Only Scan: performs as if you look only into the index to find information that does not need to see other pages, such as getting the numbers of chapters in the book.

Going deeper in the explanation

Reading an execution plan

An execution plan should be read bottom-top:

• Version with COUNT(): Index Only Scan with Heap Fetches -> GroupAggregate and Filter (count() > 1)
• Version with COUNT(adjustment_id): Seq Scan -> Sort with external merge Disk -> GroupAggregate and Filter: (count(adjustment_id) > 1)

Understanding key words

Estimation of execution of a node: After each node we can see two parentheses. The first one is an estimation provided by the planner: (cost=140239785.58..141500120.46 rows=504133952 width=16)

Get Anass Guendef’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Actual statistics of execution of a node: The second parentheses contains actual stats after executing the node. This information is returned by the executor, not the planner: (actual time=86675.662..206504.852 rows=134377 loops=1)

Cost: This is not in milliseconds. It is an arbitrary unit based on some parameters configured in PostgreSQL. seq_page_cost is conventionally set to 1.0 and the other cost parameters are set relative to that. You can execute these queries if you want to see the values of some parameters:

SHOW seq_page_cost; -- sequentiel cost (default: 1.0) 
SHOW random_page_cost; -- random cost (default: 4.0) 
SHOW cpu_tuple_cost; -- cost of reading a line (default: 0.01) 
SHOW cpu_index_tuple_cost; -- cost of reading an index (default: 0.005) 
SHOW cpu_operator_cost; -- cost of cpu oprtation (default: 0.0025

In our example we have cost=140239785.58..141500120.46

• The first value is the startup cost; it determines how much it’s going to take to fetch the first row.
• The value after .. is total cost; it determines how much it’s going to take to fetch all the rows.

Rows: In the estimated part, this is the number of rows estimated to be returned by the node. In the actual part, this is the real number of rows returned when executing the node.

Width: The estimated size occupied by each line, here it’s equal to 16 bytes because we are returning two columns of type bigint (8 bytes each). This information is important because we can use it to calculate the size of our result set, which is the number of lines multiplied by the width. In our example 134377*16 = 2 MB.

Loops: Number of times the operation was executed

Heap Fetches: The number where it was necessary to access the table during the index only scan. This operation is performed if the table is not vacuumed.

Vacuum consists of cleaning the frozen lines on a table. When we perform delete in PostgreSQL, lines are marked as removed but are not truly deleted. This is done after a vacuum.

PostgreSQL has a data structure called “Visibility Map”. The VM tells us whether all the lines of a table are visible to all transactions or not. A VM is updated by delete operations and vacuum. When delete operation occurs the VM equivalent is marked as not visible until the vacuum is executed.

In our case, Heap Fetches was done because the executor consulted the visibility map and saw that some lines are not visible, thus it was necessary to check the table for 788294 lines to see if they exist or not. If the vacuum was performed before, Heap fetches would be = 0.

Sort Method: external merge Disk: The Executor performs an external merge when there isn’t enough work_mem available to perform the sort operation entirely in memory.

rows × width × overhead = Memory needed 504,133,952 × 16 bytes × ~1.1 = ~8.9 GB

Comparison between estimated and actual statistics

Query 1 (with COUNT(adjustment_id)):

GroupAggregate
- Estimated rows: 158,293,619
- Actual rows: 134,377
- Accuracy: Off by ~99.92%

Sort
- Estimated rows: 504,133,952
- Actual rows: 504,035,748
- Accuracy: Off by ~0.02%

Seq Scan
- Estimated rows: 504,133,952
- Actual rows: 504,035,748
- Accuracy: Off by ~0.02%

Query 2 (with COUNT(*)):

GroupAggregate
- Estimated rows: 163,294,901
- Actual rows: 134,368
- Accuracy: Off by ~99.92%

Index Only Scan
- Estimated rows: 506,419,104
- Actual rows: 506,246,363
- Accuracy: Off by ~0.03%

GroupAggregate row estimation is significantly off Base scan estimations are very accurate

Why the Differences:

• Statistics limitations for complex operations
• Lack of detailed statistics about data distribution

This analysis helps us understand:

• Where the planner needs better statistics
• Which operations are harder to estimate
• How accurate the base table statistics are

We can update the table statisitcs with Analyse command.

What ANALYZE Does:

1. Samples the table data
2. Updates statistics about:

• Number of distinct values in each column
• Most common values and their frequencies
• Data distribution statistics
• Correlation between physical and logical row ordering

Conclusion

Understanding execution plans is essential for query optimization. The key is to:

• Know how to read and interpret the plans
• Understand different scan types and their implications
• Recognize when estimates are accurate or not
• Maintain database statistics and indexes properly