EXPLAIN output reading in PostgreSQL - Mini Project: Build & Apply

Understanding EXPLAIN Output in PostgreSQL

📖 Scenario: You are working as a junior database analyst. Your manager wants you to understand how PostgreSQL executes queries to help optimize them. You will learn to read the output of the EXPLAIN command, which shows the query plan.

🎯 Goal: Build a simple table, run a query, and use EXPLAIN to read and understand the query plan output.

📋 What You'll Learn

Create a table named employees with columns id (integer) and name (text).

Insert three specific rows into the employees table.

Write a SELECT query to get the employee with id = 2.

Use EXPLAIN to get the query plan for the SELECT query.

💡 Why This Matters

🌍 Real World

Understanding query plans helps database users and developers optimize queries for faster performance.

💼 Career

Database administrators and developers often use EXPLAIN to troubleshoot slow queries and improve database efficiency.

1

Create the employees table

Create a table called employees with two columns: id as integer and name as text.

PostgreSQL

-- Create the employees table with id and name columns
-- Your code here

Need a hint?

Use CREATE TABLE employees (id INTEGER, name TEXT); to create the table.

2

Insert data into employees

Insert these three rows into the employees table: (1, 'Alice'), (2, 'Bob'), and (3, 'Charlie').

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT);
-- Insert three rows into employees
-- Your code here

Need a hint?

Use INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

3

Write a SELECT query to find employee with id = 2

Write a SELECT query to get all columns from employees where id = 2.

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- Write SELECT query to get employee with id = 2
-- Your code here

Need a hint?

Use SELECT * FROM employees WHERE id = 2; to get the employee with id 2.

4

Use EXPLAIN to read the query plan

Add EXPLAIN before the SELECT query to see how PostgreSQL plans to execute it.

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- Use EXPLAIN before the SELECT query
-- Your code here

Need a hint?

Use EXPLAIN SELECT * FROM employees WHERE id = 2; to see the query plan.