What is Index Scan in PostgreSQL: Explanation and Example

In PostgreSQL, an index scan is a method to find rows in a table by searching an index instead of scanning the whole table. It speeds up queries by quickly locating data using the index structure.

How It Works

Imagine you have a big book and you want to find a specific topic. Instead of reading every page, you use the index at the back to jump directly to the pages you need. An index scan in PostgreSQL works the same way. It uses a special data structure called an index to quickly find rows that match your query.

When you run a query with conditions on indexed columns, PostgreSQL can use the index to find matching rows without checking every row in the table. This saves time, especially for large tables. The database looks up the index, finds pointers to the matching rows, and then fetches those rows from the table.

Example

This example shows how PostgreSQL uses an index scan to find rows with a specific value in an indexed column.

sql

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

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'Engineering'),
('Carol', 'Sales'),
('Dave', 'Engineering');

CREATE INDEX idx_department ON employees(department);

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Output

Index Scan using idx_department on employees  (cost=0.29..8.31 rows=2 width=36) (actual time=0.012..0.014 rows=2 loops=1)
  Index Cond: (department = 'Sales'::text)
Planning Time: 0.123 ms
Execution Time: 0.030 ms

When to Use

Use an index scan when you want to speed up queries that filter rows based on indexed columns. It is especially helpful for large tables where scanning every row would be slow.

For example, if you often search for employees by department or customers by city, creating an index on those columns lets PostgreSQL quickly find matching rows using an index scan. However, for very small tables or queries that return most rows, a full table scan might be faster.

Key Points

• An index scan uses an index to find rows quickly without scanning the whole table.
• It improves query speed for large tables with selective filters.
• PostgreSQL decides automatically when to use an index scan based on query cost.
• Indexes add some overhead for inserts and updates, so use them wisely.

Key Takeaways

An index scan uses an index to quickly locate rows matching query conditions.

It speeds up queries on large tables by avoiding full table scans.

PostgreSQL automatically chooses index scans when beneficial.

Indexes improve read speed but add overhead on writes.

Use index scans for queries filtering on indexed columns.