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

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

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'

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'

Hint

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

Practice

1. Which of the following is the best reason to create an index on a column in PostgreSQL?

easy

A. To speed up searches on that column

B. To reduce the size of the database

C. To automatically backup the data

D. To encrypt the data in that column

Solution

1. Step 1: Understand what an index does

   An index helps the database find rows faster by creating a quick lookup structure.

2. Step 2: Match the purpose to the options

   Only speeding up searches matches the purpose of an index; other options are unrelated.

3. Final Answer:

   To speed up searches on that column -> Option A

4. Quick Check:

   Index = Speed up search [OK]

Hint: Indexes speed up searches, not storage or encryption [OK]

Common Mistakes:

• Thinking indexes reduce database size
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to check the query plan in PostgreSQL?

easy

A. EXPLAIN SELECT * FROM users;

B. DESCRIBE SELECT * FROM users;

C. PLAN SELECT * FROM users;

D. SHOW PLAN SELECT * FROM users;

Solution

1. Step 1: Recall the command to view query plans

   PostgreSQL uses EXPLAIN to show how it will run a query.

2. Step 2: Compare options to the correct command

   Only EXPLAIN SELECT * FROM users; is valid syntax for query plans.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN = Query plan check [OK]

Hint: Use EXPLAIN before your query to see the plan [OK]

Common Mistakes:

• Using SHOW PLAN which is invalid
• Trying PLAN or DESCRIBE which are not PostgreSQL commands
• Missing the EXPLAIN keyword

3. Consider the query:
SELECT id, name FROM employees WHERE department = 'Sales';
Which optimization pattern does this query follow?

medium

A. Using a JOIN to combine tables

B. Using ORDER BY to sort results

C. Using a subquery to filter data

D. Selecting only needed columns instead of *

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only 'id' and 'name', not all columns with '*'.

2. Step 2: Identify the optimization pattern

   Selecting only needed columns reduces data transfer and improves speed.

3. Final Answer:

   Selecting only needed columns instead of * -> Option D

4. Quick Check:

   Selective columns = Better performance [OK]

Hint: Avoid SELECT *; pick only columns you need [OK]

Common Mistakes:

• Confusing JOIN usage with column selection
• Thinking ORDER BY is always an optimization
• Assuming subqueries are used here

4. You have this query:
SELECT * FROM orders WHERE order_date = '2023-01-01';
It runs slowly. Which fix will likely improve performance?

medium

A. Change SELECT * to SELECT COUNT(*)

B. Add an index on the order_date column

C. Remove the WHERE clause

D. Use GROUP BY order_date

Solution

1. Step 1: Identify the cause of slowness

   Query filters on order_date but may scan all rows without an index.

2. Step 2: Apply optimization by indexing

   Adding an index on order_date lets PostgreSQL find matching rows faster.

3. Final Answer:

   Add an index on the order_date column -> Option B

4. Quick Check:

   Index on filter column = Faster query [OK]

Hint: Index columns used in WHERE for faster filtering [OK]

Common Mistakes:

• Removing WHERE loses filtering purpose
• Changing SELECT * to COUNT(*) changes result, not speed
• Using GROUP BY without aggregation is incorrect

5. You want to optimize a query that joins two large tables on a column and filters by a date range. Which combination of patterns will best improve performance?

hard

A. Use subqueries instead of JOINs; do not filter by date

B. Select all columns with *; avoid indexes to save space

C. Create indexes on join columns and filter columns; use EXPLAIN to check plan

D. Add ORDER BY on join column; remove WHERE clause

Solution

1. Step 1: Identify key optimization needs

   Joining large tables and filtering by date needs indexes on join and filter columns.

2. Step 2: Use EXPLAIN to verify query plan

   Checking the plan helps confirm indexes are used and query is efficient.

3. Final Answer:

   Create indexes on join columns and filter columns; use EXPLAIN to check plan -> Option C

4. Quick Check:

   Indexes + EXPLAIN = Best optimization [OK]

Hint: Index join and filter columns; verify with EXPLAIN [OK]

Common Mistakes:

• Selecting all columns wastes resources
• Avoiding indexes slows queries
• Removing WHERE loses filtering benefits