-- Find slowest queries SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;


### 2. Analyze with EXPLAIN

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;

-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem

3. Create Index

-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);

-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x

Critical Rules

Always Do ✓

Rule	Why	Example
Index foreign keys	JOINs need indexed columns	`CREATE INDEX idx_orders_user ON orders(user_id)`
Use EXPLAIN ANALYZE before production	Verify query plan is optimal	`EXPLAIN (ANALYZE, BUFFERS) <query>`
Select specific columns	Reduces data transfer 90%	`SELECT id, name FROM users` not `SELECT *`
Add LIMIT to unbounded queries	Prevents memory exhaustion	`SELECT * FROM logs ORDER BY id LIMIT 100`
Use prepared statements	Prevents SQL injection + faster	`db.query('SELECT * FROM users WHERE id = $1', [id])`
Run ANALYZE after bulk operations	Updates query planner statistics	`ANALYZE table_name`
Monitor pg_stat_statements	Track query performance over time	Review daily for regressions
Use connection pooling	Reduces connection overhead 10x	`new Pool({ max: 20 })`

Never Do ✗

Anti-Pattern	Problem	Fix
SELECT * in production	Fetches unnecessary columns	Select specific columns only
Leading wildcard LIKE '%term%'	Cannot use index	Use full-text search instead
String concatenation for SQL	SQL injection vulnerability	Use parameterized queries
No LIMIT on large results	Memory exhaustion	Always add LIMIT + pagination
N+1 queries in loops	Network latency × N	Use JOIN or batch loading
Ignoring EXPLAIN output	Deploy slow queries to production	Always EXPLAIN before deploy
Multiple INSERTs in loop	Slow bulk operations	Use batch INSERT with multiple VALUES
OFFSET for pagination	O(n) time, scans skipped rows	Use cursor-based pagination

Top 7 Critical Errors

1. Sequential Scan on Large Table

Symptom: Seq Scan on orders (cost=0.00..150000.00) on 1M+ rows Cause: No index on filter column Fix: CREATE INDEX idx_orders_column ON orders(column) Impact: 10-100x faster

2. Missing Index on Foreign Key

Symptom: Slow JOINs (5+ seconds) Cause: Foreign key columns not indexed Fix: CREATE INDEX idx_orders_user_id ON orders(user_id) Impact: 50-500x faster JOINs

3. N+1 Query Problem

Symptom: 1 + N queries for N records Cause: ORM lazy loading in loop Fix: Use JOIN or eager loading: SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id Impact: N queries → 1 query

4. Leading Wildcard LIKE

Symptom: WHERE name LIKE '%search%' sequential scan Cause: Index cannot match middle of string Fix: Use full-text search (GIN index) or trigrams Impact: 100-1000x faster

5. SELECT * in Production

Symptom: High network traffic, slow responses Cause: Fetches all 50 columns instead of needed 3 Fix: SELECT id, name, email (explicit column list) Impact: 90% less data transfer

6. Missing LIMIT on Large Results

Symptom: Server out of memory, query timeout Cause: Attempting to return 5M rows Fix: SELECT * FROM logs WHERE ... LIMIT 100 + pagination Impact: Constant memory usage

7. Stale Statistics After Bulk Load

Symptom: Wrong query plan chosen despite index Cause: PostgreSQL statistics outdated Fix: ANALYZE table_name after bulk operations Impact: Correct query plan selection

See references/error-catalog.md for all 12 errors with detailed solutions.

Common Patterns Summary

Pattern	Use Case	Example	Performance
B-Tree Index	Equality, range, sort queries	`CREATE INDEX idx ON t(col)`	Default, best general purpose
Composite Index	Multi-column WHERE clauses	`CREATE INDEX idx ON t(c1, c2)`	5-50x faster than single index
Covering Index	Include all query columns	`CREATE INDEX idx ON t(c1) INCLUDE (c2)`	2-10x faster (no heap fetch)
Partial Index	Filter subset of rows	`CREATE INDEX idx ON t(c) WHERE status='active'`	50-90% smaller index
JOIN Rewrite	Replace IN subquery	`INNER JOIN users u ON o.user_id = u.id`	5-20x faster than subquery
Batch INSERT	Bulk data loading	`INSERT INTO t VALUES (..),(..)`	10-100x faster than individual
Cursor Pagination	Large offset performance	`WHERE id > last_id LIMIT 100`	Constant time vs O(n)

Configuration Summary

PostgreSQL Config

-- Increase work_mem for complex queries (reloadable - no restart needed)
SET work_mem = '256MB';

-- Increase shared_buffers for better caching (25% of RAM)
ALTER SYSTEM SET shared_buffers = '8GB';

-- IMPORTANT: shared_buffers requires a full PostgreSQL server restart!
-- This setting is NOT reloadable via pg_reload_conf()
--
-- To apply shared_buffers change:
-- 1. Stop PostgreSQL:   sudo systemctl stop postgresql
-- 2. Start PostgreSQL:  sudo systemctl start postgresql
-- OR use:              sudo systemctl restart postgresql
--
-- Verify the change took effect:
-- SHOW shared_buffers;

-- Enable auto-vacuum (reloadable - can use pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;

-- Reload config (ONLY works for parameters that don't require restart)
-- This will NOT reload shared_buffers - restart required for that!
SELECT pg_reload_conf();

MySQL Config

# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G  # 70% of RAM
max_connections = 500
slow_query_log = 1
long_query_time = 1

When to Load References

Performance Analysis:

Load references/explain-analysis.md when: Reading EXPLAIN output, understanding query plans, analyzing buffer statistics, comparing PostgreSQL vs MySQL EXPLAIN
Load references/performance-monitoring.md when: Setting up monitoring, tracking slow queries over time, monitoring cache hit ratios, identifying bloated tables

Index Optimization:

Load references/index-strategies.md when: Choosing index type (B-Tree, GIN, GiST, Hash), creating composite indexes, determining column order, using covering indexes, implementing partial indexes, monitoring index usage

Query Optimization:

Load references/query-rewrites.md when: Rewriting slow queries, converting subqueries to JOINs, eliminating N+1 queries, implementing pagination, optimizing LIKE queries, batching operations

Systematic Process:

Load references/optimization-workflow.md when: Following step-by-step optimization process, creating optimization hypothesis, measuring improvements, monitoring long-term performance

Error Resolution:

Load references/error-catalog.md when: Debugging specific errors (sequential scans, missing indexes, N+1 queries, etc.), understanding root causes, implementing verified solutions

Using Bundled Resources

Templates (Copy-Paste SQL)

# EXPLAIN query templates
templates/explain-query.sql

# Index creation patterns
templates/index-examples.sql

# Query rewrite examples
templates/query-rewrites.sql

# Monitoring queries
templates/monitoring-queries.sql

References (Deep Dives)

# Comprehensive guides
references/error-catalog.md              # All 12 errors + solutions
references/explain-analysis.md           # Reading query plans
references/index-strategies.md           # Index types & selection
references/query-rewrites.md             # Before/after optimizations
references/performance-monitoring.md     # Long-term monitoring
references/optimization-workflow.md      # Systematic process

Dependencies

PostgreSQL Extensions:

pg_stat_statements - Query performance tracking (built-in)
pg_trgm - Trigram similarity search (optional, for fuzzy matching)

MySQL:

performance_schema - Performance monitoring (enabled by default in 8.0+)

No additional dependencies required.

Known Issues Prevention

Issue	Symptom	Prevention
Sequential scans	Seq Scan on 1M+ rows	Index filter columns before production
Missing FK indexes	Slow JOINs	Always index foreign keys
N+1 queries	1+N database calls	Use JOIN or eager loading
Leading wildcards	LIKE '%x%' slow	Use full-text search (GIN)
SELECT * bloat	High network traffic	Select specific columns
No LIMIT	Memory exhaustion	Always LIMIT unbounded queries
Stale statistics	Wrong query plans	ANALYZE after bulk operations
Wrong index order	Index exists but not used	Match query pattern
Missing composite	Multiple WHERE slow	Create composite index
No connection pool	High latency	Implement pooling (20-50 connections)
SQL injection	Security vulnerability	Use prepared statements only
Temp spills	Disk I/O on sorts	Increase work_mem

Complete Setup Checklist

Production Deployment:

Production Example

Before Optimization:

-- Query: Fetch user orders
SELECT * FROM orders WHERE user_id = 123;

-- Performance:
-- Execution time: 2500ms
-- Seq Scan on orders (1M rows scanned)
-- Network: 50MB transferred
-- No index on user_id

After Optimization:

-- Add index
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Optimize query
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;

-- Performance:
-- Execution time: 12ms (208x faster!)
-- Index Scan using idx_orders_user_id (100 rows)
-- Network: 50KB transferred (1000x less!)
-- Covering index with INCLUDE

Result: 208x faster execution, 1000x less data transfer

For comprehensive optimization guidance, error resolution, and production patterns, load the appropriate reference files listed in "When to Load References" above.

sql-query-optimization

SQL Query Optimization

Quick Start (10 Minutes)

1. Identify Slow Query