Covering indexes with INCLUDE in PostgreSQL - Mini Project: Build & Apply

Creating a Covering Index with INCLUDE in PostgreSQL

📖 Scenario: You work for an online bookstore. The database has a table called books that stores information about each book, including its id, title, author, price, and stock.To speed up queries that search by author and also display the title and price, you want to create a special index called a covering index.

🎯 Goal: Create a covering index on the books table that indexes the author column and includes the title and price columns to make queries faster.

📋 What You'll Learn

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

Add a covering index on the author column.

Include the title and price columns in the index using the INCLUDE clause.

💡 Why This Matters

🌍 Real World

Covering indexes help speed up database queries by including extra columns in the index, reducing the need to access the main table data.

💼 Career

Database administrators and backend developers use covering indexes to optimize query performance in real-world applications.

1

Create the books table

Write a SQL statement to create a table called books with these columns and types: id as integer primary key, title as text, author as text, price as numeric(6,2), and stock as integer.

PostgreSQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE books and define each column with its type. Remember to set id as the primary key.

2

Define the index name

Create a variable called index_name and set it to the string 'idx_books_author' to hold the name of the index you will create.

PostgreSQL

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

-- Your code here to define index_name variable

Need a hint?

Use the PostgreSQL psql command \set to define a variable called index_name with the value 'idx_books_author'.

3

Create the covering index with INCLUDE

Write a SQL statement to create an index named idx_books_author on the author column of the books table. Include the title and price columns in the index using the INCLUDE clause.

PostgreSQL

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

\set index_name 'idx_books_author'

-- Your code here to create the covering index

Need a hint?

Use CREATE INDEX idx_books_author ON books (author) INCLUDE (title, price); to create the covering index.

4

Verify the index creation

Write a SQL query to list all indexes on the books table by selecting from pg_indexes where tablename = 'books'.

PostgreSQL

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

\set index_name 'idx_books_author'

CREATE INDEX idx_books_author ON books (author) INCLUDE (title, price);

-- Your code here to query pg_indexes for books table

Need a hint?

Use SELECT * FROM pg_indexes WHERE tablename = 'books'; to see the indexes on the books table.