XplainDB: Constraint-Driven Index Optimisation and Evidential Reporting for PostgreSQL

Indexes can reduce query-processing work but consume storage and add maintenance when rows change. Existing index advisors can recommend portfolios without exposing how query priorities and resource limits shaped the choice, whether the solver proved its result, or whether estimated benefits persisted after materialisation. This paper proposes XplainDB, a PostgreSQL index advisor that records this evidence. It evaluates candidate B-tree indexes using PostgreSQL and HypoPG. Three stages minimise estimated workload cost within an index allowance, seek a smaller portfolio within an estimated-cost margin, and minimise cost at that size. Evaluation used TPC Benchmark H (TPC-H), TPC Benchmark DS (TPC-DS), the Join Order Benchmark, three strategy-inspired baselines, runtime tests, and data-modification tests. On TPC-H, three XplainDB indexes reduced estimated workload cost by 99.86%, within 0.01 percentage points of the highest five-index strategy result. On TPC-DS, priority-aware selection increased priority-weighted utility from 57.69% to 61.79%. A 15% estimated-cost margin reduced the TPC-H portfolio from five indexes to two while estimated improvement decreased by 0.0204 percentage points. The materialised priority-aware TPC-H portfolio reduced summed comparable per-query median runtime by 20.76%. XplainDB records each selected index’s estimated contribution, affected queries, alternatives, active constraints, and solver certainty. An optional local language model explains recorded evidence without changing the recommendation. The study contributes a transparent, evidence-aligned approach to index selection, while offering a reproducible foundation for explainable and priority-aware index advisors.