Covering Indexes: Optimizing Query Performance Through Index Design

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

-- Measure write performance impact of covering index
 
-- Step 1: Baseline - measure INSERT performance without covering index
-- (Disable or drop covering index first)
\timing on
 
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT 
    (random() * 1000000)::int,
    CURRENT_DATE - (random() * 365)::int,
    random() * 1000,
    (ARRAY['pending', 'shipped', 'delivered'])[1 + (random()*3)::int % 3]
FROM generate_series(1, 10000);
 
-- Record: INSERT 10000, Time: XXX ms
 
-- Step 2: Create covering index
CREATE INDEX idx_orders_covering 
ON orders (customer_id, order_date)
INCLUDE (total_amount, status, shipping_date);
 
-- Step 3: Measure INSERT performance with covering index
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT 
    (random() * 1000000)::int,
    CURRENT_DATE - (random() * 365)::int,
    random() * 1000,
    (ARRAY['pending', 'shipped', 'delivered'])[1 + (random()*3)::int % 3]
FROM generate_series(1, 10000);
 
-- Record: INSERT 10000, Time: XXX ms (expect 20-50% slower)
 
-- Step 4: Measure UPDATE performance on INCLUDE column
-- Updates to 'status' (INCLUDE column) require index maintenance
 
UPDATE orders SET status = 'shipped' 
WHERE order_id IN (SELECT order_id FROM orders 
                   WHERE status = 'pending' LIMIT 1000);
 
-- Compare with baseline (no covering index) UPDATE time
 
-- Step 5: Track index write statistics
SELECT 
    indexrelname,
    idx_tup_insert,
    idx_tup_delete
FROM pg_stat_user_indexes
WHERE relname = 'orders';
 
-- High idx_tup_delete + idx_tup_insert = lots of index churn
-- (UPDATEs are implemented as delete + insert in PostgreSQL)

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

-- Comprehensive storage analysis for covering indexes
 
-- 1. Current index sizes and overhead
SELECT 
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    pg_size_pretty(pg_relation_size(tablename::regclass)) as table_size,
    round(100.0 * pg_relation_size(indexrelid) / 
          NULLIF(pg_relation_size(tablename::regclass), 0), 2) as index_pct_of_table
FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- 2. Index bloat estimation (dead tuples consuming space)
SELECT 
    schemaname, 
    relname as indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_tup_read,
    idx_tup_fetch,
    -- High ratio of deletes to current entries indicates bloat potential
    (SELECT n_dead_tup FROM pg_stat_user_tables 
     WHERE relname = i.relname) as table_dead_tuples
FROM pg_stat_user_indexes i
WHERE schemaname = 'public';
 
-- 3. WAL generation by index (requires pg_stat_statements)
-- Check which indexes generate most WAL activity
SELECT 
    queryid,
    calls,
    wal_records,
    wal_bytes,
    query
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
  AND (query ILIKE '%INSERT%' OR query ILIKE '%UPDATE%')
ORDER BY wal_bytes DESC
LIMIT 10;
 
-- 4. Total storage impact including all indexes
SELECT 
    pg_size_pretty(pg_total_relation_size('orders')) as total_size,
    pg_size_pretty(pg_relation_size('orders')) as table_only,
    pg_size_pretty(pg_indexes_size('orders')) as all_indexes,
    round(100.0 * pg_indexes_size('orders') / 
          pg_total_relation_size('orders'), 1) as indexes_pct_of_total;

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

-- Monitor vacuum performance for tables with covering indexes
 
-- Check vacuum statistics
SELECT 
    relname,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) 
        as dead_tuple_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
-- Check index-specific vacuum activity
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';
 
-- Estimate vacuum duration for large covering indexes
-- Run VACUUM VERBOSE to see detailed timing:
VACUUM VERBOSE orders;
 
-- Output shows:
-- - Time spent on table pages
-- - Time spent on each index
-- - Covering indexes take longer due to larger entry size
 
-- Autovacuum tuning for tables with multiple covering indexes
-- Consider more aggressive settings:
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- Vacuum at 5% dead tuples
    autovacuum_vacuum_cost_limit = 2000,     -- Allow more work per cycle
    autovacuum_vacuum_cost_delay = 5         -- Shorter delays between work
);

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

-- Calculate read-write ratio for covering index decision
 
-- Step 1: Measure read operations (queries that would use the index)
WITH query_stats AS (
    SELECT 
        SUM(calls) as total_reads
    FROM pg_stat_statements
    WHERE query ILIKE '%SELECT%orders%customer_id%'
      AND calls > 100
)
SELECT total_reads FROM query_stats;
 
-- Step 2: Measure write operations on the table
SELECT 
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    (n_tup_ins + n_tup_upd + n_tup_del) as total_writes
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
-- Step 3: Calculate read-write ratio
-- Reads : Writes ratio determines covering index viability
 
-- GUIDELINES:
-- > 100:1 reads:writes → Excellent candidate, proceed with covering index
-- 10-100:1 reads:writes → Good candidate, evaluate storage costs
-- 1-10:1 reads:writes → Marginal, only for critical queries
-- < 1:1 reads:writes → Poor candidate, likely not worth the overhead
 
-- Step 4: Factor in query improvement magnitude
-- If covering index provides 100x improvement for 10:1 read:write,
-- net benefit = (100x faster reads) vs (2x slower writes)
-- This is likely still worthwhile
 
-- DETAILED ROI CALCULATION:
-- Time saved per read: estimated_current_time - estimated_new_time
-- Extra time per write: estimated_covering_overhead
-- Daily net: (reads × time_saved) - (writes × extra_time)
 
-- If daily net is positive, covering index provides value

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

-- Comprehensive covering index monitoring
 
-- 1. Index usage and efficiency
SELECT 
    schemaname,
    relname as table_name,
    indexrelname as index_name,
    idx_scan as times_used,
    idx_tup_read,
    idx_tup_fetch,
    -- Ratio of actual heap fetches to index lookups
    -- Lower is better for covering indexes (0 = perfect coverage)
    round(100.0 * idx_tup_fetch / NULLIF(idx_tup_read, 0), 2) 
        as heap_fetch_pct,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE relname LIKE '%orders%'
ORDER BY idx_scan DESC;
 
-- 2. Identify unused covering indexes (candidates for removal)
SELECT 
    schemaname || '.' || relname as table,
    indexrelname as index,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan as scans,
    idx_tup_read as tuples_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used!
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- > 10MB
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- 3. Check index bloat (comparing actual vs expected size)
SELECT 
    current_database() as database,
    nspname as schema,
    tblname as table,
    idxname as index,
    pg_size_pretty(real_size::bigint) as actual_size,
    pg_size_pretty(expected_size::bigint) as expected_size,
    round(bloat_ratio::numeric, 1) as bloat_ratio
FROM (
    -- This is a simplified bloat check
    -- For production, use more sophisticated bloat queries
    SELECT 
        n.nspname,
        c.relname as tblname,
        i.relname as idxname,
        pg_relation_size(i.oid) as real_size,
        pg_relation_size(i.oid) * 0.9 as expected_size,  -- Rough estimate
        pg_relation_size(i.oid)::float / 
            NULLIF(pg_relation_size(i.oid) * 0.9, 0) as bloat_ratio
    FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class i ON i.oid = x.indexrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
) as bloat_check
WHERE bloat_ratio > 1.5  -- More than 50% bloated
ORDER BY real_size DESC;
 
-- 4. Track index maintenance-related wait events
SELECT 
    event,
    wait_count,
    sum_wait_time_ms
FROM pg_wait_events()  -- Hypothetical; use actual monitoring tools
WHERE event LIKE '%idx%' OR event LIKE '%index%';
 
-- 5. Set up alerting thresholds
-- Alert if: heap_fetch_pct > 20 (visibility map issues)
-- Alert if: idx_scan = 0 for 7+ days (unused index)
-- Alert if: bloat_ratio > 2.0 (needs reindex)
-- Alert if: index_size > expected_size * 1.5