Database Performance Optimization

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

B-Tree Index Structure (simplified)
 
                        [Root: 50, 100]
                       /       |        \
                      /        |         \
           [20, 35]      [65, 85]      [120, 150]
           /  |  \        /  |  \        /  |  \
          /   |   \      /   |   \      /   |   \
      [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
      
      Finding key "75":
      1. Start at root [50, 100]
      2. 75 > 50 and 75 < 100 → go to middle child
      3. At [65, 85]: 75 > 65 and 75 < 85 → go to middle child  
      4. Scan leaf node for key 75 → found in 3 node accesses!
      
      B-tree properties:
      - Height is O(log n) where n = number of keys
      - All leaves at same depth (self-balancing)
      - Nodes are sized to fit disk pages (typically 8KB)
      - Branching factor of 100-1000 typical (depends on key size)
      - 1 billion rows ≈ 4-5 levels = 4-5 disk reads max

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

-- B-Tree (default) - equality and range queries
CREATE INDEX idx_orders_date ON orders(created_at);
-- Supports: =, <, <=, >, >=, BETWEEN, IN, IS NULL
 
-- Hash - only equality, but very fast
CREATE INDEX idx_sessions_token ON sessions USING hash(token);
-- Only supports: =
-- Note: Pre-PostgreSQL 10, hash indexes weren't crash-safe
 
-- GIN - for arrays and JSONB
CREATE INDEX idx_products_tags ON products USING gin(tags);
-- Supports: @>, <@, &&, ? for arrays
-- Example query: SELECT * FROM products WHERE tags @> ARRAY['sale'];
 
-- GIN for JSONB
CREATE INDEX idx_events_data ON events USING gin(metadata);
-- Example: SELECT * FROM events WHERE metadata @> '{"type": "click"}';
 
-- GiST - for geometric and range types
CREATE INDEX idx_stores_location ON stores USING gist(location);
-- Supports: <<, >>, ~=, && for geometric operations
-- With PostGIS: ST_Contains, ST_DWithin, ST_Intersects
 
-- BRIN - for huge, naturally sorted tables (e.g., time-series)
CREATE INDEX idx_logs_timestamp ON logs USING brin(created_at);
-- Tiny index (1000x smaller than B-tree) for sorted data
-- Perfect for append-only tables with timestamp columns
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles 
  USING gin(to_tsvector('english', title || ' ' || body));
-- Example: SELECT * FROM articles 
--          WHERE to_tsvector('english', title || ' ' || body) 
--                @@ to_tsquery('database & optimization');

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

-- Index on (country, city, district)
CREATE INDEX idx_address_location 
ON addresses(country, city, district);
 
-- ✅ Uses index fully (all columns matched, leftmost to rightmost)
SELECT * FROM addresses 
WHERE country = 'US' AND city = 'Seattle' AND district = 'Downtown';
 
-- ✅ Uses index (leftmost prefix: country, city)
SELECT * FROM addresses 
WHERE country = 'US' AND city = 'Seattle';
 
-- ✅ Uses index (leftmost prefix: country only)
SELECT * FROM addresses WHERE country = 'US';
 
-- ❌ Cannot use index (skips "country" - leftmost column)
SELECT * FROM addresses WHERE city = 'Seattle';
 
-- ❌ Cannot use index (skips "country" and "city")
SELECT * FROM addresses WHERE district = 'Downtown';
 
-- ⚠️ Partially uses index (country only, city comparison is range)
SELECT * FROM addresses 
WHERE country = 'US' AND city LIKE 'Sea%';
-- Index used for country = 'US', then scans for city LIKE 'Sea%'
 
-- ORDER BY also benefits from composite indexes
-- ✅ Uses index for both filtering AND sorting
SELECT * FROM addresses 
WHERE country = 'US' 
ORDER BY city, district;
 
-- ❌ Requires sorting (order doesn't match index)
SELECT * FROM addresses 
WHERE country = 'US' 
ORDER BY district, city;

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

-- Query we want to optimize
SELECT customer_id, SUM(amount), COUNT(*)
FROM orders
WHERE status = 'completed'
  AND created_at >= '2024-01-01'
GROUP BY customer_id;
 
-- Basic index (requires heap access to get 'amount')
CREATE INDEX idx_orders_status_date 
ON orders(status, created_at);
-- Execution: Index Scan + 1 heap read per row
 
-- Covering index includes 'amount' and 'customer_id'
CREATE INDEX idx_orders_covering 
ON orders(status, created_at) 
INCLUDE (customer_id, amount);
-- PostgreSQL 11+ syntax: INCLUDE for non-key columns
 
-- Execution: Index Only Scan - no heap access!
-- Much faster for queries that match this pattern
 
-- Alternative covering index (all columns in key)
CREATE INDEX idx_orders_covering_alt 
ON orders(status, created_at, customer_id, amount);
-- Works but larger index; INCLUDE is usually better

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

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    relname as table_name,
    indexrelname as index_name,
    idx_scan as times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used
  AND indexrelname NOT LIKE '%_pkey'  -- Exclude primary keys
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Find duplicate/redundant indexes
SELECT 
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
    (array_agg(idx))[1] as index1,
    (array_agg(idx))[2] as index2
FROM (
    SELECT indexrelid::regclass as idx, 
           indrelid, 
           indkey::text
    FROM pg_index
) sub
GROUP BY indrelid, indkey
HAVING count(*) > 1;
 
-- Check index bloat (simplified)
SELECT
    nspname, relname,
    round(100 * pg_relation_size(indexrelid) / 
          pg_relation_size(indrelid))::text || '%' as index_ratio
FROM pg_stat_user_indexes i
JOIN pg_class c ON i.relid = c.oid
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
 
-- Index usage statistics
SELECT
    relname as table_name,
    indexrelname as index_name,
    idx_scan as scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;