SQL Query Optimization: Writing Efficient Queries

Writing efficient SQL queries is crucial for database performance. A poorly written query can bring your application to a crawl, while an optimized query can handle thousands of requests per second. Let's explore the techniques and tools for query optimization.

Understanding Query Performance

Before optimizing, you need to understand how your queries are performing. PostgreSQL provides powerful tools for analyzing query execution.

EXPLAIN and EXPLAIN ANALYZE

The EXPLAIN command shows you how PostgreSQL plans to execute your query, while EXPLAIN ANALYZE shows the actual execution time and row counts.

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

-- Detailed analysis with actual execution
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM users WHERE email = 'user@example.com';

Reading Execution Plans

Understanding execution plans is key to optimization:

-- Example execution plan
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- Output interpretation:
-- 1. Sequential Scan vs Index Scan
-- 2. Join methods (Nested Loop, Hash Join, Merge Join)
-- 3. Sort operations
-- 4. Actual vs estimated row counts

Common Performance Issues

1. Missing Indices

-- Slow query without proper indexing
SELECT * FROM users WHERE email = 'user@example.com';
-- This might do a sequential scan of the entire table

-- Solution: Add an index
CREATE INDEX idx_users_email ON users(email);

-- Verify the index is being used
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

2. Inefficient WHERE Clauses

-- Bad: Using functions in WHERE clause
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good: Use expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Better: Store normalized data
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255);
UPDATE users SET email_lower = LOWER(email);
CREATE INDEX idx_users_email_lower ON users(email_lower);

3. **SELECT * Instead of Specific Columns**

-- Bad: Selecting all columns
SELECT * FROM users WHERE active = true;

-- Good: Select only needed columns
SELECT id, name, email FROM users WHERE active = true;

4. Inefficient JOINs

-- Bad: Joining without proper conditions
SELECT u.name, o.amount 
FROM users u, orders o 
WHERE u.id = o.user_id AND o.amount > 100;

-- Good: Explicit JOIN syntax
SELECT u.name, o.amount 
FROM users u
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 100;

Query Optimization Techniques

1. Use Appropriate JOIN Types

-- Choose the right join type
-- INNER JOIN: When you need matching records only
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: When you need all users, even without orders
SELECT u.name, COALESCE(SUM(o.amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

2. Optimize GROUP BY and Aggregations

-- Bad: Grouping by unnecessary columns
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- Good: Group by primary key only
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

3. Use LIMIT and OFFSET Efficiently

-- Bad: Using OFFSET for pagination
SELECT * FROM users ORDER BY name LIMIT 10 OFFSET 1000;

-- Good: Use keyset pagination
SELECT * FROM users 
WHERE name > 'last_seen_name' 
ORDER BY name 
LIMIT 10;

4. Optimize Subqueries

-- Bad: Correlated subquery
SELECT u.name, 
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

-- Good: Use JOIN with aggregation
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Advanced Optimization Techniques

1. Common Table Expressions (CTEs)

-- Use CTEs for complex queries
WITH user_stats AS (
  SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_spent
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY user_id
),
top_customers AS (
  SELECT user_id, order_count, total_spent
  FROM user_stats
  WHERE total_spent > 1000
)
SELECT u.name, tc.order_count, tc.total_spent
FROM users u
INNER JOIN top_customers tc ON u.id = tc.user_id
ORDER BY tc.total_spent DESC;

2. Window Functions

-- Use window functions for ranking and running totals
SELECT 
  name,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) as rank,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders o
JOIN users u ON o.user_id = u.id;

3. Materialized Views

-- Create materialized views for expensive aggregations
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT 
  u.id,
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_spent,
  MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Refresh when needed
REFRESH MATERIALIZED VIEW user_order_summary;

-- Query the materialized view
SELECT * FROM user_order_summary WHERE total_spent > 1000;

Performance Monitoring

1. Check Query Statistics

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

-- 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;

2. Monitor Index Usage

-- Check unused indices
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

Best Practices

1. Always Use EXPLAIN ANALYZE

-- Test your queries before deploying
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

2. Index Strategically

-- Index columns used in WHERE, JOIN, and ORDER BY
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
CREATE INDEX idx_users_active_email ON users(email) WHERE active = true;

3. Use Prepared Statements

-- Prepared statements are parsed once and reused
PREPARE user_query(text) AS 
SELECT * FROM users WHERE email = $1;

EXECUTE user_query('user@example.com');

4. Avoid N+1 Queries

-- Bad: Multiple queries in a loop
-- Good: Single query with JOIN
SELECT u.name, o.amount, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

Configuration Tuning

1. Memory Settings

-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Adjust based on your workload
-- shared_buffers: 25% of RAM for dedicated database
-- work_mem: 4MB-64MB depending on query complexity
-- maintenance_work_mem: 64MB-1GB for maintenance operations

2. Query Planner Settings

-- Enable query plan caching
SET plan_cache_mode = 'auto';

-- Adjust cost estimates
SET random_page_cost = 1.1;  -- For SSD storage
SET seq_page_cost = 1.0;

Common Anti-patterns to Avoid

Using SELECT DISTINCT unnecessarily
Not using appropriate data types
Over-normalizing your schema
Using OR conditions that could be UNION
Not considering the query planner's limitations

Remember: Query optimization is an iterative process. Start with the biggest performance bottlenecks, measure the impact, and continue refining. The goal is to find the right balance between query complexity and performance.