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

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

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

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

Hint

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

Practice

1. What is the main advantage of an index-only scan in PostgreSQL?

easy

A. It reads data only from the index without accessing the main table.

B. It updates the index faster than a normal scan.

C. It locks the table to prevent concurrent writes.

D. It creates a new index automatically during query execution.

Solution

1. Step 1: Understand what an index-only scan does

   An index-only scan uses the index to get all needed data without reading the main table.

2. Step 2: Compare options to this behavior

   Only It reads data only from the index without accessing the main table. describes reading data solely from the index, which is the key benefit.

3. Final Answer:

   It reads data only from the index without accessing the main table. -> Option A

4. Quick Check:

   Index-only scan = reads from index only [OK]

Hint: Index-only scans avoid table reads by using index data only [OK]

Common Mistakes:

• Thinking index-only scans update data
• Assuming they lock tables
• Believing they create indexes automatically

2. Which of the following is a correct condition for PostgreSQL to use an index-only scan?

easy

A. The table has no indexes defined.

B. The index contains all columns needed by the query.

C. The query uses aggregate functions only.

D. The table is empty.

Solution

1. Step 1: Recall index-only scan requirements

   PostgreSQL can use index-only scans only if the index has all columns the query needs.

2. Step 2: Evaluate each option

   The index contains all columns needed by the query. matches the requirement; others do not enable index-only scans.

3. Final Answer:

   The index contains all columns needed by the query. -> Option B

4. Quick Check:

   Index-only scan requires full column coverage in index [OK]

Hint: Index-only scans need all query columns in the index [OK]

Common Mistakes:

• Thinking index-only scans work without indexes
• Assuming aggregates always use index-only scans
• Believing empty tables affect index-only scans

3. Given a table users(id, name, email) with an index on (id, email), which query can use an index-only scan?

medium

A. SELECT id, email FROM users WHERE id = 10;

B. SELECT name FROM users WHERE id = 10;

C. SELECT email FROM users WHERE name = 'Alice';

D. SELECT * FROM users WHERE email = '[e-mail]';

Solution

1. Step 1: Check index columns and query columns

   The index covers columns id and email. Query C requests only id and email.

2. Step 2: Determine if index-only scan is possible

   Query C can use index-only scan because all requested columns are in the index. Others request columns not in the index.

3. Final Answer:

   SELECT id, email FROM users WHERE id = 10; -> Option A

4. Quick Check:

   Query columns ⊆ index columns = index-only scan [OK]

Hint: Index-only scan if query columns are subset of index columns [OK]

Common Mistakes:

• Choosing queries requesting columns not in index
• Ignoring WHERE clause columns
• Assuming * always uses index-only scan

4. You have an index on (username, email) but your query SELECT email FROM users WHERE username = 'bob'; is not using an index-only scan. What could be the reason?

medium

A. The query uses a WHERE clause on username, so index-only scan is impossible.

B. The index does not include the email column.

C. The table's visibility map is not updated, so PostgreSQL must check the table.

D. PostgreSQL never uses index-only scans on text columns.

Solution

1. Step 1: Confirm index covers needed columns

   The index includes username and email, so columns are covered.

2. Step 2: Understand visibility map role

   Index-only scans require the visibility map to confirm tuples are visible without table access. If not updated, PostgreSQL reads the table.

3. Final Answer:

   The table's visibility map is not updated, so PostgreSQL must check the table. -> Option C

4. Quick Check:

   Visibility map must be updated for index-only scan [OK]

Hint: Visibility map must be updated for index-only scans [OK]

Common Mistakes:

• Assuming index missing columns
• Believing WHERE clause blocks index-only scan
• Thinking data type prevents index-only scans

5. You want to optimize a query SELECT id, status FROM orders WHERE status = 'shipped'; for index-only scans. The current index is on (id). What is the best way to enable index-only scans?

hard

A. Add a WHERE clause to filter only 'shipped' status.

B. Create a partial index on (id) where status = 'shipped'.

C. Drop the existing index and rely on sequential scan.

D. Create a new index on (status, id) including both columns.

Solution

1. Step 1: Identify columns needed for index-only scan

   The query selects id and status, so the index must cover both columns.

2. Step 2: Choose index that covers all columns

   Creating an index on (status, id) includes both columns, enabling index-only scans.

3. Final Answer:

   Create a new index on (status, id) including both columns. -> Option D

4. Quick Check:

   Index covering all query columns enables index-only scan [OK]

Hint: Index must cover all selected columns for index-only scan [OK]

Common Mistakes:

• Thinking partial index alone enables index-only scan
• Dropping index reduces performance
• Adding WHERE clause doesn't affect index structure