Test of 179 recommended Postgres indexes shows 18% made queries worse

The test index advisors were missing

Prateek Arora, creator of PgLens, an open-source index advisor for PostgreSQL, published on Planet PostgreSQL an uncomfortable experiment for anyone who makes a living recommending indexes: of 179 recommendations already approved by the database's own planner, 32 made the real query slower, and seven even doubled (or more) the execution time.

The starting point is technical and specific. Like most index advisors on the market, PgLens uses the HypoPG extension to create a hypothetical index, run EXPLAIN again, and compare the estimated cost before and after. If the estimated cost drops by at least 15%, the recommendation is approved and shown to the user as a gain. Arora wanted to know how much that number is actually worth in practice, so he decided to really build each suggested index and time the query end to end.

Methodology: Join Order Benchmark, 7 GB of real data

For the test, he used the Join Order Benchmark (JOB): 113 queries over the real IMDB dataset, totaling 7 GB, running on PostgreSQL 16 with hypopg 1.4. The JOB was designed precisely to expose cases where the optimizer misestimates the number of rows returned by a join, which makes it a tough test for any advisor based on estimated cost rather than real measurement.

PgLens generated 214 recommendations validated by the planner for the benchmark's queries, but these recommendations point to just 10 distinct indexes: a single foreign-key index, for example, serves as the solution for several different queries. Before running the test, Arora fixed the success criterion: 15% faster is a win, 5% slower is a loss. Setting that bar before looking at the results is what gives the experiment credibility, and that matters to any DBA evaluating a third-party tool.

The numbers: wins, losses, and neutral results

Of the 179 indexes actually measured, 128 delivered on the promise: the query became at least 15% faster. But 32 (18% of the total) made the real time worse by 5% or more, and seven of them more than doubled the execution time. Doing the math, the remaining 19 landed in a neutral range: neither the minimum 15% gain nor the 5% loss that would count as a defeat.

Beyond these 179, another 34 recommendations couldn't even be tested, because the suggested index simply couldn't be built on the real database. All 34 of these point to the same index, the second-ranked suggestion in PgLens's list for these queries: movie_info(info).

The worst case: from common sense to disaster

The worst result in the batch was query 10c, with an index suggested on cast_info(movie_id). The planner estimated a 91% cost reduction. Measured for real, the time went from 0.93 s to 7.3 s. Arora ran it again manually to double-check, and the pattern repeated: about 1 s became about 9 s.

The cause is a classic for anyone who reads execution plans every day. With the index in place, the planner chose a nested loop that queries cast_info once for each row of an earlier join. The problem is that it underestimated how many rows that join would return, and the loop ran far more times than the plan predicted: the query read 71 times more buffers than it should have. HypoPG doesn't detect this kind of error because it consults the same planner, with the same wrong row estimate that already existed before the index.

Arora sums up the problem directly:

Every advisor built on hypothetical indexes has this blind spot, mine included.

Prateek Arora, creator of PgLens

For anyone who administers a database in production, the lesson isn't new, but the number is a valuable reminder: an index doesn't fix a wrong cardinality estimate, and it can sometimes make the plan worse by opening the door to a bad join strategy.

The 34 that never left the drawing board: a physical limit of the B-tree

The second problem is structural, not a matter of estimation. When trying to build the index on movie_info(info), Postgres refused the operation:

ERROR: index row requires 9392 bytes, maximum size is 8191

Of the 14.8 million values in that column, 1,182 exceed the maximum size a B-tree entry accepts. A hypothetical index never writes a real entry, so HypoPG had no way to predict this failure: it simulates cost, it doesn't write data. Any advisor that stops at simulation, without trying a real CREATE INDEX (or at least validating the size of the values), will recommend something the database rejects at the moment of truth.

What the planner gets right: ranking, not magnitude

Not everything went wrong, and this is the most useful point of the experiment for anyone evaluating a recommendation tool. The index that PgLens ranked #1 saved 364 of the 424 total seconds spent by the queries it applied to. Looking at the whole set, the order of indexes by estimated savings was close to the order by measured savings (Spearman correlation of 0.83).

What didn't hold up was the isolated percentage per query. Knowing that "this index cuts 91% of the cost" said almost nothing about the real gain for that specific query. In other words: asking "which index matters most?" is a question the planner answers reasonably well; asking "how much will this query gain?" is not.

Changes Arora made to PgLens after the test

With the results in hand, Arora changed the tool's behavior in four ways:

• Every number coming from the planner is now labeled as an estimate, never shown as a guaranteed "speed gain."
• The pglens confirm command builds the index on a copy of the database and times the user's real queries, before and after, without touching the production environment.
• After the index is actually created, PgLens now shows the measured time for each query, before and after, instead of just the cost estimate.
• The tool now warns when a column may contain values too large to fit in a B-tree entry, before suggesting the index.

What this means for anyone running Postgres in production

Arora's experiment doesn't invalidate the use of index advisors, but it draws a clear line on where to trust them. Relative ranking among index candidates tends to be reliable; an isolated gain percentage per query is not. That distinction matters more than any marketing number from a third-party tool.

In short: before applying any index suggestion, whether from an automated advisor or from the team's intuition, it's worth repeating the path the experiment describes: actually run EXPLAIN (ANALYZE, BUFFERS) on a copy of the data, time the query with and without the index, and only then decide. Blindly trusting the estimated cost of a hypothetical plan, without measuring real execution time, means giving up exactly what separates a careful DBA from someone who just follows a tool's recipe.

It's also worth noting the second, less discussed type of risk: the compatibility of the data with the index structure. Before creating a B-tree index on a free-text or JSON column, it's prudent to check the size distribution of the values, because the 8,191-byte-per-entry limit is a physical constraint of Postgres, not a suggestion.

PgLens is open source (Apache-2.0 license), runs locally (self-hosted), and, according to the author, only reads the database, never writing to it without explicit authorization from the confirm command. The project is at github.com/Prateek-Arora/pglens, still a release candidate, with the benchmark method, tables, and scripts documented in docs/benchmarks.md and reproducible via make accuracy-job. For anyone who wants to challenge the numbers, Arora has an invitation: run pglens confirm on your own database and compare.