How PostgreSQL Executes Your Queries: EXPLAIN, ANALYZE, and Index Strategies

Không phân loại

1. Introduction

This document summarizes knowledge and experience on database query optimization, focusing on using EXPLAIN, EXPLAIN ANALYZE, table access methods, and index optimization. It’s suitable for developers and DBAs who want a deep understanding of how databases execute queries and techniques to improve performance.

2.1 Execution Plan with EXPLAIN

The execution plan reveals how the database will execute a query. To get the execution plan, prefix your query with EXPLAIN, for example:

EXPLAIN SELECT * FROM film;

EXPLAIN plan example

An execution plan consists of nodes (steps), such as:

  • Seq Scan: sequentially scans the table
  • cost: execution cost of the step (startup cost → total cost)
  • rows: estimated number of output rows
  • width: average width in bytes per row

More complex queries with ORDER BY, LIMIT, GROUP BY, aggregates, DISTINCT, JOIN, etc., require multiple steps:


EXPLAIN SELECT length, rating
FROM film
WHERE rental_duration = 7
ORDER BY length
LIMIT 10;

EXPLAIN steps

Example execution plan:

Execution flow example

2.2 Execution Plan with EXPLAIN ANALYZE

EXPLAIN ANALYZE executes the query and provides actual statistics:


EXPLAIN ANALYZE
SELECT film_id, title, description
FROM film
WHERE rating IN ('R', 'PG-13')
ORDER BY rental_duration DESC;

EXPLAIN ANALYZE output

Additional info from EXPLAIN ANALYZE:

  • loops: how many times each node ran
  • actual time: real execution time per node (startup → total)
  • actual rows: actual number of rows returned
  • Additional details (filtered rows, memory usage, sort size, etc.)
  • Total query runtime

Note: Use EXPLAIN ANALYZE sparingly in production, especially on data-changing queries—wrap them in a transaction and rollback afterward to avoid side effects.

3. Table Access Methods

3.1 Sequential Scan

Reads every row in the table one by one:

SELECT * FROM film;

Sequential Scan

3.2 Index Scan

Steps:

  1. Access index
  2. Find matching index entries → retrieve row IDs
  3. Use row IDs to fetch actual rows from the table

Index structure
Row IDs
Index to table lookup
Index scan flow

3.3 Index Only Scan

Uses only the index when all needed columns are in the index:

Heap vs Index storage
Index Only Scan
Demo Index Only Scan
Index Only vs Heap needed

3.4 Bitmap Scan

Efficient when many rows match. It batches index results into a bitmap before accessing the table:

Sequential vs Index
Bitmap array
Bitmap AND example

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 50;

Bitmap scan example

EXPLAIN ANALYZE SELECT * FROM users WHERE age = 50 AND last_name = 'Joey';

Bitmap AND scan

4. Tuning with Indexes

4.1 B‑Tree Index

B‑Tree is the default balanced tree structure used in PostgreSQL:

B‑Tree illustration
BST tree imbalance
Balanced tree concept
B‑Tree structure

4.2 Hash Index

Useful for equality queries (=) on non-indexed columns or when B‑Tree is inefficient. Learn more here.

CREATE INDEX ON users USING HASH(email);
  • Build → hash values into buckets
  • Probe → hash probe input and search bucket

Hash join diagram
Hash buckets
Hash join animation
Hash join animation

4.3 Composite Index

Create an index on multiple columns:

CREATE INDEX idx_name_age ON users(name, age);

Composite index usage

5. Index Suppression

(TBU)

6. Simple Tuning Techniques

(TBU)

7. Performance Enhancements

8. SQL Best Practices

(TBU)

9. Join Methods

(TBU)

10. References

Published