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

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

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

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

Hint

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

Practice

1. What is the main purpose of using EXPLAIN ANALYZE in PostgreSQL?

easy

A. To create a new database

B. To delete data from a table

C. To show how a query runs and how long each step takes

D. To backup the database

Solution

1. Step 1: Understand the function of EXPLAIN ANALYZE

   It runs the query and shows the execution plan with timing details.

2. Step 2: Compare options with this function

   Only To show how a query runs and how long each step takes describes showing query steps and timing, others are unrelated.

3. Final Answer:

   To show how a query runs and how long each step takes -> Option C

4. Quick Check:

   EXPLAIN ANALYZE = Query profiling [OK]

Hint: EXPLAIN ANALYZE = see query steps and time [OK]

Common Mistakes:

• Thinking EXPLAIN ANALYZE modifies data
• Confusing EXPLAIN ANALYZE with backup commands
• Assuming it creates or deletes databases

2. Which of the following is the correct syntax to run EXPLAIN ANALYZE on a query that selects all rows from a table named users?

easy

A. EXPLAIN ANALYZE SELECT * FROM users;

B. ANALYZE EXPLAIN SELECT * FROM users;

C. EXPLAIN SELECT * FROM users ANALYZE;

D. SELECT EXPLAIN ANALYZE * FROM users;

Solution

1. Step 1: Recall the correct order of EXPLAIN ANALYZE

   The correct syntax is EXPLAIN ANALYZE followed by the query.

2. Step 2: Check each option's syntax

   Only EXPLAIN ANALYZE SELECT * FROM users; correctly places EXPLAIN ANALYZE before the SELECT statement.

3. Final Answer:

   EXPLAIN ANALYZE SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN ANALYZE + query = EXPLAIN ANALYZE SELECT * FROM users; [OK]

Hint: EXPLAIN ANALYZE always comes before the query [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE keywords
• Placing ANALYZE after the query
• Trying to SELECT EXPLAIN ANALYZE as a column

3. Given the output snippet from EXPLAIN ANALYZE for a query:

Seq Scan on products  (cost=0.00..35.50 rows=255 width=12) (actual time=0.010..0.200 rows=255 loops=1)

What does Seq Scan mean in this context?

medium

A. The query used an index to find rows quickly

B. The query used a sequential scan reading all rows in the table

C. The query failed to execute

D. The query only scanned the first 10 rows

Solution

1. Step 1: Understand 'Seq Scan' in EXPLAIN output

   'Seq Scan' means scanning the whole table row by row without using an index.

2. Step 2: Match this meaning to options

   The query used a sequential scan reading all rows in the table correctly describes a sequential scan reading all rows; others are incorrect.

3. Final Answer:

   The query used a sequential scan reading all rows in the table -> Option B

4. Quick Check:

   Seq Scan = full table scan [OK]

Hint: Seq Scan means full table read, no index used [OK]

Common Mistakes:

• Thinking Seq Scan uses an index
• Assuming Seq Scan means query error
• Believing Seq Scan reads only part of table

4. You run EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 10; but get an error: ERROR: syntax error at or near "ANALYZE". What is the likely cause?

medium

A. You need to run ANALYZE separately before EXPLAIN

B. The table orders does not exist

C. You forgot a semicolon at the end

D. You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE

Solution

1. Step 1: Check the error message and syntax

   The error points to a syntax problem near ANALYZE, likely wrong keyword order.

2. Step 2: Identify correct keyword order

   The correct command is EXPLAIN ANALYZE, not ANALYZE EXPLAIN, so You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE fits.

3. Final Answer:

   You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE -> Option D

4. Quick Check:

   EXPLAIN must come before ANALYZE [OK]

Hint: EXPLAIN always before ANALYZE in command [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE keywords
• Assuming missing semicolon causes this error
• Thinking ANALYZE must run separately first

5. You want to optimize a slow query. After running EXPLAIN ANALYZE, you see a nested loop join with very high actual time. What should you do to improve performance?

hard

A. Create indexes on the join columns to speed up lookups

B. Remove all indexes to force sequential scans

C. Rewrite the query without a WHERE clause

D. Increase the number of loops in the query

Solution

1. Step 1: Understand nested loop join with high time

   High time in nested loops often means many repeated scans; indexes can help reduce this.

2. Step 2: Choose the best optimization

   Creating indexes on join columns helps the database find matching rows faster, improving performance.

3. Final Answer:

   Create indexes on the join columns to speed up lookups -> Option A

4. Quick Check:

   Indexes speed up joins with nested loops [OK]

Hint: Add indexes on join keys to speed nested loops [OK]

Common Mistakes:

• Removing indexes which slows queries
• Removing WHERE clause losing filtering
• Trying to increase loops which is not a setting