The ROI of an Index: Why `hypopg` and `pg_stat_statements` Aren't Enough

TL;DR

• Your index advisor finds a Seq Scan in pg_stat_statements and suggests an index. This is a local optimization that ignores the global cost.
• Every index you add is a tax on INSERT, UPDATE, and DELETE. It amplifies write I/O, bloats your WAL, and creates more work for autovacuum.
• A correct solution models the full Return on Investment: the aggregate read-time saved across all queries vs. the aggregate write-time cost, storage, and actual planner adoption post-creation.

---

Years ago on the Oracle query engine team, we had a saying: the fastest query is the one you don't run. The second fastest is the one that uses an index. The problem is, everyone forgets that indexes aren't free.

I recently saw a team take down their write-heavy ingestion service by following the advice of a popular Postgres index advisor. The tool saw a few slow analytical queries and dutifully recommended three new multi-column indexes on their main events table. The EXPLAIN plans looked great. The queries were faster. But INSERT latency tripled, replication lag spiked, and their WAL disk filled twice as fast. They'd traded a minor annoyance for a production outage.

This is the seductive lie of modern index advisors. They treat your database like a read-only data warehouse. They don't understand that for most applications, a database is a living system where the cost of writing is just as, if not more, important than the cost of reading.

The Advisor's Playbook: A Local Optimum

Let's be clear about how these tools work. It's a simple, logical, and deeply flawed process.

1. Find Pain: They poll pg_stat_statements and sort by total_time or mean_time.
2. Identify Waste: They look for queries with high execution counts that use a Seq Scan operator. This is the low-hanging fruit.
3. Hypothesize a Fix: They parse the query's WHERE clause and suggest an index on the filtered columns. Some are more advanced, using extensions like hypopg to create a hypothetical index without paying the upfront build cost.
4. Validate the Fix: They run EXPLAIN with the hypothetical index and check if the planner swaps the Seq Scan for an Index Scan or Bitmap Heap Scan, and if the estimated cost goes down.

Looks good on paper. You see the cost in an EXPLAIN plan drop from 1,200,000 to 8.5, and you commit the change. The problem is, that cost number is a unitless estimate of read-side work. It completely ignores the write-side.

The Hidden Tax: Write Amplification and Your WAL

When you INSERT a row into a table with five indexes, you are not doing one write. You are doing at least six.

1. The row is written to the table's heap file.
2. A new key is added to the B-tree for index_1.
3. A new key is added to the B-tree for index_2.
4. A new key is added to the B-tree for index_3.
5. A new key is added to the B-tree for index_4.
6. A new key is added to the B-tree for index_5.

Each of these B-tree insertions might involve traversing several pages from the root to a leaf and potentially splitting a page, which is even more I/O. Crucially, every single one of these page modifications must be logged to the Write-Ahead Log (WAL). This is how Postgres guarantees durability and enables replication.

Your single-row INSERT statement just generated a storm of WAL traffic. This isn't theoretical. If your table gets 1,000 writes per second, adding those three "helpful" indexes doesn't just add a few microseconds to each write. It can be the difference between your primary and replica staying in sync and the replica falling hours behind because it can't keep up with the firehose of WAL records.

Let's run the numbers on a plausible scenario. Your advisor wants to speed up a reporting query that runs twice a day. It's slow, taking 30 seconds. An index brings it down to 100ms.

• Read Savings: ~60 seconds per day.

But the table it's on powers your application's real-time activity feed. It gets 200 inserts per second. Let's say our new index adds just 0.2ms of overhead to each insert (a very conservative estimate).

• Write Cost: 200 inserts/sec * 86,400 sec/day * 0.0002 sec/insert = 3456 seconds per day.

We've added nearly an hour of cumulative write latency to the system to save one minute of read latency. The ROI is catastrophically negative. No standard index advisor even attempts this math. They don't correlate DML stats from pg_stat_user_tables (n_tup_ins, n_tup_upd) with the read performance gains from pg_stat_statements.

-- A query that looks ripe for optimization.
-- Standard advisors will jump on this.
EXPLAIN ANALYZE SELECT * FROM user_events WHERE user_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6' AND event_type = 'login';

----------------------------------------------------------------------------------------------------------------------
-- Query Plan
----------------------------------------------------------------------------------------------------------------------
-- Seq Scan on user_events  (cost=0.00..45015.00 rows=10 width=128) (actual time=0.025..350.123 rows=15 loops=1)
--   Filter: ((user_id = 'a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6'::uuid) AND (event_type = 'login'::text))
--   Rows Removed by Filter: 2,000,000
-- Planning Time: 0.150 ms
-- Execution Time: 350.180 ms

The advisor sees this, suggests CREATE INDEX ON user_events (user_id, event_type), the plan flips to a fast Index Scan, and the engineer approves it, blind to the 5,000 writes/sec hitting this table.

The Fickle Planner and Post-Merge Reality

The second failure is not tracking adoption. hypopg is a great tool, but it only tells you what the planner might do in a sterile, hypothetical environment. The real world is messy.

The planner's choice to use an index depends on the current table statistics (retrieved by autovacuum/ANALYZE), the cardinality of the values in your WHERE clause, and runtime parameters like work_mem.

An index on status might be great for WHERE status = 'archived' (0.01% of rows) but useless for WHERE status = 'active' (95% of rows), where a Seq Scan is legitimately faster. Does your advisor understand the distribution of values in your workload? Or does it just test one EXPLAIN and call it a day?

A proper system doesn't just recommend an index; it verifies that the index is actually being used by the target queries in production after it's created. I've seen countless indexes get added, only to be ignored by the planner a week later after table stats shift. These 'zombie' indexes provide zero read benefit but continue to charge their full write tax, forever. They are pure overhead.

This isn't just a Postgres problem. In MySQL, the sys schema has views for unused indexes, but it's a lagging indicator. It tells you what hasn't been used recently, but it doesn't help you make a good decision in the first place. You need a system that models the full cost/benefit trade-off before you type CREATE INDEX.

What DeepSQL does about this

We built DeepSQL to fix this. It doesn't just scan for slow queries. It builds a complete economic model of your database workload. We ingest query and table statistics to calculate a proper ROI for every potential index, balancing read gains against write costs. We model the breadth of impact – whether an index will help one query or a thousand related patterns. And crucially, after a recommendation is implemented, our system continuously monitors planner behavior to confirm the index is being adopted and delivering value. If it's not, we tell you to drop it.