EXPLAIN ANALYZE for query profiling in PostgreSQL - Mini Project: Build & Apply

Using EXPLAIN ANALYZE for Query Profiling in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to understand how PostgreSQL runs your queries to find books by a specific author. This helps you see if your query is fast or slow and where it spends time.

🎯 Goal: Learn how to use EXPLAIN ANALYZE to profile a SQL query and understand its execution details.

📋 What You'll Learn

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

Insert sample data into the books table.

Write a SELECT query to find books by a specific author.

Use EXPLAIN ANALYZE to profile the SELECT query.

💡 Why This Matters

🌍 Real World

Database administrators and developers use EXPLAIN ANALYZE to find slow queries and improve database performance.

💼 Career

Knowing how to profile queries is essential for roles like database developer, data analyst, and backend engineer.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and author as text.

PostgreSQL

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books and define id as SERIAL PRIMARY KEY.

2

Insert sample data into books

Insert these three rows into the books table: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'The Silmarillion', 'J.R.R. Tolkien').

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT
);
-- Insert the three specified rows into books
-- Your code here

Need a hint?

Use a single INSERT INTO books statement with multiple rows.

3

Write a SELECT query to find books by 'J.R.R. Tolkien'

Write a SQL SELECT statement to get all columns from books where the author is exactly 'J.R.R. Tolkien'.

PostgreSQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'The Silmarillion', 'J.R.R. Tolkien');

-- Write a SELECT query to find books by 'J.R.R. Tolkien'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE author = 'J.R.R. Tolkien'.

4

Use EXPLAIN ANALYZE to profile the SELECT query

Add EXPLAIN ANALYZE before the SELECT statement to see how PostgreSQL runs the query and how long it takes.

PostgreSQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'The Silmarillion', 'J.R.R. Tolkien');

-- Add EXPLAIN ANALYZE before the SELECT query
-- Your code here

Need a hint?

Write EXPLAIN ANALYZE SELECT * FROM books WHERE author = 'J.R.R. Tolkien';