Common query optimization patterns in PostgreSQL - Step-by-Step Execution

Concept Flow - Common query optimization patterns

Write Query

↓

Analyze Query Plan

↓

Identify Bottlenecks

↓

Apply Optimization Patterns

↓

Re-run Query Plan

↓

Check Performance Improvement?

No→Adjust or Try Other Patterns

Yes↓

Use Optimized Query

This flow shows how to optimize a query by analyzing its plan, applying patterns, and checking improvements.

Execution Sample

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
-- Add index on customer_id
CREATE INDEX idx_customer_id ON orders(customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This example shows how adding an index on a filter column improves query performance.

Execution Table

Step	Action	Query Plan Detail	Execution Time	Effect
1	Run initial query	Seq Scan on orders (filter customer_id=123)	50 ms	Slow due to full table scan
2	Create index	Index idx_customer_id created	-	Prepares for faster lookup
3	Run query again	Index Scan using idx_customer_id	5 ms	Much faster due to index use
4	Check if performance improved	Execution time reduced from 50 ms to 5 ms	-	Optimization successful

💡 Performance improved after adding index, no further optimization needed

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Execution Time (ms)	-	50	-	5	5
Query Plan Type	-	Seq Scan	-	Index Scan	Index Scan

Key Moments - 2 Insights

Why does adding an index reduce execution time so much?

Because the initial plan used a sequential scan reading all rows (Step 1), but after adding the index (Step 2), the query uses an index scan (Step 3) that quickly finds matching rows without scanning the whole table.

Can adding an index always improve performance?

No, indexes help mainly when filtering or joining on indexed columns. For small tables or queries returning many rows, index overhead might not help. This is why checking the query plan (Step 4) is important.

Visual Quiz - 3 Questions

Look at the execution_table, what was the query plan type at Step 1?

A Bitmap Heap Scan

B Index Scan

C Seq Scan

D Hash Join

💡 Hint

Check the 'Query Plan Detail' column at Step 1 in the execution_table

At which step did the execution time drop significantly?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Execution Time' column in the execution_table and find when it changed from 50 ms to 5 ms

If the index was not created, what would likely happen to the execution time?

A It would remain about 50 ms

B It would increase above 50 ms

C It would stay around 5 ms

D It would drop to 0 ms

💡 Hint

Refer to the execution_table Step 1 and Step 3 to compare execution times with and without index

Concept Snapshot

Common Query Optimization Patterns:
- Analyze query plan with EXPLAIN ANALYZE
- Identify slow operations like sequential scans
- Add indexes on filter/join columns
- Rewrite queries to avoid unnecessary work
- Re-check plan and performance after changes
- Optimization is iterative and context-dependent

Full Transcript

This visual execution shows how to optimize a PostgreSQL query by analyzing its execution plan, identifying bottlenecks like sequential scans, and applying common patterns such as adding indexes. Initially, the query scans the entire orders table, taking 50 milliseconds. After creating an index on the customer_id column, the query uses an index scan, reducing execution time to 5 milliseconds. This demonstrates how indexes speed up data retrieval by avoiding full table scans. However, indexes are not always beneficial, so checking the query plan and performance after changes is essential. Optimization is a step-by-step process of analyzing, applying patterns, and verifying improvements.

Practice

1. Which of the following is the best reason to create an index on a column in PostgreSQL?

easy

A. To speed up searches on that column

B. To reduce the size of the database

C. To automatically backup the data

D. To encrypt the data in that column

Solution

1. Step 1: Understand what an index does

   An index helps the database find rows faster by creating a quick lookup structure.

2. Step 2: Match the purpose to the options

   Only speeding up searches matches the purpose of an index; other options are unrelated.

3. Final Answer:

   To speed up searches on that column -> Option A

4. Quick Check:

   Index = Speed up search [OK]

Hint: Indexes speed up searches, not storage or encryption [OK]

Common Mistakes:

• Thinking indexes reduce database size
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to check the query plan in PostgreSQL?

easy

A. EXPLAIN SELECT * FROM users;

B. DESCRIBE SELECT * FROM users;

C. PLAN SELECT * FROM users;

D. SHOW PLAN SELECT * FROM users;

Solution

1. Step 1: Recall the command to view query plans

   PostgreSQL uses EXPLAIN to show how it will run a query.

2. Step 2: Compare options to the correct command

   Only EXPLAIN SELECT * FROM users; is valid syntax for query plans.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN = Query plan check [OK]

Hint: Use EXPLAIN before your query to see the plan [OK]

Common Mistakes:

• Using SHOW PLAN which is invalid
• Trying PLAN or DESCRIBE which are not PostgreSQL commands
• Missing the EXPLAIN keyword

3. Consider the query:
SELECT id, name FROM employees WHERE department = 'Sales';
Which optimization pattern does this query follow?

medium

A. Using a JOIN to combine tables

B. Using ORDER BY to sort results

C. Using a subquery to filter data

D. Selecting only needed columns instead of *

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only 'id' and 'name', not all columns with '*'.

2. Step 2: Identify the optimization pattern

   Selecting only needed columns reduces data transfer and improves speed.

3. Final Answer:

   Selecting only needed columns instead of * -> Option D

4. Quick Check:

   Selective columns = Better performance [OK]

Hint: Avoid SELECT *; pick only columns you need [OK]

Common Mistakes:

• Confusing JOIN usage with column selection
• Thinking ORDER BY is always an optimization
• Assuming subqueries are used here

4. You have this query:
SELECT * FROM orders WHERE order_date = '2023-01-01';
It runs slowly. Which fix will likely improve performance?

medium

A. Change SELECT * to SELECT COUNT(*)

B. Add an index on the order_date column

C. Remove the WHERE clause

D. Use GROUP BY order_date

Solution

1. Step 1: Identify the cause of slowness

   Query filters on order_date but may scan all rows without an index.

2. Step 2: Apply optimization by indexing

   Adding an index on order_date lets PostgreSQL find matching rows faster.

3. Final Answer:

   Add an index on the order_date column -> Option B

4. Quick Check:

   Index on filter column = Faster query [OK]

Hint: Index columns used in WHERE for faster filtering [OK]

Common Mistakes:

• Removing WHERE loses filtering purpose
• Changing SELECT * to COUNT(*) changes result, not speed
• Using GROUP BY without aggregation is incorrect

5. You want to optimize a query that joins two large tables on a column and filters by a date range. Which combination of patterns will best improve performance?

hard

A. Use subqueries instead of JOINs; do not filter by date

B. Select all columns with *; avoid indexes to save space

C. Create indexes on join columns and filter columns; use EXPLAIN to check plan

D. Add ORDER BY on join column; remove WHERE clause

Solution

1. Step 1: Identify key optimization needs

   Joining large tables and filtering by date needs indexes on join and filter columns.

2. Step 2: Use EXPLAIN to verify query plan

   Checking the plan helps confirm indexes are used and query is efficient.

3. Final Answer:

   Create indexes on join columns and filter columns; use EXPLAIN to check plan -> Option C

4. Quick Check:

   Indexes + EXPLAIN = Best optimization [OK]

Hint: Index join and filter columns; verify with EXPLAIN [OK]

Common Mistakes:

• Selecting all columns wastes resources
• Avoiding indexes slows queries
• Removing WHERE loses filtering benefits