Latency Optimization: Eliminating Performance Bottlenecks

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- PostgreSQL: Basic query with execution plan
EXPLAIN ANALYZE
SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
  AND o.status = 'completed';
 
-- Example output:
/*
Nested Loop  (cost=0.87..1234.56 rows=142 width=40) 
             (actual time=0.045..2.341 rows=138 loops=1)
  ->  Index Scan using idx_orders_created_at on orders o
        (cost=0.43..567.89 rows=142 width=20) 
        (actual time=0.025..1.234 rows=138 loops=1)
        Index Cond: (created_at > '2024-01-01')
        Filter: (status = 'completed')
        Rows Removed by Filter: 45
  ->  Index Scan using users_pkey on users u
        (cost=0.43..4.45 rows=1 width=24) 
        (actual time=0.006..0.006 rows=1 loops=138)
        Index Cond: (id = o.user_id)
Planning Time: 0.234 ms
Execution Time: 2.456 ms
*/
 
-- Key things to look for:
-- 1. "Seq Scan" on large tables - usually bad, needs index
-- 2. "actual rows" >> "rows" estimate - stale statistics
-- 3. High "loops" count with nested loops - may need different join
-- 4. "Rows Removed by Filter" is high - filter not pushed to index
-- 5. Large "cost" numbers - expensive operations
 
-- PostgreSQL: Get detailed buffer/IO statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM large_table WHERE id = 12345;
 
-- MySQL equivalent
EXPLAIN ANALYZE
SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- Single column index for equality and range queries
CREATE INDEX idx_orders_created_at ON orders(created_at);
 
-- Composite index for multi-column filtering
-- Column order matters! Put equality columns first, then range
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);
 
-- This index supports these queries efficiently:
-- WHERE user_id = X
-- WHERE user_id = X AND status = Y
-- WHERE user_id = X AND status = Y AND created_at > Z
-- BUT NOT: WHERE status = Y (can't skip user_id)
 
-- Covering index: includes all columns query needs
-- Enables "Index Only Scan" - no table access required
CREATE INDEX idx_orders_covering 
ON orders(user_id, status) 
INCLUDE (order_total, created_at);
 
-- Partial index: only index subset of rows
-- Smaller, faster, perfect for skewed data
CREATE INDEX idx_active_orders 
ON orders(created_at) 
WHERE status = 'active';
 
-- Expression index: index computed values
CREATE INDEX idx_users_lower_email 
ON users(LOWER(email));
 
-- Now this query uses the index:
-- WHERE LOWER(email) = 'user@example.com'
 
-- For text search with LIKE 'prefix%'
-- B-tree works for prefix, but need special operator class
CREATE INDEX idx_users_name_pattern 
ON users(name varchar_pattern_ops);
 
-- PostgreSQL: Check index usage
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

-- PROBLEM 1: Subquery in WHERE with correlation
-- Bad: Correlated subquery executes once per row
SELECT * FROM orders o
WHERE o.total > (
    SELECT AVG(total) FROM orders o2 WHERE o2.user_id = o.user_id
);
 
-- Better: Rewrite as JOIN with pre-aggregated data
SELECT o.* FROM orders o
JOIN (
    SELECT user_id, AVG(total) as avg_total 
    FROM orders 
    GROUP BY user_id
) user_avg ON o.user_id = user_avg.user_id
WHERE o.total > user_avg.avg_total;
 
-- PROBLEM 2: OR conditions on different columns
-- Bad: Often can't use indexes effectively
SELECT * FROM products WHERE category_id = 5 OR brand_id = 10;
 
-- Better: UNION of two indexed queries
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE brand_id = 10;
 
-- PROBLEM 3: DISTINCT with large result set
-- Bad: Sorts entire result for deduplication
SELECT DISTINCT user_id FROM orders WHERE created_at > '2024-01-01';
 
-- Better: GROUP BY (sometimes optimizes differently)
SELECT user_id FROM orders 
WHERE created_at > '2024-01-01' 
GROUP BY user_id;
 
-- Even better if exists check is sufficient
SELECT user_id FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.created_at > '2024-01-01'
);
 
-- PROBLEM 4: Counting with expensive conditions
-- Bad: Counts all matching rows
SELECT COUNT(*) FROM orders WHERE status = 'completed';
 
-- Better for approximation: Use pg_stat_user_tables
SELECT n_tup_ins - n_tup_del AS approx_count 
FROM pg_stat_user_tables WHERE relname = 'orders';
 
-- Or for exact count on filtered large table, consider:
SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'orders';
 
-- PROBLEM 5: Pagination with OFFSET
-- Bad: OFFSET 10000 still reads and discards 10000 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;
 
-- Better: Keyset pagination (seek method)
SELECT * FROM products 
WHERE id > 10020  -- Last ID from previous page
ORDER BY id LIMIT 20;
 
-- PROBLEM 6: NOT IN with nullable column
-- Bad: NOT IN with NULLs returns unexpected results, can't use index
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM deleted_users);
 
-- Better: NOT EXISTS (handles NULLs correctly, often faster)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM deleted_users d WHERE d.user_id = u.id);
 
-- Or: LEFT JOIN / IS NULL
SELECT u.* FROM users u
LEFT JOIN deleted_users d ON u.id = d.user_id
WHERE d.user_id IS NULL;
 
-- PROBLEM 7: Multiple conditions on same table
-- Bad: Multiple subqueries hit same table repeatedly
SELECT * FROM products 
WHERE category_id = (SELECT id FROM categories WHERE name = 'Electronics')
  AND brand_id = (SELECT id FROM brands WHERE name = 'Samsung');
 
-- Better: Single lookup with JOIN
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
WHERE c.name = 'Electronics' AND b.name = 'Samsung';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Ensure FK columns are indexed
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
 
-- PostgreSQL: Force specific join order when optimizer fails
SET join_collapse_limit = 1;  -- Respect explicit join order
 
SELECT *
FROM users u
JOIN orders o ON o.user_id = u.id        -- Join 1
JOIN order_items oi ON oi.order_id = o.id  -- Join 2
JOIN products p ON p.id = oi.product_id    -- Join 3
WHERE u.id = 12345;
 
RESET join_collapse_limit;
 
-- PostgreSQL: Hint to disable specific join type
SET enable_hashjoin = off;  -- Force nested loop or merge join
EXPLAIN ANALYZE SELECT ...;
SET enable_hashjoin = on;   -- Reset
 
-- Large join optimization: materialize expensive subquery
WITH expensive_filter AS MATERIALIZED (
    SELECT user_id, SUM(total) as total_spend
    FROM orders
    WHERE created_at > '2024-01-01'
    GROUP BY user_id
    HAVING SUM(total) > 1000
)
SELECT u.*, ef.total_spend
FROM users u
JOIN expensive_filter ef ON u.id = ef.user_id;
 
-- Lateral join for efficient correlated logic
-- Get latest 3 orders per user efficiently
SELECT u.*, recent_orders.*
FROM users u,
LATERAL (
    SELECT order_id, total, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent_orders
WHERE u.status = 'active';
 
-- This is more efficient than:
-- SELECT * FROM users u
-- JOIN orders o ON o.user_id = u.id
-- WHERE ... (with window functions for top 3)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

// N+1 PROBLEM: 1 query for users, N queries for orders
const users = await prisma.user.findMany({ take: 100 });
for (const user of users) {
    // This executes 100 separate queries!
    const orders = await prisma.order.findMany({
        where: { userId: user.id }
    });
}
 
// SOLUTION: Eager loading / batch fetching
const usersWithOrders = await prisma.user.findMany({
    take: 100,
    include: {
        orders: true  // Single query with JOIN
    }
});
 
// ALTERNATIVE: DataLoader pattern for GraphQL
import DataLoader from 'dataloader';
 
const orderLoader = new DataLoader(async (userIds: string[]) => {
    const orders = await prisma.order.findMany({
        where: { userId: { in: userIds } }
    });
    // Group by userId and return in same order as input
    const ordersByUser = new Map<string, Order[]>();
    for (const order of orders) {
        const existing = ordersByUser.get(order.userId) || [];
        ordersByUser.set(order.userId, [...existing, order]);
    }
    return userIds.map(id => ordersByUser.get(id) || []);
});
 
// Now resolvers use the loader - automatically batched
const resolvers = {
    User: {
        orders: (user) => orderLoader.load(user.id)
    }
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Efficient: Get top 100 with rank
-- Use window function with LIMIT
SELECT 
    user_id,
    score,
    RANK() OVER (ORDER BY score DESC) as position
FROM leaderboard
ORDER BY score DESC
LIMIT 100;
 
-- Get specific user's rank without scanning entire table
-- Create covering index
CREATE INDEX idx_leaderboard_score ON leaderboard(score DESC, user_id);
 
-- Count users with higher score
SELECT 
    (SELECT COUNT(*) FROM leaderboard WHERE score > l.score) + 1 as position,
    l.score
FROM leaderboard l
WHERE l.user_id = 12345;
 
-- For pagination in leaderboards, use keyset
SELECT user_id, score
FROM leaderboard
WHERE score < 9500  -- Last score from previous page
ORDER BY score DESC
LIMIT 100;
 
-- For ties, include user_id as tiebreaker
WHERE (score, user_id) < (9500, 'prev_user_id')
ORDER BY score DESC, user_id DESC
LIMIT 100;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Partition by time for efficient range queries
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    created_at TIMESTAMP,
    data JSONB
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
 
-- Query automatically only scans relevant partition
SELECT * FROM events 
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-20';
 
-- For large time-series, use BRIN index (tiny, fast for sorted data)
CREATE INDEX idx_events_created_at_brin ON events 
USING BRIN (created_at) WITH (pages_per_range = 128);
 
-- Aggregate with date_trunc for grouping
SELECT 
    date_trunc('hour', created_at) as hour,
    event_type,
    COUNT(*) as count
FROM events
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;
 
-- Pre-aggregate into summary table for fast dashboards
INSERT INTO event_hourly_summary (hour, event_type, count)
SELECT 
    date_trunc('hour', created_at),
    event_type,
    COUNT(*)
FROM events
WHERE created_at >= NOW() - INTERVAL '1 hour'
GROUP BY 1, 2
ON CONFLICT (hour, event_type) 
DO UPDATE SET count = EXCLUDED.count;