PostgreSQL Index Tuning Advisor: detect, prioritize and track index optimizations by table and by query

Why this module exists

Even with good SQL practices from the start, a database evolves: new usage patterns, growing volume, regressions after a release, changing execution plans. Without dedicated tooling, this drift is only detected once the incident is already visible in production.

PWR's Index Tuning Advisor turns the statistics history already collected by the platform (perfhist snapshots) into concrete actions: it isolates the tables and queries where an index problem is likely, before the slowness becomes critical.

• detecting tables and queries penalized by suboptimal access patterns (sequential scans, expensive sorts, disk spills),
• prioritizing objectively with a score rather than a subjective impression of slowness,
• avoiding "blind" over-indexing by telling real signals apart from false positives,
• tracking over time whether a table becomes healthy again after an action (VACUUM, index, query rewrite).

What the Index Tuning Advisor analyzes

The engine relies on two sources already historized by the collector: pg_stat_user_tables (each table's access and maintenance behavior) for the per-table score, and pg_stat_statements (real query cost) for the per-query detail panel.

For every table in the most recently analyzed snapshot (or a previously selected snapshot), PWR computes five independent risk factors, each capped, then adds them up to get an overall score out of 100:

• seq_scan / idx_scan — number of sequential scans vs index scans since the last statistics reset,
• n_live_tup / n_dead_tup — actual table volume and dead tuples awaiting cleanup,
• n_tup_ins / n_tup_upd / n_tup_del — write intensity (affects index maintenance cost),
• last_vacuum / last_autovacuum — freshness of the table's last maintenance,
• Goal: target tables that combine several signals (volume + scans + activity), not just the biggest table or the single slowest query.

f1_scan_ratio    = min(40, (seq_scan / (idx_scan + 1)) x 10)   -- sequential scan pressure
f2_volume        = min(20, n_live_tup / 100 000)                -- table volume
f3_activity       = min(20, (n_tup_ins + n_tup_upd + n_tup_del) / 50 000)  -- DML activity
f4_dead_tuples    = min(10, n_dead_tup / 50 000)                -- unreclaimed dead tuples
f5_maintenance    = (5 if last_vacuum missing) + (5 if last_autovacuum missing)

score = min(100, f1 + f2 + f3 + f4 + f5)
dominant_factor = the factor contributing the most to the score

Risk score and priority levels

The overall score (0 to 100) is then converted into a priority level the team can read at a glance, shown as a colored badge in the interface.

• The dashboard also shows, per schema, the average score and the maximum score of the tables it contains, to quickly spot the riskiest schemas.
• Every table keeps its scan_ratio (seq_scan / idx_scan) shown as-is, alongside the overall score, to directly visualize sequential scan pressure.

score <= 30            -> low       (low risk)
30 < score <= 60        -> medium    (medium risk)
60 < score <= 80        -> high      (high risk)
score > 80              -> critical  (critical risk)

Automatic per-table recommendations

Depending on which factors are triggered (above a threshold specific to each), PWR attaches one or more recommendation tags to each table, with a plain-language explanation:

• missing_index — "Likely missing index: review frequent filters and joins" (f1_scan_ratio ≥ 20),
• large_table — "Large table: prioritize queries that scan many rows" (f2_volume ≥ 10),
• composite_index — "High activity: a composite index may reduce unnecessary reads and writes" (f3_activity ≥ 10),
• vacuum_required — "High dead tuples: schedule a VACUUM and check autovacuum" (f4_dead_tuples ≥ 5),
• maintenance_missing — "Missing maintenance: check VACUUM and autovacuum for this table" (f5_maintenance ≥ 5),
• no_index_usage — "No index used on this snapshot despite sequential scans" (idx_scan = 0 and seq_scan > 0).

Complementary per-query analysis (pg_stat_statements)

In addition to the per-table score, each table in the table can be expanded to show the pg_stat_statements queries related to it, with their own alert signals, independent of the table's score:

• slow_query — average execution time ≥ 100 ms: "optimize the query and check its execution plan",
• temp_spill — temporary writes detected (temp_blks_written > 0): "evaluate work_mem for this workload",
• physical_reads — high physical reads (shared_blks_read ≥ 1000): "check indexes and selectivity",
• wal_volume — high WAL volume (≥ 100 MB cumulative): "reduce batch sizes or split transactions".

Example of reading a recommendation

Table public.orders, a typical e-commerce workload: frequent lookups by customer_id sorted by created_at.

• Finding: heavy sequential scan pressure on a large table, autovacuum never run.
• Proposal: create a composite index (customer_id, created_at DESC) to cover the filter and the sort in a single access, then check the autovacuum scheduling for this table.
• Caution: monitor the extra write cost on orders (n_tup_ins/upd/del already notable) before multiplying indexes.

seq_scan = 42 000   idx_scan = 1 200   -> f1_scan_ratio = min(40, (42000/1201)x10) = 40 (capped)
n_live_tup = 2 400 000               -> f2_volume     = min(20, 2400000/100000) = 20 (capped)
n_tup_ins+upd+del = 180 000          -> f3_activity   = min(20, 180000/50000)   = 3.6
n_dead_tup = 65 000                  -> f4_dead_tuples = min(10, 65000/50000)   = 1.3
last_autovacuum = NULL               -> f5_maintenance = 5

score = 40 + 20 + 3.6 + 1.3 + 5 = 69.9  -> HIGH level
dominant_factor = f1_scan_ratio
recommendations = [missing_index, large_table, maintenance_missing]

Recommended production workflow

The Index Tuning Advisor is a prioritization aid, not an autopilot: the workflow is still driven by the team.

• Analyze — open /index-advisor, sort by descending score and filter by schema or risk level to spot the top impact.
• Validate on the DBA side — cross-check the recommendation against the real data model (column cardinality, existing constraints, existing indexes).
• Test in pre-production — use EXPLAIN ANALYZE to verify that the new execution plan does use the proposed index, under a representative load.
• Deploy in a controlled way — prefer CREATE INDEX CONCURRENTLY to avoid locking the table in production, in a suitable window.
• Measure — take a new snapshot after a few days and compare the score, the scan_ratio and the recommendations for the same table.
• Iterate — keep, adjust or remove indexes that don't improve the score or that are no longer used (idx_scan close to zero).

Best practices

The score and the recommendation tags are an objective starting point; the final decision remains contextual.

• Always correlate the recommendation with the application context (a large, rarely queried table is not a priority, even with a high f2_volume score).
• Avoid over-indexing: every additional index slows down INSERT/UPDATE/DELETE and takes up disk space.
• Prefer indexes aligned with the actual filter and sort patterns being executed, visible in the table's queries panel.
• Periodically review tables flagged no_index_usage: an unused index deserves to be dropped, not just ignored.
• Handle vacuum_required / maintenance_missing recommendations before adding indexes: excess dead tuples skew both the score and the execution plans.

Limits to be aware of

The Index Tuning Advisor is a decision aid, not a replacement for DBA expertise:

• some slowness comes from a query that needs rewriting (SQL rewrite), not an additional index,
• some slowness comes from the PostgreSQL configuration (see the Tuning Advisor) or from the underlying infrastructure,
• an index that's relevant today may become useless after a functional change or a shift in query patterns,
• the per-table score depends on the available statistics window: a recent stats reset (pg_stat_reset) temporarily skews seq_scan/idx_scan.

Positioning within the SaaS solution

In the platform, the Index Tuning Advisor sits between observation (collecting and historizing statistics via the perfhist snapshots), decision (score and prioritization by table and by query) and continuous improvement (comparing the score between two snapshots after an action).

It complements the Tuning Advisor: the latter adjusts the PostgreSQL configuration (postgresql.conf), the Index Tuning Advisor adjusts the structure of tables and indexes — both rely on the same real workload history.

Measure (perfhist snapshots) -> Recommend (score + tags per table/query) -> Deploy (CREATE INDEX CONCURRENTLY, VACUUM) -> Verify (new snapshot) -> Improve (iterate)

Expected outcome for teams

The goal is not to replace technical review, but to make it faster and better targeted.

• less time lost on manual diagnosis of raw system views,
• optimizations prioritized by an objective score rather than a feeling of slowness,
• better DBA / Dev / Support collaboration around a shared list of at-risk tables,
• more stable and predictable production performance, with measurable before/after tracking.