Bitmap index scan behavior in PostgreSQL - Mini Project: Build & Apply

Understanding Bitmap Index Scan Behavior in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database for a small online bookstore. The database has a table called books that stores information about each book, including its id, title, author, and genre. You want to understand how PostgreSQL uses bitmap index scans to efficiently find books by genre.

🎯 Goal: Build a simple PostgreSQL setup with a books table and an index on the genre column. Then, write a query that uses the bitmap index scan to find all books in a specific genre.

📋 What You'll Learn

Create a books table with columns id, title, author, and genre

Insert 5 specific rows into the books table

Create a B-tree index on the genre column

Write a SELECT query filtering by genre to trigger a bitmap index scan

💡 Why This Matters

🌍 Real World

Bitmap index scans help databases quickly find rows matching conditions without scanning the whole table, improving performance in real applications like online stores or libraries.

💼 Career

Understanding bitmap index scans is useful for database administrators and developers to optimize queries and improve application speed.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), author (text), and genre (text). Then insert these exact rows: (1, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy'), (2, '1984', 'George Orwell', 'Dystopian'), (3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'), (4, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'), (5, 'Brave New World', 'Aldous Huxley', 'Dystopian').

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as listed.

2

Create a B-tree index on the genre column

Create a B-tree index named idx_books_genre on the genre column of the books table.

PostgreSQL

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

INSERT INTO books (id, title, author, genre) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy'),
(2, '1984', 'George Orwell', 'Dystopian'),
(3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'),
(4, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian');

-- Create the B-tree index on genre
-- Your code here

Need a hint?

Use CREATE INDEX with the name idx_books_genre on the genre column.

3

Write a SELECT query filtering by genre

Write a SELECT query to get all columns from books where genre is exactly 'Dystopian'. Use EXPLAIN before the query to see the query plan that includes a bitmap index scan.

PostgreSQL

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

INSERT INTO books (id, title, author, genre) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy'),
(2, '1984', 'George Orwell', 'Dystopian'),
(3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'),
(4, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian');

CREATE INDEX idx_books_genre ON books USING btree (genre);

-- Write EXPLAIN SELECT query filtering genre = 'Dystopian'
-- Your code here

Need a hint?

Use EXPLAIN before the SELECT statement to see the query plan.

4

Complete by running the SELECT query to get the results

Write a SELECT query to get all columns from books where genre is 'Dystopian' without EXPLAIN, to retrieve the actual rows.

PostgreSQL

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

INSERT INTO books (id, title, author, genre) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy'),
(2, '1984', 'George Orwell', 'Dystopian'),
(3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'),
(4, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'),
(5, 'Brave New World', 'Aldous Huxley', 'Dystopian');

CREATE INDEX idx_books_genre ON books USING btree (genre);

EXPLAIN SELECT * FROM books WHERE genre = 'Dystopian';

-- Write the SELECT query to get all dystopian books
-- Your code here

Need a hint?

Write a simple SELECT query filtering genre to 'Dystopian' without EXPLAIN.