Sequential scan vs index scan in PostgreSQL - Hands-On Comparison

Understanding Sequential Scan vs Index Scan in PostgreSQL

📖 Scenario: You are working with a small bookstore database. You want to understand how PostgreSQL finds books by their titles. Sometimes it looks through every book one by one (sequential scan), and sometimes it uses a shortcut (index scan) to find books faster.

🎯 Goal: Build a simple table of books, add an index on the title column, and write queries that show the difference between sequential scan and index scan.

📋 What You'll Learn

Create a table named books with columns id (integer) and title (text).

Insert exactly 5 rows into the books table with given titles.

Create an index on the title column named idx_books_title.

Write a query to select books with title 'The Hobbit' using an index scan.

Write a query to select books with title 'Unknown Book' to show a sequential scan.

💡 Why This Matters

🌍 Real World

Indexes help databases find data quickly, just like an index in a book helps you find a topic without reading every page.

💼 Career

Understanding how to use indexes and how queries run efficiently is important for database administrators and developers to optimize application performance.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Then insert these 5 rows exactly: (1, 'The Hobbit'), (2, '1984'), (3, 'Brave New World'), (4, 'The Catcher in the Rye'), (5, 'To Kill a Mockingbird').

PostgreSQL

-- Create the books table and insert 5 rows
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Create an index on the title column

Create an index named idx_books_title on the title column of the books table to speed up searches by title.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Brave New World'),
(4, 'The Catcher in the Rye'),
(5, 'To Kill a Mockingbird');
-- Create index idx_books_title on title
-- Your code here

Hint

Use CREATE INDEX index_name ON table_name(column_name);

3

Write a query to find 'The Hobbit' using the index

Write a SELECT query to get all columns from books where title is 'The Hobbit'. This query will use the index you created.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Brave New World'),
(4, 'The Catcher in the Rye'),
(5, 'To Kill a Mockingbird');
CREATE INDEX idx_books_title ON books(title);
-- Write SELECT query to find 'The Hobbit'
-- Your code here

Hint

Use SELECT * FROM books WHERE title = 'The Hobbit';

4

Write a query to find a non-existing book to show sequential scan

Write a SELECT query to get all columns from books where title is 'Unknown Book'. This query will cause PostgreSQL to do a sequential scan because no matching index entry exists.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Brave New World'),
(4, 'The Catcher in the Rye'),
(5, 'To Kill a Mockingbird');
CREATE INDEX idx_books_title ON books(title);
SELECT * FROM books WHERE title = 'The Hobbit';
-- Write SELECT query to find 'Unknown Book'
-- Your code here

Hint

Use SELECT * FROM books WHERE title = 'Unknown Book';

Practice

1. What does a sequential scan do in PostgreSQL?

easy

A. Reads only the first 10 rows of a table

B. Uses an index to find specific rows quickly

C. Deletes rows based on a condition

D. Reads every row in the table one by one

Solution

1. Step 1: Understand sequential scan behavior

   A sequential scan reads all rows in the table from start to end without using any index.

2. Step 2: Compare with other options

   Using an index is an index scan, reading only first 10 rows or deleting rows are unrelated actions.

3. Final Answer:

   Reads every row in the table one by one -> Option D

4. Quick Check:

   Sequential scan = full table read [OK]

Hint: Sequential scan reads all rows, no index used [OK]

Common Mistakes:

• Confusing sequential scan with index scan
• Thinking sequential scan reads only some rows
• Assuming sequential scan deletes rows

2. Which of the following is the correct way to see if PostgreSQL uses an index scan or sequential scan?

easy

A. SELECT * FROM table WHERE id = 1;

B. EXPLAIN SELECT * FROM table WHERE id = 1;

C. CREATE INDEX ON table(id);

D. DROP INDEX index_name;

Solution

1. Step 1: Identify command to check query plan

   The EXPLAIN command shows how PostgreSQL executes a query, including scan type.

2. Step 2: Eliminate other options

   SELECT runs query but doesn't show plan; CREATE and DROP INDEX modify indexes, not show plans.

3. Final Answer:

   EXPLAIN SELECT * FROM table WHERE id = 1; -> Option B

4. Quick Check:

   EXPLAIN shows scan type [OK]

Hint: Use EXPLAIN before query to see scan type [OK]

Common Mistakes:

• Running SELECT without EXPLAIN to check scan
• Confusing index creation with scan checking
• Using DROP INDEX to check scans

3. Given a table users(id SERIAL PRIMARY KEY, name TEXT) with 1 million rows, which scan is PostgreSQL likely to use for this query?
SELECT * FROM users WHERE id = 500000;

medium

A. Index scan using the primary key index

B. Sequential scan scanning all 1 million rows

C. Bitmap heap scan reading random rows

D. No scan, query will fail

Solution

1. Step 1: Analyze query condition and table size

   The query filters by primary key id, which has an index, and the table is large (1 million rows).

2. Step 2: Determine efficient scan type

   PostgreSQL uses an index scan to quickly find the single matching row instead of scanning all rows.

3. Final Answer:

   Index scan using the primary key index -> Option A

4. Quick Check:

   Selective query on indexed column = index scan [OK]

Hint: Selective query on indexed column uses index scan [OK]

Common Mistakes:

• Assuming sequential scan for large tables with indexed filter
• Confusing bitmap heap scan with index scan
• Thinking query fails without reason

4. You wrote this query:
SELECT * FROM orders WHERE customer_id = 123;
But EXPLAIN shows a sequential scan instead of an index scan. What could be the reason?

medium

A. There is no index on customer_id

B. The table is empty

C. The query syntax is incorrect

D. PostgreSQL always uses sequential scan

Solution

1. Step 1: Check index presence on filter column

   If no index exists on customer_id, PostgreSQL must scan all rows sequentially.

2. Step 2: Evaluate other options

   Empty table would still show scan but no rows; syntax is correct; PostgreSQL chooses scan type based on indexes.

3. Final Answer:

   There is no index on customer_id -> Option A

4. Quick Check:

   No index on filter column = sequential scan [OK]

Hint: No index on filter column causes sequential scan [OK]

Common Mistakes:

• Assuming syntax error causes scan type
• Thinking PostgreSQL always uses sequential scan
• Ignoring missing index as cause

5. You have a large table products with millions of rows and an index on category_id. You run:
SELECT * FROM products WHERE category_id IN (1, 2, 3, 4, 5);
PostgreSQL chooses a sequential scan instead of an index scan. Why might this happen?

hard

A. The index on category_id is corrupted

B. Sequential scan is always faster for any query

C. The query is not selective enough; many rows match

D. The IN clause is invalid syntax

Solution

1. Step 1: Understand query selectivity

   The query matches many rows because it filters on multiple categories, reducing selectivity.

2. Step 2: Explain PostgreSQL scan choice

   When many rows match, PostgreSQL prefers sequential scan as it can be faster than many index lookups.

3. Final Answer:

   The query is not selective enough; many rows match -> Option C

4. Quick Check:

   Low selectivity = sequential scan preferred [OK]

Hint: Low selectivity queries often use sequential scan [OK]

Common Mistakes:

• Assuming index corruption without evidence
• Thinking sequential scan is always slower
• Believing IN clause is invalid syntax