Partial indexes with WHERE clause in PostgreSQL - Mini Project: Build & Apply

Creating Partial Indexes with WHERE Clause in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for an online bookstore. The books table contains information about all books, including their id, title, author, price, and stock status.To improve query performance for books that are currently in stock, you want to create a partial index that only indexes rows where stock is greater than zero.

🎯 Goal: Build a partial index on the books table that indexes only the books with stock > 0 using the WHERE clause.

📋 What You'll Learn

Create a books table with columns id, title, author, price, and stock.

Insert sample data into the books table with some books having stock greater than zero and some with zero stock.

Create a partial index on the books table that indexes only rows where stock > 0.

Verify the partial index creation with the correct WHERE clause.

💡 Why This Matters

🌍 Real World

Partial indexes help speed up queries by indexing only relevant rows, saving space and improving performance in large databases.

💼 Career

Database administrators and backend developers use partial indexes to optimize query speed and resource usage in production systems.

1

Create the books table and insert sample data

Create a table called books with columns id (integer primary key), title (text), author (text), price (numeric), and stock (integer). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3), (2, '1984', 'George Orwell', 8.99, 0), (3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5).

PostgreSQL

-- Create the books table and insert sample data
-- Your code here

Hint

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

2

Define the partial index condition

Create a variable or note the condition stock > 0 that will be used in the WHERE clause of the partial index.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Define the partial index condition
-- Your code here

Hint

Just write the condition stock > 0 as a comment or variable for clarity.

3

Create the partial index on books for in-stock books

Write a SQL statement to create an index named idx_books_in_stock on the books table for the stock column, but only for rows where stock > 0 using the WHERE clause.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Partial index condition
-- stock > 0

-- Create the partial index below
-- Your code here

Hint

Use CREATE INDEX index_name ON table(column) WHERE condition syntax.

4

Verify the partial index creation

Write a SQL query to list all indexes on the books table and confirm that the index idx_books_in_stock exists with the WHERE stock > 0 condition.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    stock INTEGER
);

INSERT INTO books (id, title, author, price, stock) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 3),
(2, '1984', 'George Orwell', 8.99, 0),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.99, 5);

-- Partial index condition
-- stock > 0

CREATE INDEX idx_books_in_stock ON books(stock) WHERE stock > 0;

-- Verify the partial index below
-- Your code here

Hint

Use the pg_indexes system catalog to check indexes on a table.

Practice

1. What is the main purpose of a partial index with a WHERE clause in PostgreSQL?

easy

A. To index only rows that meet a specific condition

B. To index all rows in the table regardless of condition

C. To create a backup of the table

D. To delete rows that do not meet the condition

Solution

1. Step 1: Understand partial index concept

   A partial index indexes only a subset of rows based on a condition.

2. Step 2: Role of WHERE clause

   The WHERE clause defines which rows to include in the index.

3. Final Answer:

   To index only rows that meet a specific condition -> Option A

4. Quick Check:

   Partial index = conditional indexing [OK]

Hint: Partial indexes use WHERE to limit indexed rows [OK]

Common Mistakes:

• Thinking partial indexes index all rows
• Confusing partial index with table backup
• Assuming partial index deletes rows

2. Which of the following is the correct syntax to create a partial index on a table orders for rows where status = 'pending'?

easy

A. CREATE INDEX idx_pending ON orders (status) WHERE status = 'pending';

B. CREATE INDEX idx_pending ON orders WHERE status = 'pending';

C. CREATE INDEX idx_pending ON orders WHERE status = 'pending' (status);

D. CREATE INDEX idx_pending ON orders (status) WHERE status == 'pending';

Solution

1. Step 1: Check index creation syntax

   CREATE INDEX requires index name, table, columns, and optional WHERE clause.

2. Step 2: Validate WHERE clause and operators

   Use single equals (=) for comparison, and specify columns to index.

3. Final Answer:

   CREATE INDEX idx_pending ON orders (status) WHERE status = 'pending'; -> Option A

4. Quick Check:

   Correct syntax includes columns and WHERE with = [OK]

Hint: Include columns before WHERE; use single = for condition [OK]

Common Mistakes:

• Omitting columns in index definition
• Using double equals (==) instead of single =
• Placing WHERE before columns

3. Given the partial index:
CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;
What will be the result of this query?
SELECT * FROM users WHERE active = true AND last_login > '2024-01-01';

medium

A. The query will return no rows because partial indexes exclude all rows

B. The query will ignore the partial index and perform a full table scan

C. The query will cause a syntax error due to the partial index

D. The query will use the partial index and return matching rows quickly

Solution

1. Step 1: Understand partial index condition

   The index covers rows where active = true, indexing last_login.

2. Step 2: Analyze query filter

   The query filters on active = true and last_login > '2024-01-01', matching the index condition.

3. Final Answer:

   The query will use the partial index and return matching rows quickly -> Option D

4. Quick Check:

   Query matches partial index condition = uses index [OK]

Hint: Query filters must match partial index WHERE to use it [OK]

Common Mistakes:

• Assuming partial index causes syntax errors
• Thinking partial index excludes all rows
• Believing query ignores partial index if condition partially matches

4. You created a partial index:
CREATE INDEX idx_recent_orders ON orders (order_date) WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
But queries filtering order_date > CURRENT_DATE - INTERVAL '30 days' are not using the index. What is the likely problem?

medium

A. Partial indexes cannot use date intervals

B. The index is missing the column order_date

C. The partial index condition uses a non-immutable function, so it cannot be used

D. The WHERE clause in the index is invalid syntax

Solution

1. Step 1: Check partial index WHERE clause

   The WHERE clause uses CURRENT_DATE, which is non-immutable (changes daily).

2. Step 2: Understand index usage limitation

   PostgreSQL partial indexes require immutable conditions to be used by queries.

3. Final Answer:

   The partial index condition uses a non-immutable function, so it cannot be used -> Option C

4. Quick Check:

   Non-immutable functions block partial index usage [OK]

Hint: Partial index WHERE must use immutable expressions [OK]

Common Mistakes:

• Assuming syntax error causes issue
• Thinking partial indexes can't use date columns
• Ignoring function immutability rules

5. You want to speed up queries on a products table filtering only active products with price < 100. Which partial index is best to create?

hard

A. CREATE INDEX idx_active_price ON products (price);

B. CREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100;

C. CREATE INDEX idx_active_price ON products (price) WHERE active = true OR price < 100;

D. CREATE INDEX idx_active_price ON products WHERE active = true AND price < 100;

Solution

1. Step 1: Define index condition matching query filters

   The query filters active = true AND price < 100, so index WHERE must match both.

2. Step 2: Check syntax and columns

   Index must specify columns (price) and use AND in WHERE clause for correct filtering.

3. Final Answer:

   CREATE INDEX idx_active_price ON products (price) WHERE active = true AND price < 100; -> Option B

4. Quick Check:

   Partial index WHERE matches query filters exactly [OK]

Hint: Use AND in WHERE to match all query conditions [OK]

Common Mistakes:

• Using OR instead of AND in WHERE clause
• Omitting columns in index definition
• Trying to create index without columns