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 = 'user@example.com';

EXPLAIN ANALYZE (actual execution):

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

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 = 'user@example.com';

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;

Index on expressions:

CREATE INDEX idx_users_lower_email ON users(LOWER(email)); -- For: WHERE LOWER(email) = 'user@example.com'

Query Rewriting

Use EXISTS instead of IN for large sets:

-- Slow SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- Faster SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM posts p WHERE p.user_id = u.id );

Use JOIN instead of subquery:

-- Slow SELECT * FROM posts WHERE user_id IN ( SELECT id FROM users WHERE is_active = true );

-- Faster SELECT p.* FROM posts p JOIN users u ON p.user_id = u.id WHERE u.is_active = true;

Avoid functions on indexed columns:

-- Bad: Can't use index SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good: Can use index SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Use UNION ALL instead of UNION:

-- Slow: Removes duplicates SELECT id FROM posts UNION SELECT id FROM drafts;

-- Fast: No duplicate removal SELECT id FROM posts UNION ALL SELECT id FROM drafts;

JOIN Optimization

Order matters - filter early:

-- Bad: Large intermediate result SELECT * FROM posts p JOIN users u ON p.user_id = u.id WHERE p.created_at > '2024-01-01';

-- Good: Filter first SELECT * FROM posts p WHERE p.created_at > '2024-01-01' JOIN users u ON p.user_id = u.id;

Use appropriate JOIN type:

-- INNER JOIN: Only matching rows SELECT * FROM posts p INNER JOIN users u ON p.user_id = u.id;

-- LEFT JOIN: All posts, even without user SELECT * FROM posts p LEFT JOIN users u ON p.user_id = u.id;

Index JOIN columns:

CREATE INDEX idx_posts_user_id ON posts(user_id); CREATE INDEX idx_users_id ON users(id); -- Usually PK already indexed

Pagination Optimization

Offset pagination (slow for large offsets):

-- Slow for page 1000 SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20000;

Cursor pagination (faster):

-- First page SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (using last created_at and id) SELECT * FROM posts WHERE (created_at, id) < ('2024-01-01 12:00:00', 12345) ORDER BY created_at DESC, id DESC LIMIT 20;

Aggregation Optimization

Use indexes for GROUP BY:

CREATE INDEX idx_posts_user_id ON posts(user_id);

SELECT user_id, COUNT(*) FROM posts GROUP BY user_id;

Filter before aggregating:

-- Good SELECT user_id, COUNT(*) FROM posts WHERE created_at > '2024-01-01' GROUP BY user_id;

Use HAVING for aggregate filters:

SELECT user_id, COUNT() as post_count FROM posts GROUP BY user_id HAVING COUNT() > 10;

Subquery Optimization

Correlated subqueries (slow):

-- Bad: Runs subquery for each row SELECT * FROM users u WHERE (SELECT COUNT(*) FROM posts WHERE user_id = u.id) > 10;

JOIN instead:

-- Good: Single query SELECT u.* FROM users u JOIN ( SELECT user_id, COUNT() as post_count FROM posts GROUP BY user_id HAVING COUNT() > 10 ) p ON u.id = p.user_id;

Caching Strategies

Materialized views:

CREATE MATERIALIZED VIEW user_post_counts AS SELECT user_id, COUNT(*) as post_count FROM posts GROUP BY user_id;

-- Refresh periodically REFRESH MATERIALIZED VIEW user_post_counts;

Query result caching (application level):

Cache expensive queries

@cache(ttl=300) def get_popular_posts(): return db.query("SELECT * FROM posts ORDER BY views DESC LIMIT 10")

Common Patterns

Full-text Search

PostgreSQL:

-- Add tsvector column ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Update with trigger CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Search SELECT * FROM posts WHERE search_vector @@ to_tsquery('postgresql & optimization');

Use dedicated search engine for complex needs:

• Elasticsearch

• Algolia

• Meilisearch

Batch Operations

Bulk insert:

-- Bad: Multiple inserts INSERT INTO users (name) VALUES ('User 1'); INSERT INTO users (name) VALUES ('User 2');

-- Good: Single insert INSERT INTO users (name) VALUES ('User 1'), ('User 2'), ('User 3');

Bulk update:

-- Use CASE for conditional updates UPDATE posts SET status = CASE WHEN views > 1000 THEN 'popular' WHEN views > 100 THEN 'normal' ELSE 'new' END;

Connection Pooling

Use connection pool

from sqlalchemy import create_engine

engine = create_engine( 'postgresql://user:pass@localhost/db', pool_size=20, max_overflow=10 )

Performance Monitoring

Check slow queries:

-- PostgreSQL: Enable slow query log ALTER DATABASE mydb SET log_min_duration_statement = 1000; -- 1 second

-- View pg_stat_statements SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

Check index usage:

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan = 0 -- Unused indexes ORDER BY pg_relation_size(indexrelid) DESC;

Check table statistics:

SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_user_tables ORDER BY seq_scan DESC;

Best Practices

Always:

• Use EXPLAIN ANALYZE for slow queries

• Index foreign keys

• Index WHERE/JOIN columns

• Limit result sets

• Use prepared statements

Avoid:

• SELECT *

• Functions on indexed columns in WHERE

• Correlated subqueries

• Large OFFSET values

• Over-indexing

Monitor:

• Slow query log

• Index usage

• Table statistics

• Connection pool

Troubleshooting

Query still slow after indexing:

• Check if index is being used (EXPLAIN)

• Verify index column order for composite indexes

• Consider covering index

• Check for stale statistics (ANALYZE table)

Too many indexes:

• Remove unused indexes

• Combine similar indexes

• Monitor write performance

High memory usage:

• Reduce work_mem

• Optimize sort operations

• Use streaming instead of loading all data