Sequential scan vs index scan in PostgreSQL

Introduction

We want to find data quickly in a database. Sequential scan and index scan are two ways to look for data.

When you want to read all rows in a table.

When you want to find a few rows using a search condition.

When the table is small and reading all rows is fast.

When the table is large but you have an index on the searched column.

When you want to understand how PostgreSQL finds data.

Syntax

PostgreSQL

EXPLAIN SELECT * FROM table_name WHERE column_name = value;

This command shows how PostgreSQL plans to find data.

It can show if it uses a sequential scan or an index scan.

Examples

Shows how PostgreSQL searches for employee with id 5.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE id = 5;

Shows how PostgreSQL reads all employees (likely sequential scan).

PostgreSQL

EXPLAIN SELECT * FROM employees;

Creates an index on the name column to speed up searches by name.

PostgreSQL

CREATE INDEX idx_name ON employees(name);

Shows if PostgreSQL uses the index on name to find Alice.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE name = 'Alice';

Sample Program

This creates a small employees table, adds three rows, and shows how PostgreSQL finds the employee with id 2.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, age INT);
INSERT INTO employees (name, age) VALUES ('Alice', 30), ('Bob', 25), ('Carol', 27);
EXPLAIN SELECT * FROM employees WHERE id = 2;

Important Notes

A sequential scan reads every row one by one. It is simple but can be slow on big tables.

An index scan uses a special data structure to jump directly to matching rows. It is faster for selective queries.

PostgreSQL decides automatically which scan to use based on table size and query.

Summary

Sequential scan reads all rows; good for small tables or full reads.

Index scan uses an index to find rows faster; good for selective queries.

Use EXPLAIN to see which scan PostgreSQL chooses.