/postgresql-indexing

name: postgresql-indexing
description: >
  PostgreSQL indexing best practices for Prowler: index design, partial indexes, partitioned table
  indexing, EXPLAIN ANALYZE validation, concurrent operations, monitoring, and maintenance.
  Trigger: When creating or modifying PostgreSQL indexes, analyzing query performance with EXPLAIN,
  debugging slow queries, reviewing index usage statistics, reindexing, dropping indexes, or working
  with partitioned table indexes. Also trigger when discussing index strategies, partial indexes,
  or index maintenance operations like VACUUM or ANALYZE.
license: Apache-2.0
metadata:
  author: prowler-cloud
  version: "1.0"
  scope: [api]
  auto_invoke:
    - "Creating or modifying PostgreSQL indexes"
    - "Analyzing query performance with EXPLAIN"
    - "Debugging slow queries or missing indexes"
    - "Dropping or reindexing PostgreSQL indexes"
allowed-tools: Read, Grep, Glob, Bash

When to use

• Creating or modifying PostgreSQL indexes
• Analyzing query plans with `EXPLAIN`
• Debugging slow queries or missing index usage
• Dropping, reindexing, or validating indexes
• Working with indexes on partitioned tables (findings, resource_finding_mappings)
• Running VACUUM or ANALYZE after index changes

Index design

Partial indexes: constant columns go in WHERE, not in the key

When a column has a fixed value for the query (e.g., `state = 'completed'`), put it in the `WHERE` clause of the index, not in the indexed columns. Otherwise the planner cannot exploit the ordering of the other columns.

-- Bad: state in the key wastes space and breaks ordering
CREATE INDEX idx_scans_tenant_state ON scans (tenant_id, state, inserted_at DESC);

-- Good: state as a filter, planner uses tenant_id + inserted_at ordering
CREATE INDEX idx_scans_tenant_ins_completed ON scans (tenant_id, inserted_at DESC)
    WHERE state = 'completed';

Column order matters

Put high-selectivity columns first (columns that filter out the most rows). For composite indexes, the leftmost column must appear in the query's WHERE clause for the index to be used.

Validating index effectiveness

Always EXPLAIN (ANALYZE, BUFFERS) after adding indexes

Never assume an index is being used. Run `EXPLAIN (ANALYZE, BUFFERS)` to confirm.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = 'user@example.com';

Use [Postgres EXPLAIN Visualizer (pev)](https://tatiyants.com/pev/) to visualize query plans and identify bottlenecks.

Force index usage for testing

The planner may choose a sequential scan on small datasets. Toggle `enable_seqscan = off` to confirm the index path works, then re-enable it.

SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT ON (provider_id) provider_id
FROM scans
WHERE tenant_id = '95383b24-da01-44b5-a713-0d9920d554db'
  AND state = 'completed'
ORDER BY provider_id, inserted_at DESC;

SET enable_seqscan = on;  -- always re-enable after testing

This is for validation only. Never leave `enable_seqscan = off` in production.

Over-indexing

Every extra index has three costs that compound:

1. **Write overhead.** Every INSERT and UPDATE must maintain all indexes. Extra indexes also kill HOT (Heap-Only-Tuple) updates, which normally skip index maintenance when unindexed columns change.

2. **Planning time.** The planner evaluates more execution paths per index. On simple OLTP queries, planning time can exceed execution time by 4x when index count is high.

3. **Lock contention (fastpath limit).** PostgreSQL uses a fast path for the first 16 locks per backend. After 16 relations (table + its indexes), it falls back to slower LWLock mechanisms. At high QPS (100+), this causes `LockManager` wait events.

Rules:

• Drop unused and redundant indexes regularly
• Be especially careful with partitioned tables (each partition multiplies the index count)
• Use prepared statements to reduce planning overhead when index count is high

Finding redundant indexes

Two indexes are redundant when:

• They have the same columns in the same order (duplicates)
• One is a prefix of the other: index `(a)` is redundant to `(a, b)`, but NOT to `(b, a)`

Column order matters. For partial indexes, the WHERE clause must also match.

-- Quick check: find indexes that share a leading column on the same table
SELECT
    a.indrelid::regclass AS table_name,
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a,
    pg_size_pretty(pg_relation_size(b.indexrelid)) AS size_b
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid != b.indexrelid
    AND a.indkey::text = (
        SELECT string_agg(x::text, ' ')
        FROM unnest(b.indkey[:array_length(a.indkey, 1)]) AS x
    )
WHERE NOT a.indisunique;

Before dropping: verify on all workload nodes (primary + replicas), use `DROP INDEX CONCURRENTLY`, and monitor for plan regressions.

Monitoring index usage

Identify unused indexes

Query `pg_stat_all_indexes` to find indexes that are never or rarely scanned:

SELECT
    idxstat.schemaname AS schema_name,
    idxstat.relname AS table_name,
    idxstat.indexrelname AS index_name,
    idxstat.idx_scan AS index_scans_count,
    idxstat.last_idx_scan AS last_idx_scan_timestamp,
    pg_size_pretty(pg_relation_size(idxstat.indexrelid)) AS index_size
FROM pg_stat_all_indexes AS idxstat
JOIN pg_index i ON idxstat.indexrelid = i.indexrelid
WHERE idxstat.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND NOT i.indisunique
ORDER BY idxstat.idx_scan ASC, idxstat.last_idx_scan ASC;

Indexes with `idx_scan = 0` and no recent `last_idx_scan` are candidates for removal.

Before dropping, verify:

• Stats haven't been reset recently (check `stats_reset` in `pg_stat_database`)
• Stats cover at least 1 month of production traffic
• All workload nodes (primary + replicas) have been checked

-