How PostgreSQL processes a query (parser, planner, executor) - Try It Yourself

Understanding How PostgreSQL Processes a Query

📖 Scenario: You are a database administrator learning how PostgreSQL handles SQL queries behind the scenes. Understanding this helps you optimize queries and troubleshoot issues.

🎯 Goal: Build a simple demonstration of how PostgreSQL processes a query by simulating the three main stages: parsing, planning, and execution using SQL commands and explanations.

📋 What You'll Learn

Create a simple table with exact columns and data

Write a query string variable to simulate input

Show how parsing works by checking syntax

Show how planning works by explaining the query plan

Show how execution works by running the query and getting results

💡 Why This Matters

🌍 Real World

Understanding query processing helps database users write efficient queries and troubleshoot performance issues.

💼 Career

Database administrators and developers use query plans to optimize database performance and ensure fast data retrieval.

1

Create a table with sample data

Create a table called employees with columns id (integer), name (text), and salary (integer). Insert exactly these three rows: (1, 'Alice', 50000), (2, 'Bob', 60000), (3, 'Charlie', 55000).

PostgreSQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the query string to process

Create a variable called query and set it to the exact string SELECT name, salary FROM employees WHERE salary > 55000; to simulate the SQL query input.

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT, salary INTEGER);
INSERT INTO employees VALUES (1, 'Alice', 50000), (2, 'Bob', 60000), (3, 'Charlie', 55000);

-- Define the query string variable
-- Your code here

Need a hint?

Use \set to create a variable in psql with the exact query string.

3

Show the query plan (planning stage)

Use the exact command EXPLAIN SELECT name, salary FROM employees WHERE salary > 55000; to display the query plan that PostgreSQL creates during the planning stage.

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT, salary INTEGER);
INSERT INTO employees VALUES (1, 'Alice', 50000), (2, 'Bob', 60000), (3, 'Charlie', 55000);

\set query 'SELECT name, salary FROM employees WHERE salary > 55000;'

-- Use EXPLAIN to show the query plan
-- Your code here

Need a hint?

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

4

Execute the query to get results

Run the exact query SELECT name, salary FROM employees WHERE salary > 55000; to execute the query and retrieve the matching rows.

PostgreSQL

CREATE TABLE employees (id INTEGER, name TEXT, salary INTEGER);
INSERT INTO employees VALUES (1, 'Alice', 50000), (2, 'Bob', 60000), (3, 'Charlie', 55000);

\set query 'SELECT name, salary FROM employees WHERE salary > 55000;'

EXPLAIN SELECT name, salary FROM employees WHERE salary > 55000;

-- Execute the query to get the results
-- Your code here

Need a hint?

Run the query exactly as given to see the final results from the executor stage.