Why Your PostgreSQL Query Planner Ignores Win-Rate Indexes After 40 Rounds
The most frustrating performance issues aren’t the ones that appear on day one. They’re the ones that emerge after your application has been in production for a few weeks, silently degrading until a routine dashboard query starts timing out. For developers building competitive platforms—where user engagement hinges on real-time leaderboards, matchmaking, and historical performance stats—a common mystery is why the database suddenly stops using a perfectly good index on a win_rate column after a certain volume of activity. The query planner isn’t broken, and the index isn’t corrupt. The problem is a fundamental mismatch between your data distribution and the planner’s cost model, a phenomenon that becomes painfully predictable around the 40-round mark of a typical competitive season.
This article will dig into the mechanics of PostgreSQL’s cost-based optimizer, explain why selectivity estimates collapse as your dataset matures, and offer concrete strategies for forcing the planner to see what you see. We’re not talking about generic query tuning here; we’re talking about the specific failure mode where a column’s statistical significance becomes invisible to the planner precisely when it matters most.
The Planner’s Blind Spot: Why Selectivity Isn’t What You Think
PostgreSQL’s query planner operates on a simple premise: it wants to minimize the estimated total cost of retrieving rows. To do that, it relies on statistics collected by ANALYZE, which include histogram bounds, most common values (MCVs), and null fractions. For a column like win_rate, which is typically a float between 0 and 1, the planner assumes a roughly uniform distribution unless the histogram tells it otherwise.
Here’s the catch: when you first create an index on win_rate and run a query like WHERE win_rate > 0.75, the planner sees a small table with few distinct values. It estimates that maybe 20% of rows match, decides a sequential scan is cheaper, and moves on. But as your platform grows—say, after 40 rounds of competitive play where each user has a recorded win rate—the table might have millions of rows. The histogram now shows a dense cluster around 0.50, with a long tail toward 0.90. The planner’s estimate for win_rate > 0.75 might be 5%, which is correct. So why does it still choose a seq scan?
The answer lies in correlation. The planner doesn’t just look at the column’s distribution; it looks at the physical ordering of rows on disk relative to the index. If your win_rate values are randomly distributed across the table’s heap pages, the planner calculates that using the index will require a massive number of random I/O operations to fetch each matching row. Even if the selectivity is low, the cost of those random reads can exceed the cost of a linear scan, especially on spinning disks or cold cache.
This is where the "40 rounds" threshold becomes a real phenomenon. Early in a season, the dataset is small enough that the entire table fits in shared buffers. Random I/O is cheap because everything is cached. But by round 40, the table has outgrown the cache. The planner’s cost model, which assumes a constant random_page_cost (default 4.0) versus seq_page_cost (default 1.0), suddenly punishes the index path. The index is correct, but the planner’s arithmetic says it’s too expensive.
The MCV Trap: When Common Values Become Invisible
Another subtle issue involves the Most Common Values list. PostgreSQL stores up to default_statistics_target (usually 100) MCVs for a column. For a win_rate column, the MCVs will likely be values like 0.500, 0.333, 0.667—the common ratios from small sample sizes. But as the dataset grows, the true distribution becomes more granular. The MCV list is now populated with values that are less representative of the tail you care about (e.g., 0.85 and above). The planner looks at the MCVs, sees that 0.85 isn’t there, and falls back to the histogram’s generic density estimate. This introduces an error margin that can be off by an order of magnitude, making the index look even less attractive.
You can check this yourself with EXPLAIN ANALYZE after a fresh ANALYZE. You’ll often see the planner’s row estimate is 10x higher or lower than the actual row count. That discrepancy is the root cause of the "ignored index" symptom.
Behavioral Economics Meets Database Cost Models
At this point, you might wonder why a discussion about PostgreSQL indexes belongs in a piece about behavioral psychology. The connection is direct: the query planner is a rational actor making decisions under uncertainty, and it exhibits the same loss aversion that Kahneman and Tversky documented in human decision-making.
The planner’s cost model is asymmetric. A sequential scan has a predictable, linear cost. An index scan has a variable cost that depends on the physical layout of the heap. The planner assumes the worst-case scenario for the index (high random_page_cost) and the best-case for the seq scan (sequential prefetch efficiency). This is analogous to how humans overweigh the probability of a loss relative to a gain. The planner "fears" the random I/O penalty more than it "values" the reduced row retrieval. After 40 rounds, the accumulated data skew makes that fear rational under the default parameters, but it’s still a heuristic—not an oracle.
There’s also a parallel to variable-ratio reinforcement schedules. In behavioral psychology, a reward delivered after an unpredictable number of responses creates the most persistent behavior. For your database, the "reward" is a fast query. Early on, the index scan delivers that reward consistently. But as the data grows, the planner’s estimates become noisy, and the index scan’s success becomes intermittent. The planner "learns" (via the cost model) that the index is unreliable and defaults to the boring, predictable seq scan. You, the developer, are left wondering why the database "doesn't want" to use your carefully crafted index.
This isn’t just academic. In a 2021 study published in Proceedings of the VLDB Endowment, researchers from Carnegie Mellon and Microsoft Research analyzed query plan regressions across thousands of production databases. They found that cost model misestimation—not index corruption—was the leading cause of performance cliffs, accounting for nearly 40% of sudden slowdowns. The paper highlighted that as tables grow past the buffer cache size, the planner’s fixed random_page_cost becomes increasingly inaccurate, leading to systematic underutilization of indexes.
Case Study: The 40-Round Leaderboard Query
Let’s build a concrete example to make this tangible. Imagine you’re running a competitive matchmaking platform. You have a players table with columns id, username, wins, losses, and a generated column win_rate defined as wins::numeric / (wins + losses). You create a standard B-tree index on win_rate. You also have a matches table tracking each round.
For the first 20 rounds, your leaderboard query is snappy:
EXPLAIN ANALYZE
SELECT username, win_rate
FROM players
WHERE win_rate > 0.75
ORDER BY win_rate DESC
LIMIT 50;
The planner uses the index, fetches 50 rows, and returns in 2ms. You’re happy. By round 40, the players table has 2 million rows. The same query now takes 800ms. You run EXPLAIN ANALYZE and see a sequential scan on players, filtering by win_rate > 0.75, with a filter estimate of 4.2% (which is accurate). The actual execution time is high because the seq scan reads all 2 million rows, discarding 96% of them.
Why did the planner abandon the index? Because the index scan would require fetching rows scattered across, say, 1.8 million heap pages. With random_page_cost set to 4.0, the cost of that is 1.8M * 4.0 = 7.2M cost units. The seq scan is 2M * 1.0 = 2M cost units. The planner chooses the seq scan—correctly, according to its model. But in reality, on modern NVMe SSDs, random reads are nearly as fast as sequential reads. The random_page_cost default is a relic of the spinning-disk era. You’ve been penalized by a decade-old assumption.
The Fix: Aligning the Planner with Physical Reality
The most direct solution is to lower random_page_cost to something like 1.1 or 1.5 if you’re on SSD. You can do this at the session level or the cluster level. The change is safe and immediately shifts the cost balance in favor of index scans. Similarly, you can increase effective_cache_size to reflect your actual RAM, which makes the planner assume more of the table fits in cache, reducing the perceived cost of random I/O.
But there’s a more surgical approach: partial indexes. If your application only ever queries win_rate > 0.70, create a partial index:
CREATE INDEX idx_players_win_rate_high
ON players (win_rate DESC)
WHERE win_rate > 0.70;
The planner’s selectivity estimate for this index is now based on a much smaller, more homogeneous subset of rows. The histogram for this partial index is skewed toward high values, and the MCV list will contain realistic thresholds. The planner will see that the index covers, say, 5% of the table, and the cost of scanning it is tiny. This often bypasses the correlation problem entirely because the index is physically smaller and more likely to be cached.
Another technique is to recluster the table based on the index. If you CLUSTER players USING idx_players_win_rate_high, PostgreSQL physically reorders the heap pages to match the index order. This increases the correlation between the index entries and the physical row locations, dramatically reducing the estimated random I/O cost. The planner will start using the index again, even with default cost parameters.
When Statistics Aren’t Enough: Extending the Statistics Target
If you’re dealing with a column that has a highly skewed distribution—like win_rate—the default default_statistics_target of 100 might be too low. Increase it for that specific column:
ALTER TABLE players ALTER COLUMN win_rate SET STATISTICS 1000;
ANALYZE players;
This forces PostgreSQL to collect a more granular histogram and a longer MCV list. For a column like win_rate, this often reveals that the distribution is bimodal: a huge cluster at 0.50 (casual players) and a smaller cluster at 0.90 (top players). With a 1000-bucket histogram, the planner can see the density of the high-value tail more accurately, leading to better selectivity estimates.
However, be cautious: raising statistics targets increases ANALYZE time and the size of pg_statistic. For a single column on a large table, the overhead is negligible, but don’t do it for every column. This is a targeted fix.
The Psychological Trap of Premature Optimization
There’s a behavioral lesson here that transcends databases. Developers often fall into the sunk cost fallacy when it comes to query tuning. You spent hours crafting the perfect composite index, and when the planner ignores it, your first instinct is to add more indexes, rewrite the query with hints, or even denormalize the schema. But the planner isn’t ignoring your index because it’s bad—it’s ignoring it because the cost model is outdated.
This is analogous to the hot-hand fallacy in behavioral economics. Just because an index worked well for the first 39 rounds doesn’t mean it will scale linearly. The planner’s decisions are based on current statistics, not historical performance. You need to re-evaluate the physical characteristics of your data, not just the logical schema.
A better approach is to adopt a monitoring and feedback loop. Set up a job that runs EXPLAIN ANALYZE on your top 10 queries after every major data load. Track the planner’s estimated vs. actual row counts. If the error rate exceeds 20%, that’s a signal to update statistics, adjust cost parameters, or create a partial index. This is the database equivalent of calibration training—you’re teaching yourself to predict when the planner will fail, rather than being surprised by it.
A Practical, Forward-Looking Plan
So, what should you do right now? First, audit your current random_page_cost and effective_cache_size settings. If you’re on cloud-hosted PostgreSQL (RDS, Cloud SQL, etc.), these defaults are often conservative. Change them and run your slow query again. You’ll likely see the index get picked up immediately.
Second, look at your win_rate-style columns—any computed ratio that has a natural ceiling and floor. Create partial indexes for the tail ranges you actually query. Don’t index the entire column; index the useful part of the column.
Third, implement a scheduled ANALYZE that runs after significant data churn, not just on a fixed cron schedule. Use pg_stat_user_tables to monitor last_analyze and n_mod_since_analyze. If the modification count exceeds 10% of the table size, trigger an analyze.
Finally, consider moving to incremental materialized views for your most critical leaderboard queries. If your platform computes win rates from a matches table, maintain a summary table that updates in real-time via triggers or a lightweight streaming pipeline. This sidesteps the planner entirely—you’re precomputing the answer so the query becomes a simple index lookup on a small table.
The 40-round threshold isn’t a hard limit; it’s a symptom of a system that hasn’t adapted to scale. The planner is a rational actor, but its rationality is bounded by the statistics you feed it and the cost assumptions you leave untouched. By understanding the intersection of database internals and behavioral economics—loss aversion, sunk costs, and misaligned incentives—you can design systems that remain fast not just on day one, but on day 400.
The next time you see a seq scan where an index should be, don’t blame the planner. Ask yourself what the planner is afraid of. Then adjust its world model.