PostgreSQL Composite Index Optimization

Overview

This guide covers adding a composite index to improve query performance when looking up financial note items by type and reference ID.

Table Schema

CREATE TABLE financial_note_items (
    id              SERIAL PRIMARY KEY,
    created_date    TIMESTAMP WITH TIME ZONE,
    updated_date    TIMESTAMP WITH TIME ZONE,
    created_user_id INTEGER,
    updated_user_id INTEGER,
    deleted         BOOLEAN NOT NULL DEFAULT FALSE,
    item_id         INTEGER NOT NULL,
    item_type       VARCHAR(16) NOT NULL,
    amount          NUMERIC(12,2) NOT NULL,
    sgst_amt        NUMERIC(12,2) NOT NULL,
    cgst_amt        NUMERIC(12,2) NOT NULL,
    igst_amt        NUMERIC(12,2) NOT NULL,
    // ... other columns ...
    entity_id         INTEGER NOT NULL
);

Current Indexes

-- Existing indexes
"financial_note_items_pkey" PRIMARY KEY, btree (id)
"financial_note_items_created_user_id" btree (created_user_id)
"financial_note_items_entity_id" btree (entity_id)
"financial_note_items_updated_user_id" btree (updated_user_id)

Problem Statement

We need to find note items given charge items or invoice IDs to check for settlement amount mismatches. The item_id column references these related records, and queries filter by item_type to determine the reference type.

Typical Query Pattern

SELECT * FROM financial_note_items
WHERE item_type = 'CHARGE'
  AND item_id = 12345;

Without a proper index, this query scans a large portion of the table.

Proposed Solution

Create Composite Index

CREATE INDEX CONCURRENTLY financial_note_items_type_item_idx 
ON financial_note_items 
USING btree (item_type, item_id);

Key Points:

• CONCURRENTLY prevents table locks during index creation
• Column order matters: item_type first (lower cardinality), then item_id
• Covers the common query pattern filtering on both columns

Performance Impact

Index Cleanup Recommendations

Consider removing unused indexes to reduce write overhead, but ensure they are not critical for other queries:

-- These indexes are rarely used in queries
DROP INDEX financial_note_items_created_user_id;
DROP INDEX financial_note_items_updated_user_id;

Verification Before Removal

-- Check index usage statistics
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname = 'financial_note_items'
ORDER BY idx_scan DESC;

If idx_scan is consistently 0 or very low for an index, it’s a candidate for removal.

Best Practices

1. Use CONCURRENTLY for Production

-- Good: Non-blocking
CREATE INDEX CONCURRENTLY idx_name ON table_name (columns);

-- Bad: Blocks writes during creation
CREATE INDEX idx_name ON table_name (columns);

2. Column Order in Composite Indexes

Order columns by:

1. Equality conditions first (= comparisons)
2. Lower cardinality columns first
3. Range conditions last (>, <, BETWEEN)

3. Monitor Index Size

SELECT 
    pg_size_pretty(pg_relation_size('financial_note_items_type_item_idx')) as index_size;

4. Analyze After Creation

ANALYZE financial_note_items;

Query Execution Plan Comparison

Before (without composite index)

EXPLAIN ANALYZE
SELECT * FROM financial_note_items
WHERE item_type = 'CHARGE' AND item_id = 12345;

-- Result:
-- Seq Scan on financial_note_items (cost=0.00..45678.00 rows=1 width=120)
--   Filter: ((item_type = 'CHARGE') AND (item_id = 12345))

After (with composite index)

EXPLAIN ANALYZE
SELECT * FROM financial_note_items
WHERE item_type = 'CHARGE' AND item_id = 12345;

-- Result:
-- Index Scan using financial_note_items_type_item_idx (cost=0.43..8.45 rows=1 width=120)
--   Index Cond: ((item_type = 'CHARGE') AND (item_id = 12345))

Rollback Plan

If issues arise:

DROP INDEX CONCURRENTLY financial_note_items_type_item_idx;