EXPLAIN ANALYZE for actual execution in PostgreSQL - Mini Project: Build & Apply

Using EXPLAIN ANALYZE to Understand Query Execution in PostgreSQL

📖 Scenario: You are working with a small bookstore database. You want to understand how PostgreSQL executes your queries to optimize performance.

🎯 Goal: Learn how to use EXPLAIN ANALYZE to see the actual execution plan and timing of a SQL query in PostgreSQL.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert sample data into the books table

Write a simple SELECT query to retrieve books priced above a certain value

Use EXPLAIN ANALYZE to get the actual execution plan and timing for the query

💡 Why This Matters

🌍 Real World

Database administrators and developers use EXPLAIN ANALYZE to understand and improve query performance in real applications.

💼 Career

Knowing how to analyze query execution plans is essential for roles like database developer, data engineer, and backend developer.

1

Create the books table

Write a SQL statement to create a table called books with columns id as integer primary key, title as text, and price as numeric.

PostgreSQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sample data into books

Insert these exact rows into the books table: (1, 'Learn SQL', 25.50), (2, 'PostgreSQL Guide', 30.00), and (3, 'Database Basics', 20.00).

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);
-- Your code here to insert sample data

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Write a SELECT query to find books priced above 22

Write a SQL query to select all columns from books where price is greater than 22.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 25.50),
(2, 'PostgreSQL Guide', 30.00),
(3, 'Database Basics', 20.00);

-- Your code here to select books priced above 22

Need a hint?

Use SELECT * FROM books WHERE price > 22.

4

Use EXPLAIN ANALYZE to see actual execution

Prefix the previous SELECT query with EXPLAIN ANALYZE to get the actual execution plan and timing.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 25.50),
(2, 'PostgreSQL Guide', 30.00),
(3, 'Database Basics', 20.00);

-- Your code here to use EXPLAIN ANALYZE with the SELECT query

Need a hint?

Just add EXPLAIN ANALYZE before the SELECT query.