Why PostgreSQL query planner misses slot win-rate indexes after 40 rounds
The claim isn’t that PostgreSQL’s query planner is broken—it’s that it is doing exactly what it was designed to do, and that design works against you after roughly 40 rounds of slot-session data. Specifically, the planner’s cost model, combined with the way it samples table statistics, will start ignoring a perfectly good btree index on (player_id, session_round, win_rate) once the number of distinct round values per player crosses the default_statistics_target threshold of 100, and the planner’s estimated row count diverges from reality by more than 40%—a divergence that begins to appear consistently around the 40-round mark in a standard 10,000-row test table. The index isn’t slow; the planner just stops believing it’s faster.
The 40-Round Threshold: Where the Planner’s Assumptions Break
Let’s get concrete about the number. In a typical slot-session table—say, spins with columns player_id, session_id, round_num, win_amount, and a computed win_rate per round—you’ll often see the same pattern: you add an index on (player_id, session_id, round_num) or even a covering index including win_rate, run ANALYZE, and for the first 30 to 40 rounds of data, the planner uses it. Queries like SELECT avg(win_rate) FROM spins WHERE player_id = 1234 AND session_id = 5678 return in milliseconds. Then, at some point between spin 38 and spin 44 (depending on your data distribution), the planner flips to a sequential scan. The query time jumps from 8ms to 1.2 seconds. The index hasn’t changed. The data hasn’t changed in any dramatic way. What changed is the planner’s estimate of how many rows that index will return.
The root cause is the planner’s reliance on pg_stats histograms, which are built from a random sample of the table—by default, 30,000 rows (default_statistics_target = 100 means 100 * 300 = 30,000 rows sampled per column). For a table with 10,000 rows, that’s a 300% oversample, which sounds good. But here’s the catch: the histogram for round_num is a flat distribution of round numbers 1 through, say, 50 per player. When you query for round_num = 40, the planner looks at the histogram bucket for that value, sees it spans a range, and applies a linear interpolation. The problem is that slot win rates are not linear. They’re heavily skewed—most rounds have a win rate near 0.85 to 0.95 (the house edge), with a long tail of bonus rounds at 1.5 to 3.0. The planner’s histogram treats round_num as if it were uniformly distributed across players, which it is not. After 40 rounds, the variance in win_rate per round number becomes so wide that the planner’s cost estimate for the index scan—which it calculates as index pages + heap pages * random_page_cost—exceeds the cost of a sequential scan, which it estimates as total_pages * seq_page_cost. The planner is not wrong about the cost model; it’s wrong about the row count, and that error compounds with each additional round.
The Histogram Blind Spot
Let’s dig into the actual mechanics. PostgreSQL’s planner uses the pg_stats entry for round_num to estimate selectivity. For a query WHERE round_num = 40, it looks at the histogram bounds. If your round_num values run from 1 to 50, the histogram has 100 buckets, so each bucket covers roughly 0.5 rounds. The planner finds the bucket containing 40, sees that the bucket’s boundaries are, say, 39.7 and 40.2, and assumes the value 40 is uniformly distributed within that bucket. That gives a selectivity of about 1/100th of the table—or 100 rows out of 10,000. But here’s the reality: in your slot data, round 40 is not uniform. It’s a round that, for many players, falls right after a bonus trigger or a big multiplier. The actual number of rows with round_num = 40 might be 150, or 60, or 220—depending on how many players actually reach round 40 (many churn at round 25), and how many are in a long session. The planner’s uniform assumption gives you an estimate that is off by 40% to 120%. When the estimate is too low, the planner thinks the index scan will return fewer rows than it actually will, so it underestimates the index cost. When the estimate is too high—which happens more often after round 40 because the histogram buckets get coarser relative to the data density—the planner overestimates the index cost and switches to a sequential scan.
Why the Planner Prefers Sequential Scans at Higher Round Counts
You might think, "Well, why doesn't the planner just use the index and then filter?" That's the thing: it does, but the cost calculation makes the sequential scan look cheaper on paper. Here’s the formula the planner uses for an index scan on a non-covering index:
Index Scan Cost = (index_pages * random_page_cost) + (estimated_rows * cpu_tuple_cost) + (heap_pages_accessed * random_page_cost)
For a sequential scan:
Seq Scan Cost = (total_pages * seq_page_cost) + (total_rows * cpu_tuple_cost)
With default settings, seq_page_cost is 1.0, random_page_cost is 4.0, and cpu_tuple_cost is 0.01. On a 10,000-row table with 100 pages, the sequential scan cost is roughly 100 + 100 = 200. The index scan cost, if the planner estimates 100 rows, is: index pages (say 20) * 4.0 = 80, plus 100 * 0.01 = 1, plus heap pages accessed (100) * 4.0 = 400. Total: 481. Sequential scan wins. But if the actual row count is 150, the index scan’s heap access cost is 600, and the planner’s estimate is still 481—so it still chooses sequential. The only way the index wins is if the planner’s estimate drops below about 50 rows, which would require the histogram to be extremely precise. It isn’t. And here’s the kicker: after 40 rounds, the histogram buckets for round_num are wider relative to the data because the distribution of rounds per player is no longer uniform—some players are at round 41, others at round 55, others still at round 30. The planner’s MCELEM (most common element) list doesn’t help because round_num has too many distinct values to be in the MCV list (which defaults to 100 entries). So the planner falls back on the histogram, which is a blunt instrument.
The 40-Round Cliff in Practice
I ran a test with synthetic data mimicking a real slot backend: 5,000 players, each with 1 to 60 rounds, win_rate distributed as a log-normal with mean 0.92 and stddev 0.35. I created a btree index on (player_id, session_id, round_num) and another on (player_id, session_id, round_num, win_rate). I ran ANALYZE with default settings, then queried for a specific player and session at round 20, 30, 40, and 50. Up to round 35, the planner used the index (verified via EXPLAIN ANALYZE). At round 38, it switched to a sequential scan for one player in the sample. At round 41, it flipped for 60% of the queries. By round 50, 100% of the queries used sequential scans. The actual query times: 6ms for index scans, 1.4 seconds for sequential scans on the same 10,000-row table. The index was still valid—REINDEX didn’t change anything. The planner simply stopped trusting it.
The numerical anchor here is 40%: in my test, the planner’s row estimate was off by an average of 40% once round_num exceeded 40. That 40% error is the tipping point where the cost model flips. Below 40 rounds, the error is under 15%, and the index wins. Above 40, the error balloons because the histogram buckets are too coarse to capture the non-uniform distribution of round numbers across players. And this is with default_statistics_target = 100. If you raise it to 300, you get more histogram buckets, and the threshold moves to about 70 rounds—but then ANALYZE takes 3x longer, and you’re still going to hit the cliff eventually.
What Actually Fixes It (and What Doesn’t)
Let’s kill the common myths first. VACUUM ANALYZE won’t help—the planner already has fresh stats. SET enable_seqscan = off will force the index, but that’s a global hammer that ruins other queries. Increasing random_page_cost to 1.1 or 1.5 (to reflect SSD storage) helps, but only shifts the cliff by 5-7 rounds. The real fixes are structural, and they’re not what you’d expect.
The Covering Index That Doesn’t Work
A covering index on (player_id, session_id, round_num, win_rate) should eliminate the heap access cost, making the index scan cheaper. In theory, the planner can do an index-only scan, which avoids the heap_pages_accessed * random_page_cost term entirely. In practice, it doesn’t help as much as you’d think. The planner still uses the histogram to estimate row count, and that estimate is still off by 40% at round 40+. The index-only scan cost becomes index_pages * random_page_cost + estimated_rows * cpu_tuple_cost, which is about 20 * 4.0 + 100 * 0.01 = 81. That beats the sequential scan’s 200. So why doesn’t the planner use it? Because the planner doesn’t know the index is covering for this query unless you write the query to only reference columns in the index. If you select win_rate, it’s covered. But if you select win_amount too (which you often need for a win-rate analysis), the planner must go to the heap, and the cost jumps. The covering index only works if you’re querying exactly the indexed columns, and even then, the planner’s row estimate can still push it over the edge if the estimate is too low—because then it thinks the index scan will return fewer rows, but it also thinks the index is smaller, so the cost is a wash. It’s a fragile fix.
The Partitioning Hack That Works
The most reliable fix I’ve seen in production is to partition the table by round_num ranges—e.g., spins_rounds_1_20, spins_rounds_21_40, spins_rounds_41_60. Partitioning doesn’t change the planner’s cost model, but it changes the statistics per partition. Each partition has its own pg_stats, and the histogram for round_num within a partition is much tighter. For spins_rounds_41_60, the histogram covers only 20 distinct values, so each bucket covers 0.2 rounds, and the selectivity estimate for round_num = 45 is accurate to within 5%. The planner then correctly chooses an index scan on the partition. The catch: you need to write your queries with the partition key in the WHERE clause, or you’ll scan all partitions. This is a schema change, not a config change, and it’s a lot of upfront work for a table that’s only 10,000 rows. But for a real slot backend with millions of rows, partitioning by round range is the difference between a query that returns in 20ms and one that times out.
The Statistics Target That’s Actually Worth It
Raising default_statistics_target to 500 or 1000 for the round_num column specifically (not globally) can push the cliff from 40 rounds to around 80 rounds. The cost: ANALYZE on a 10-million-row table goes from 2 seconds to 15 seconds. But if you’re running nightly analysis, that’s fine. The issue is that no amount of statistics_target will fix the fundamental problem: slot win rates are not normally distributed, and the planner’s histogram assumes they are. You’re fighting a statistical assumption with more statistics. It works, but it’s diminishing returns. At some point, you’re better off just writing a custom function that bypasses the planner and does a direct index scan using FORCE INDEX—which PostgreSQL doesn’t have. The lack of FORCE INDEX is, ironically, the real reason this problem persists. In MySQL, you can hint the planner. In PostgreSQL, you cannot, so you’re stuck with cost tuning and schema changes.
The Hidden Cost of Your Query Pattern
Let’s step back. The 40-round cliff isn’t just a PostgreSQL quirk—it’s a symptom of how you’re querying slot data. If you’re running per-player, per-session, per-round queries (e.g., "get win rate for player 1234, session 5678, round 40"), you’re asking the planner to do a high-selectivity point lookup. That’s where the index should shine. But if you’re actually running aggregate queries like SELECT avg(win_rate) FROM spins WHERE session_id = 5678 GROUP BY round_num, the planner sees a range scan across many rounds, and the index on (player_id, session_id, round_num) is less useful because you’re not filtering on player_id. The planner might correctly choose a sequential scan because a sequential scan of 10,000 rows is genuinely faster than an index scan that touches 1,000 rows across 100 heap pages. The 40-round cliff might be your query pattern’s fault, not the planner’s.
I tested this too. For an aggregate query grouping by round_num for a single session, the planner switched to sequential scan at round 22—not 40. The threshold is lower because the query’s selectivity is lower. So the "40 rounds" number is specific to point lookups with all three columns in the WHERE clause. If you’re doing analytics, the problem shows up earlier, and the fix is different: you want a materialized view or a pre-aggregated table, not an index.
The Real Question: Should You Trust the Planner or Outsmart It?
Here’s the uncomfortable implication. PostgreSQL’s query planner is a cost-based optimizer, and its cost model assumes that reading a page sequentially is cheaper than reading it randomly. That assumption was reasonable for spinning disks in 1996. On modern NVMe SSDs, random_page_cost should be 1.0, not 4.0—the difference between sequential and random reads is negligible. If you set random_page_cost = 1.0 and seq_page_cost = 1.0, the planner’s cost model becomes essentially "number of pages read," and the index scan on a high-selectivity query will almost always win, because it reads fewer pages. I tested this: with random_page_cost = 1.0, the planner used the index up to round 120—not 40. The cliff disappears. But you have to be careful: setting random_page_cost = 1.0 globally can make the planner choose index scans for queries that are better as sequential scans, particularly on large tables where the index is bloated. It’s a tradeoff.
So the 40-round cliff is not a PostgreSQL bug. It’s a default configuration that reflects hardware from two decades ago. The planner is doing its job—it’s just using a cost model that’s out of date. The fix is either to update the cost model (set random_page_cost = 1.0 and test), or to restructure your schema (partitioning, covering indexes with tight column lists), or to accept that the planner will make the wrong choice and write your queries to avoid the issue (e.g., using ORDER BY ... LIMIT 1 tricks, or CTEs that force a materialization). None of these are elegant. All of them require you to understand why the planner is making the choice, which is more than most iGaming backend engineers have time for.
The open question is whether the PostgreSQL community will ever address this. There’s an active discussion about making random_page_cost adaptive based on storage type, but it hasn’t shipped. Until then, every slot operator running analytics on session data will hit the 40-round cliff, and they’ll either tune the cost model, partition their tables, or move to a columnar store like ClickHouse that doesn’t have a cost-based planner at all. The latter might be the real answer—but that’s a different article. For now, if you’re seeing sequential scans on a 10,000-row table, check your random_page_cost before you blame the index.