Index-only scans mental model in PostgreSQL - Mini Project: Build & Apply

Index-only scans mental model

📖 Scenario: You are managing a small bookstore database. You want to quickly find books by their titles without scanning the entire table every time.

🎯 Goal: Build a simple table with book data, create an index on the title column, and write a query that benefits from an index-only scan to quickly retrieve book titles.

📋 What You'll Learn

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

Insert exactly three books with these titles: 'The Hobbit', '1984', and 'Pride and Prejudice'.

Create a B-tree index on the title column named idx_books_title.

Write a SELECT query to retrieve only the title column from books where the title is '1984'.

💡 Why This Matters

🌍 Real World

Index-only scans help databases quickly find data without reading the full table, improving performance for common queries like searching by title.

💼 Career

Understanding how to create indexes and optimize queries is essential for database administrators and backend developers to build fast and efficient applications.

1

Create the books table and insert data

Create a table called books with columns id as an integer primary key and title as text. Then insert these three rows exactly: (1, 'The Hobbit'), (2, '1984'), and (3, 'Pride and Prejudice').

PostgreSQL

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

Need a hint?

Use CREATE TABLE with the specified columns, then INSERT INTO with the exact values.

2

Create an index on the title column

Create a B-tree index named idx_books_title on the title column of the books table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Pride and Prejudice');

-- Create the index idx_books_title on title
-- Your code here

Need a hint?

Use CREATE INDEX idx_books_title ON books USING btree (title);

3

Write a SELECT query to use the index

Write a SELECT query to retrieve only the title column from the books table where the title is exactly '1984'.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Pride and Prejudice');

CREATE INDEX idx_books_title ON books USING btree (title);

-- Write the SELECT query to get title where title = '1984'
-- Your code here

Need a hint?

Use SELECT title FROM books WHERE title = '1984'; to get the matching book title.

4

Enable visibility map for index-only scan

Add the command to run VACUUM on the books table to update the visibility map, which helps PostgreSQL perform index-only scans.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Pride and Prejudice');

CREATE INDEX idx_books_title ON books USING btree (title);

SELECT title FROM books WHERE title = '1984';

-- Run VACUUM on books to enable index-only scans
-- Your code here

Need a hint?

Use VACUUM books; to update the visibility map for index-only scans.