PostgreSQL Scans Explained

PostgreSQL uses various methods to scan tables and retrieve data efficiently, depending on the query and table structure. Here's an explanation of Sequential Scan, Index Scan, and Bitmap Index Scan with examples.

Sequential Scan

Definition: Reads every row in the table sequentially. This is the simplest and most straightforward method.

Use Case:

• When no suitable index exists.
• If the query retrieves a large portion of the table.
• The query planner determines it is more efficient than using an index.

Example:

Suppose we have a table employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    department TEXT
);

And we run:

-- Example: Sequential Scan
EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

• If no index exists on age, PostgreSQL will perform a Sequential Scan.
• The planner reads each row, checks the condition (age > 30), and retrieves matching rows. Output (simplified):

Seq Scan on employees  (cost=0.00..10.00 rows=3 width=50)
  Filter: (age > 30)

This happens if no index exists on age, forcing PostgreSQL to read all rows and filter those matching age > 30.

Index Scan

Definition: Uses an index to directly find the rows that match the query condition, retrieving them one by one.

Use Case:

• When the query retrieves a small subset of rows.
• A matching index exists for the query condition.
• Highly selective queries.

-- Example: Index Scan
CREATE INDEX idx_age ON employees(age);

EXPLAIN ANALYZE SELECT * FROM employees WHERE age = 35;

• PostgreSQL uses the index to locate rows with age = 35 efficiently. output :

Index Scan using idx_age on employees  (cost=0.00..5.00 rows=1 width=50)
  Index Cond: (age = 35)

Bitmap Index Scan

Definition: Uses an index to fetch matching rows but instead of retrieving them one by one, it creates a bitmap of the matching rows' locations and fetches them in bulk.

Use Case:

• When multiple rows need to be fetched and scattered across the table.
• More efficient than Index Scan for large result sets.
• Often paired with a Bitmap Heap Scan to retrieve rows.

-- Example: Bitmap Index Scan
EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

• With the idx_age index, PostgreSQL might decide a Bitmap Index Scan is better if many rows match age > 30. Output:

Bitmap Heap Scan on employees  (cost=5.00..15.00 rows=10 width=50)
  Recheck Cond: (age > 30)
  -> Bitmap Index Scan on idx_age  (cost=0.00..4.00 rows=10 width=0)
       Index Cond: (age > 30)

Steps in Bitmap Index Scan:

• Bitmap Index Scan: Identifies all rows that satisfy the condition (age > 30) and creates a bitmap (a list of row locations).
• Bitmap Heap Scan: Fetches the rows from the table using the bitmap, possibly in sorted order to optimize I/O.

Comparison

Summary

• Seq Scan: Full table read, ignores indexes.
• Index Scan: Retrieves rows one by one using an index.
• Bitmap Index Scan: Retrieves many rows efficiently by using a bitmap and fetching in bulk.