query-optimizer

Query Optimizer

Optimize SQL queries for better performance through indexing, rewriting, and analysis.

Quick Start

Use EXPLAIN to analyze queries, add indexes on WHERE/JOIN columns, avoid SELECT *, limit results.

Instructions

Query Analysis with EXPLAIN

Basic EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN ANALYZE (actual execution):

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Key metrics to check:

• Seq Scan (bad) vs Index Scan (good)
• Rows: Estimated vs actual
• Cost: Lower is better
• Execution time

Common Performance Issues

1. Missing Indexes

Problem:

-- Seq Scan on users (cost=0.00..1234.56)
SELECT * FROM users WHERE email = '[email protected]';

Solution:

CREATE INDEX idx_users_email ON users(email);
-- Now: Index Scan using idx_users_email

**2. SELECT ***

Problem:

SELECT * FROM posts;  -- Fetches all columns

Solution:

SELECT id, title, created_at FROM posts;  -- Only needed columns

3. N+1 Queries

Problem:

-- Fetches posts
SELECT * FROM posts;
-- Then for each post:
SELECT * FROM users WHERE id = ?;

Solution:

-- Single query with JOIN
SELECT posts.*, users.name 
FROM posts 
JOIN users ON posts.user_id = users.id;

4. No LIMIT

Problem:

SELECT * FROM posts ORDER BY created_at DESC;  -- Returns all rows

Solution:

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

Indexing Strategies

Single column index:

CREATE INDEX idx_users_email ON users(email);

Composite index (order matters):

-- For: WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

Covering index (includes all needed columns):

-- For: SELECT id, title FROM posts WHERE user_id = ?
CREATE INDEX idx_posts_user_id_title ON posts(user_id) INCLUDE (title);

Partial index (filtered):

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;