Common query optimization patterns in PostgreSQL - Mini Project: Build & Apply

Common Query Optimization Patterns in PostgreSQL

📖 Scenario: You are working as a junior database analyst for a small online bookstore. The database has a table called books that stores information about each book, including its id, title, author, price, and stock. You want to learn how to write queries that run faster by using common query optimization patterns in PostgreSQL.

🎯 Goal: Build a series of SQL queries that demonstrate common query optimization patterns such as using indexes, filtering early, and avoiding unnecessary columns in the SELECT clause.

📋 What You'll Learn

Create a books table with specified columns

Add an index on the author column

Write a query that selects only necessary columns with a WHERE filter

Write a query that uses an aggregate function efficiently

💡 Why This Matters

🌍 Real World

Optimizing queries is essential for fast data retrieval in real-world applications like online bookstores, where users search for books by author or price.

💼 Career

Database administrators and developers use these patterns daily to improve application performance and reduce server load.

1

Create the books table

Create a table called books with columns id (integer primary key), title (text), author (text), price (numeric), and stock (integer).

PostgreSQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types. Use SERIAL for auto-incrementing id.

2

Add an index on the author column

Create an index called idx_author on the author column of the books table to speed up searches by author.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    price NUMERIC NOT NULL,
    stock INTEGER NOT NULL
);
-- Your code here to create index idx_author

Need a hint?

Use CREATE INDEX with the index name and specify the table and column.

3

Write a query selecting only needed columns with a WHERE filter

Write a SELECT query that retrieves only the title and price columns from books where the author is 'J.K. Rowling'.

PostgreSQL

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

CREATE INDEX idx_author ON books(author);

-- Your code here to select title and price where author is 'J.K. Rowling'

Need a hint?

Use SELECT with only the needed columns and a WHERE clause to filter by author.

4

Write an efficient aggregate query

Write a query that calculates the total stock of all books by 'J.K. Rowling' using the SUM aggregate function on the stock column with a WHERE filter.

PostgreSQL

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

CREATE INDEX idx_author ON books(author);

SELECT title, price FROM books WHERE author = 'J.K. Rowling';

-- Your code here to sum stock for books by 'J.K. Rowling'

Need a hint?

Use SELECT SUM(stock) with a WHERE clause to filter by author.