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

Need a 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

Need a 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

Need a 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

Need a hint?

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