EXPLAIN plan in PostgreSQL

• Pradip
• 28 Nov, 2024
• 0 Comments
• 4 Mins Read

EXPLAIN plan in PostgreSQL

What is EXPLAIN in PostgreSQL?

An EXPLAIN plan in PostgreSQL is a feature used to analyze and understand how a query will be executed by the PostgreSQL query planner. It provides detailed information about the steps PostgreSQL will take to execute a given query, including how tables are scanned, how joins are performed, and how data is sorted or aggregated.

This feature is essential for debugging performance issues and optimizing queries. The EXPLAIN command displays the plan, while EXPLAIN ANALYZE executes the query and shows the actual runtime statistics, helping you compare the estimated and real performance of the query.

Key Components of an EXPLAIN Plan 

1. Seq Scan: Indicates a sequential scan of a table, where every row is examined.

1. Index Scan: Indicates that an index is used to retrieve rows more efficiently.

1. Bitmap Heap/Index Scan: A hybrid method using bitmaps to identify blocks of rows efficiently.

1. Nested Loop, Hash Join, Merge Join: Show the types of join algorithms used for combining rows from multiple tables.

1. Cost: Displays the estimated cost of executing the plan. This includes:

• Startup Cost: Cost to retrieve the first row.

• Total Cost: Cost to retrieve all rows.

1. Rows: Estimated number of rows returned by a step in the plan.

1. Width: Estimated size of a single row in bytes.

Using EXPLAIN: Examples and Analysis

1. Basic EXPLAIN

EXPLAIN SELECT * FROM students WHERE grade = 'A'; 

EXPLAIN with ANALYZE 

EXPLAIN ANALYZE SELECT * FROM students WHERE grade = 'A';

Interpreting Output

Seq Scan on students  (cost=0.00..12.75 rows=1 width=34)

Filter: (grade = ‘A’)

• Seq Scan: A sequential scan is performed on the students table.

• Cost: Starts at 0.00 and ends at 12.75.

• Rows: Planner estimates that 1 row matches the condition.

• Width: Each row is 34 bytes wide.

Use Cases

1. Optimize Queries: Identify slow queries and restructure them for better performance.

1. Index Analysis: Check if indexes are being used or if additional indexes are needed.

1. Compare Plans: Evaluate the impact of changes in query structure or database schema.

If you’re troubleshooting a query, using EXPLAIN ANALYZE is particularly valuable as it shows the real execution time and any discrepancies from the estimates.

Here’s a detailed explanation of how to use EXPLAIN in PostgreSQL, with an example.

  1. Basic EXPLAIN

The EXPLAIN statement provides the query execution plan without actually running the query. It gives you an estimate of how PostgreSQL plans to execute the query.

Example Query

Suppose we have a table called students with the following schema:

CREATE TABLE students ( 

    id SERIAL PRIMARY KEY, 

    name TEXT, 

    grade CHAR(1), 

    age INT 

); 

 

INSERT INTO students (name, grade, age) 

VALUES 

('Alice', 'A', 20), 

('Bob', 'B', 22), 

('Charlie', 'A', 23), 

('Diana', 'C', 19); 

Now, let’s analyze this query:

EXPLAIN SELECT * FROM students WHERE grade = 'A'; 

Output

Seq Scan on students  (cost=0.00..13.50 rows=2 width=37)

Filter: (grade = ‘A’)

Explanation:

• Seq Scan: A sequential scan is being performed because no index exists on the grade column.

• Cost:

• 0.00: The startup cost (fetching the first row).

• 13.50: The total cost to scan the table.

• Rows: The planner estimates 2 rows will match the condition.

• Width: Each row’s size is estimated to be 37 bytes.
  

  
  2. EXPLAIN with Index

Let’s create an index on the grade column and see how it affects the plan:

CREATE INDEX idx_grade ON students(grade);

EXPLAIN SELECT * FROM students WHERE grade = 'A'; 

Output

Index Scan using idx_grade on students  (cost=0.14..8.27 rows=2 width=37)

Index Cond: (grade = ‘A’)

Explanation:

• Index Scan: PostgreSQL now uses the index idx_grade for efficient lookup.

• Cost: The total cost (8.27) is much lower than the sequential scan.

• Index Cond: Shows the condition being applied on the index.

 

  3. EXPLAIN ANALYZE

The EXPLAIN ANALYZE command executes the query and shows the actual runtime statistics, along with the execution plan.

Example

EXPLAIN ANALYZE SELECT * FROM students WHERE grade = 'A'; 

Output

Index Scan using idx_grade on students  (cost=0.14..8.27 rows=2 width=37) (actual time=0.020..0.045 rows=2 loops=1)

Index Cond: (grade = ‘A’)

Planning Time: 0.125 ms

Execution Time: 0.086 ms

Additional Insights:

• Actual Time: Shows the real time taken for this step (in milliseconds).

• Rows: Confirms that 2 rows were returned (matches the estimate).

• Loops: Indicates the number of times this step was executed (here, 1 loop).

• Planning Time: Time taken to generate the query plan.

• Execution Time: Total time taken to execute the query.

 

Best Practices for Query Optimization Using EXPLAIN

1. Index Analysis:
   • Ensure critical columns in WHERE clauses are indexed.
   • Use EXPLAIN to verify index utilization.
2. Avoid Full Table Scans:
   • Analyze sequential scans and replace them with index scans where appropriate.
3. Analyze Joins:
   • Check join types and restructure queries to use efficient join algorithms (e.g., Hash Join, Merge Join).
4. Monitor Costs:
   • Lower total cost indicates better query performance. Use EXPLAIN to identify expensive operations.
5. Compare Plans:
   • Run EXPLAIN ANALYZE to compare estimated and actual query performance.

 

Conclusion

PostgreSQL’s EXPLAIN and EXPLAIN ANALYZE are indispensable tools for debugging and optimizing queries. By understanding query execution plans and cost metrics, you can identify inefficiencies, create effective indexes, and achieve significant performance gains. Regularly analyzing your queries helps maintain a robust and efficient database system.

Optimize your queries today with PostgreSQL EXPLAIN and unlock better database performance!

Happy Reading!

ANKUSH😎