Designing Effective Covering Indexes

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

-- Step 1: Enable pg_stat_statements for query analysis
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Step 2: Identify high-frequency queries with table access
SELECT 
    substring(query, 1, 100) as query_sample,
    calls,
    total_exec_time / calls as avg_time_ms,
    rows / calls as avg_rows,
    shared_blks_read / calls as avg_disk_reads,
    (shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100)::int 
        as buffer_hit_pct
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
  AND calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Step 3: Examine individual query for column access
-- For each candidate query, extract all referenced columns:
-- - SELECT list columns (output)
-- - WHERE clause columns (filtering)
-- - JOIN condition columns
-- - ORDER BY columns
-- - GROUP BY columns
 
-- Example: Analyze orders table query patterns
SELECT 
    query,
    -- Extract column references (simplified - use query parser for production)
    calls,
    total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%FROM orders%'
  AND query NOT ILIKE '%INSERT%'
  AND query NOT ILIKE '%UPDATE%'
ORDER BY total_exec_time DESC;
 
-- Step 4: Identify queries with poor index coverage
-- Look for queries with high avg_disk_reads indicating heap access
SELECT *
FROM pg_stat_statements
WHERE shared_blks_read / NULLIF(calls, 0) > 100  -- High disk reads
  AND calls > 1000  -- Frequently executed
ORDER BY shared_blks_read 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

-- Example: Analyzing column order for a reporting query
 
-- Query pattern:
SELECT order_id, customer_id, total_amount, status
FROM orders
WHERE status = 'shipped'           -- Equality filter
  AND region = 'North America'     -- Equality filter  
  AND order_date >= '2024-01-01'   -- Range filter
ORDER BY order_date DESC;
 
-- CORRECT column order:
CREATE INDEX idx_orders_covering ON orders 
(status, region, order_date DESC)  -- Keys in correct order
INCLUDE (order_id, customer_id, total_amount);
 
-- Why this order works:
-- 1. status = 'shipped' → Seek to 'shipped' entries
-- 2. region = 'North America' → Further narrow within 'shipped'
-- 3. order_date >= '2024-01-01' → Range scan (stops the key matching)
-- 4. DESC order matches query ORDER BY (no additional sort needed)
 
-- INCORRECT column order:
CREATE INDEX idx_orders_wrong ON orders 
(order_date, status, region)  -- Range column first!
INCLUDE (order_id, customer_id, total_amount);
 
-- Why this fails:
-- 1. order_date >= '2024-01-01' is a RANGE, so it scans all matching dates
-- 2. status and region filters must be applied as post-scan filters
-- 3. Much less efficient - scans all dates, then filters

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

-- Example: Designing key vs. INCLUDE for a complex query
 
-- Query:
SELECT 
    customer_id,          -- Used in WHERE (equality)
    order_date,           -- Used in WHERE (range) and ORDER BY
    total_amount,         -- Only in SELECT
    status,               -- Only in SELECT
    shipping_address      -- Only in SELECT
FROM orders
WHERE customer_id = @customerId
  AND order_date >= @startDate
ORDER BY order_date DESC;
 
-- Optimal index design:
CREATE NONCLUSTERED INDEX IX_orders_customer_date_covering
ON orders (customer_id, order_date DESC)  -- Keys: filter + sort columns
INCLUDE (total_amount, status, shipping_address);  -- Include: output-only
 
-- Why this works:
-- 1. customer_id is first: enables seek for the specific customer
-- 2. order_date DESC is second: enables range scan + provides sort order
-- 3. total_amount, status, shipping_address: only needed for output
--    → INCLUDE is perfect: no sort contribution, just data coverage
 
-- ANTI-PATTERN: All columns as keys
CREATE NONCLUSTERED INDEX IX_orders_overkill
ON orders (customer_id, order_date, total_amount, status, shipping_address);
 
-- Problems:
-- 1. All columns stored at internal nodes → lower fanout → taller tree
-- 2. More data to maintain on every INSERT/UPDATE
-- 3. No benefit: we don't filter/sort on amount, status, or address

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

-- Multi-query coverage analysis workflow
 
-- Step 1: Group queries by table and leading filter columns
-- Identify query families that can share indexes
 
-- Family 1: Customer-centric queries
-- Leading column: customer_id
-- Additional filters: status, date ranges
-- Output columns: order details
 
-- Design covering index for Family 1:
CREATE INDEX idx_orders_by_customer
ON orders (customer_id, status, order_date DESC)
INCLUDE (order_id, total_amount, shipping_date, item_count);
 
-- Verify coverage for all family queries:
EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, total_amount FROM orders WHERE customer_id = 123;
-- Expected: Index Only Scan
 
EXPLAIN (ANALYZE, COSTS OFF)  
SELECT order_id, total_amount, status 
FROM orders WHERE customer_id = 123 AND status = 'pending';
-- Expected: Index Only Scan
 
EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, order_date, total_amount, shipping_date
FROM orders WHERE customer_id = 123 
ORDER BY order_date DESC LIMIT 10;
-- Expected: Index Only Scan with no sort operation
 
-- Family 2: Date-centric queries (different leading column)
-- These CANNOT share an index with Family 1 efficiently
 
CREATE INDEX idx_orders_by_date
ON orders (order_date, region)
INCLUDE (order_id, customer_id, total_amount);
 
-- This covers:
EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, customer_id, total_amount
FROM orders WHERE order_date = '2024-01-15';
 
EXPLAIN (ANALYZE, COSTS OFF)
SELECT SUM(total_amount) FROM orders
WHERE order_date >= '2024-01-01' AND region = 'West';

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

-- Estimate covering index size before creation
 
-- Get average column widths from statistics
SELECT 
    attname,
    avg_width,
    n_distinct
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('customer_id', 'order_date', 'status', 
                  'total_amount', 'shipping_date');
                  
-- Example output:
-- customer_id: 4 bytes (integer)
-- order_date: 4 bytes (date)
-- status: 12 bytes (varchar average)
-- total_amount: 8 bytes (numeric)
-- shipping_date: 4 bytes (date)
 
-- Calculate estimated index size:
SELECT 
    (SELECT reltuples FROM pg_class WHERE relname = 'orders') as row_count,
    -- Per-entry size: columns + 6 bytes tuple header + 6 bytes ItemId
    (4 + 4 + 12 + 8 + 4) + 6 + 6 as bytes_per_entry,
    -- Estimated total (with 20% B-tree overhead)
    pg_size_pretty(
        ((SELECT reltuples FROM pg_class WHERE relname = 'orders') 
         * ((4 + 4 + 12 + 8 + 4) + 12)
         * 1.2)::bigint
    ) as estimated_index_size;
 
-- After index creation, verify actual size:
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as actual_size
FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
WHERE tablename = 'orders';
 
-- Compare covering index size to table size:
SELECT 
    pg_size_pretty(pg_relation_size('orders')) as table_size,
    pg_size_pretty(pg_relation_size('idx_orders_covering')) as index_size,
    round(100.0 * pg_relation_size('idx_orders_covering') / 
          pg_relation_size('orders'), 1) as index_pct_of_table;

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

-- Strategy 1: Partial Index for Active Records Only
-- Full table: 100M rows, but only 5M are status='active'
 
-- Instead of indexing all rows:
CREATE INDEX idx_orders_all ON orders (customer_id) 
INCLUDE (order_date, total_amount);
-- Size: ~8GB
 
-- Use a partial index:
CREATE INDEX idx_orders_active ON orders (customer_id)
INCLUDE (order_date, total_amount)
WHERE status = 'active';
-- Size: ~400MB (5% of original!)
 
-- Query must include matching WHERE clause:
SELECT order_date, total_amount FROM orders
WHERE customer_id = 123 AND status = 'active';
-- Uses idx_orders_active
 
 
-- Strategy 2: Compression (SQL Server example)
CREATE NONCLUSTERED INDEX IX_orders_compressed
ON orders (customer_id, order_date)
INCLUDE (total_amount, status)
WITH (DATA_COMPRESSION = PAGE);
-- PAGE compression typically reduces size 50-70%
 
 
-- Strategy 3: Partitioned Coverage
-- Cover only recent partitions with full covering index
-- PostgreSQL example with declarative partitioning:
 
CREATE INDEX idx_orders_2024_covering 
ON orders_2024 (customer_id, order_date)
INCLUDE (order_id, total_amount, status);
 
-- Older partitions: minimal index only
CREATE INDEX idx_orders_2023_minimal
ON orders_2023 (customer_id, order_date);
-- Queries on old data are less frequent, can tolerate heap access

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

-- ANTI-PATTERN 1: The "Cover Everything" Index
-- DON'T DO THIS
CREATE INDEX idx_orders_everything ON orders (
    order_id, customer_id, order_date, status, total_amount,
    shipping_address, billing_address, created_at, updated_at,
    product_count, discount_code, sales_rep_id, notes
);
-- Problems:
-- - Massive index size (possibly larger than table)
-- - Every INSERT/UPDATE touches all columns
-- - Internal nodes are huge, tree is tall
 
-- BETTER: Multiple focused covering indexes
CREATE INDEX idx_orders_customer_lookup 
ON orders (customer_id, order_date) 
INCLUDE (order_id, total_amount, status);
 
CREATE INDEX idx_orders_status_report
ON orders (status, order_date)
INCLUDE (order_id, customer_id, total_amount);
 
 
-- ANTI-PATTERN 2: Wrong Key Column Order
-- Query: WHERE region = 'West' AND order_date >= '2024-01-01'
 
-- DON'T DO THIS (range column first)
CREATE INDEX idx_wrong_order ON orders (order_date, region);
-- Can't seek to region='West', must scan all dates
 
-- CORRECT (equality column first)
CREATE INDEX idx_right_order ON orders (region, order_date);
-- Seeks to 'West', then range scan on date
 
 
-- ANTI-PATTERN 3: Duplicate Coverage
-- DON'T create both of these:
CREATE INDEX idx_orders_v1 ON orders (customer_id) 
INCLUDE (order_date, total_amount);
 
CREATE INDEX idx_orders_v2 ON orders (customer_id) 
INCLUDE (order_date, total_amount, status);
 
-- CONSOLIDATE into one:
CREATE INDEX idx_orders_unified ON orders (customer_id)
INCLUDE (order_date, total_amount, status);
-- The superset covers all queries the subsets would cover