Why is my Whale Query 1000x Slower in Prod?

The harmless whale query that Will Eat You

Here's the entire setup. one prompt to my AI assistant:

-- Schema
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT, tier TEXT);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total NUMERIC);
CREATE TABLE order_items (id INT PRIMARY KEY, order_id INT, quantity INT, unit_price NUMERIC);

SELECT c.tier, COUNT(*), SUM(oi.quantity * oi.unit_price)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.tier = 'enterprise'
GROUP BY c.tier

That's the whole input. Three table declarations, one query, one sentence describing the skew, one row-count target. No INSERT scripts, no choice of indexes, no DDL for either engine, no plan-hint guidance, no "try a hash join first." Everything else, generating 500,000 orders at exactly an 80/20 fan-out, picking indexes, materializing the schema on Postgres and SQL Server, capturing the plans, was filled in by the AI calling ExoBench behind the scenes.

Nothing exotic about the query. Three tables, two joins, one GROUP BY. The kind of query you'd write without thinking and expect to be boring. On uniform test data it runs in about a millisecond, so you ship it.

Then it runs ~1,000x slower in production, because production has whales. Those 10 enterprise customers own 80% of the orders and the other 49,990 customers split the remaining 20%. That single fact, that a handful of rows in customers get joined-to forty thousand times each while everyone else is joined-to a handful of times, is one of the hardest things in all of database performance for a planner to get right. Every database hates this. And here's the part that should bother you: each one hates it differently, so the fix that rescues one engine does literally nothing on the other.

On Postgres, the whale query runs at 1,572ms and one free session setting drops it to ~310ms, a 5x win with zero schema change. On SQL Server, that exact setting, and a dozen of its cousins, can't move the needle off a 2,000ms floor at all. SQL Server needs a totally separate fix involving three different levers stacked together to reach ~330ms. The AI confidently predicted symmetry, one fix for both engines. The AI was wrong. I was wrong! ExoBench sorted out both of our egos!

(Already sold and want to run this on your own skewed table? Get started here. Otherwise, here's what I found.)

I didn't write a line of benchmark code for any of this. ExoBench is an MCP server I built which spins up real Postgres and Azure SQL instances, generates the synthetic data at the distribution you ask for, runs the query, and hands back the actual execution plans. Every number below is QueryTimeStats.ElapsedTime from the SQL Server plan XML or the root-operator actual time from a Postgres EXPLAIN ANALYZE. Nothing here is guessed from documentation. I'll quote my follow-up prompts inline, in boxes like the one above. they are verbatim, typos preserved, so you can see the exact shape of each request that drove the next run.

First, the control: what if the data were uniform?

Before blaming skew, you have to rule out the query and the schema. So the first thing I did after seeing the baseline numbers was run the boring counterfactual on my own. take the same schema, same indexes, same row counts, but generate orders.customer_id uniformly across all 50,000 customers instead of piling 80% onto ten of them. Then run the identical query. Same ExoBench call, just a different data generator clause.

Read that top row. With uniform data the query is instant on both engines, the plan is a clean cascading nested loop, and the optimizer's cardinality estimate is accurate to within ~2x. Everything looks fine. If you benchmarked this on Faker-generated test data, you'd ship the nested-loop plan, close the ticket, and never know there was a problem. The naive benchmark doesn't just miss the issue, it produces a confident all-clear and then production runs the identical plan at 2,340ms.

The plan is the same. The query is the same. The indexes are the same. The row counts in every table are the same. The only thing that changed is which customers the orders point at, and that single change inverts the conclusion by a factor of over a thousand. This is why test-data distribution is not a detail you can hand-wave. It's the whole experiment.

So the skew is the villain. Confirmed. Now, why does it wreck both planners, and why does the cure split?

Why both planners walk into the same wall

Here's the diagnosis, and it's the same on both engines.

Both planners look at the histogram on orders.customer_id and see 500,000 orders spread across ~50,000 distinct customers. Average: ~10 orders per customer. So when the query feeds in the 10 enterprise customer IDs, the planner reasons "10 customers times ~10-15 orders each, call it 150 rows" and picks a cascading nested loop, which is genuinely the right plan for 150 rows.

Reality: those 10 specific customer IDs aren't average. They own 400,000 orders between them, ~40,000 each. The planner used the global average density where the actual joining keys sit in the extreme tail of the distribution.

The leaf scans are accurate. The joins explode. That signature, accurate leaves and exploding joins, is the fingerprint of join-crossing correlation, and it's distinct from a stale-statistics problem where the leaf would be wrong too. Neither engine's statistics model the correlation between a customer's tier and its order count, because that correlation lives across a join boundary. A single-column histogram on orders.customer_id is averaged over all 50,000 customers, so the 10 whales get smeared into the same per-key density as the 49,990 minnows. The estimator isn't buggy. It's faithfully extrapolating from a statistic that physically cannot express the thing you need it to know.

This is not a tuning problem. It's structural, and it's present in essentially every cost-based optimizer that relies on per-table column statistics. Postgres extended statistics (CREATE STATISTICS) won't save you because the correlation is across the join, not within one table. SQL Server filtered statistics have the same ceiling. Both engines miss by roughly the same 2,400–3,000x and pick the same fragile plan.

So far, perfectly symmetric. Same query, same data, same blind spot, same wrong plan, nearly the same slowdown.

Postgres: one free line, 5x faster

One more thing before we go further. The AI is about to confidently predict the wrong outcome, and the only reason you'll see the correction is that a real engine produced it. That's the difference between this and pasting your query into a chatbot: a chatbot predicts what the planner will do, ExoBench shows you what it actually did. Guessing versus measuring. If you want the full architecture, here's how it works, and here's how it compares to chatbots and other tools.

After the baseline runs above, the AI gave me a tidy diagnosis: same blind spot in both planners, same fix on both. force a hash join on each engine and the nested-loop catastrophe goes away. It offered to run the counterfactual and prove it. I didn't buy it. You can't just poke sanctioned join-plans with a stick and get real improvements... can you!?

On Postgres, the AI was right, and it's decisive:

One session-level setting. No schema change, no new index, no rewrite. The planner still estimated ~90 rows out of a join that produced 720,000, so the estimate was just as wrong, but the hash plan doesn't care. It scans order_items once sequentially across a few parallel workers and builds a hash table, instead of doing 720,000 random index probes. The nested-loop plan's cost scales with the number of probes, so a 3,000x underestimate is catastrophic. The hash plan's cost is dominated by a single scan, so the outrageous row-estimate doesn't do anything. Buffer traffic collapsed 230x. The lever was the join strategy, and forbidding the bad algorithm cost exactly one line.

Postgres has seven index types and you didn't need any of them here. You needed to revoke one bad decision.

Okay, neat. Same fix on SQL Server and we're done, right?

SQL Server: The Quagmire where every Hint Hits a Wall

No. SQL Server did the opposite of what the AI predicted, my skepticism turned out to be earned. I'm going to summarize what happened because if I don't, it will literally hit somebody's character limit!

The forced hash plan was more than twice as bad as the original disaster. Two things sank it. First, the hash join spilled to tempdb, because SQL Server granted the build only ~4.3MB of memory, sized off that same 179-row underestimate, while the build side actually carried ~400K rows. The bad estimate didn't just mislead the plan choice, it poisoned the memory grant. Second, the forced hash plan still contained the 720K-iteration key lookup anyway, because the hint forced the joins to hash but the optimizer still chose to fetch quantity and unit_price through a per-row lookup off the non-covering index. The hint forced away the nested-loop joins and left the single most expensive nested-loop-shaped operation in place, then stacked a tempdb spill on top. Worst of both worlds.

The AI's mental model was Postgres-shaped and it had jammed that shape onto a different engine. Now I had my proof: the diagnosis was wrong, at least for SQL Server. So we went scorched-earth. Across roughly eight rounds I threw the entire no-index hint arsenal at it, and the prompts got progressively less polite. Verbatim:

This is the part that would have eaten three days by hand: spin up a 1.4-million-row dataset, build the indexes, run the query, capture the plan XML, read it, form the next hypothesis, tear it down, rebuild. Per attempt. Times ten. Through ExoBench it was ten prompts in an afternoon, each one a real Azure SQL instance with a real plan.

The entire no-index option space spans 2,007ms to 2,651ms. The best result that left the access path alone was 2,007ms, a 13% improvement that still couldn't cross the 2,000ms line. Every single lever hit the same wall, because every one of them still had to perform the same ~720,000-row, ~2-million-logical-read fetch to retrieve quantity and unit_price. No join hint, no estimator override, no join order, no memory grant tweak, no batch-mode toggle, and no query rewrite removes that fetch. It's baked into the access path.

That's the asymmetry in one sentence: Postgres's bottleneck was a join-algorithm choice you can revoke for free, and SQL Server's was an access-path choice no hint can reach. Same skew. Same bad estimate. Completely different binding constraint.

SQL Server: the breakthrough needs three levers at once

After Prompt 10 we had exhausted everything that left the access path alone. So I gave up on the hint arsenal and let the optimizer have what it had been quietly asking for the whole time:

The fix is one index. Carry the two summed columns on the index leaf so the key lookup simply doesn't exist:

CREATE INDEX idx_items_order_covering
  ON order_items(order_id) INCLUDE (quantity, unit_price);

No join hints. Let the planner choose freely.

The covering index alone got it to 1,153ms, half the baseline, by killing the lookup. But it left the engine on a serial nested-loop plan doing 1.2 million reads through the index. The real result needed all three levers stacked:

... OPTION (USE HINT('ALLOW_BATCH_MODE'), HASH JOIN, MIN_GRANT_PERCENT = 15)

That's the interesting part of the SQL Server story, and it's a genuine contrast with Postgres. Postgres needed exactly one lever and the second-best fix (the covering index) was worse for it. SQL Server needed three levers that unlock each other: the covering index makes a batch-mode scan viable, batch mode makes the covering index pay off fully, and the hash join consumes the result without a per-row probe. Neither alone got there. Covering-alone stalled at 1,153ms on a serial plan. Batch-mode-alone stalled at ~2,041ms on the lookup. Together they collapse the ~2-million-read fetch into a single covered batch-mode scan at ~3,022 reads, a 685x reduction in logical reads, and the whole query lands at 330ms. Without ExoBench, I shudder at how much time it would have taken to reach this absurdly specific combination of levers.

So the two engines reach roughly the same finish line, ~310ms versus ~330ms, by entirely different routes. Postgres: change the join strategy, one free setting. SQL Server: change the access path, an index plus two hints working in concert. The thing that helped SQL Server most (the covering index) was the weakest fix for Postgres, which only got 1.25x from it because the planner stubbornly kept the nested loop. "Bad estimate" does not imply one universal fix. The right lever depends on the engine's default plan shape and what its alternative actually costs.

Even the "proper" fix Does Not Transfer

There's a textbook answer to skew that I haven't mentioned yet, and a good engineer is already shouting it: stop lying to the optimizer. Don't hint, don't index around the problem, fix the cause. Split the high-volume rows into their own table so the statistics are honest. The enterprise customers get enterprise_orders, everyone else gets regular_orders, and now the histogram on enterprise_orders.customer_id describes only the ten whales, so its average really is ~40,000 orders per customer instead of being drowned by 49,990 minnows.

This is the deepest and most correct fix in the whole investigation. It attacks the cause instead of a symptom. And it also fails to transfer cleanly, which is the point I want to leave you with.

So I ran it. Both engines. Split orders into enterprise_orders and regular_orders so the histogram on each table describes one population, then UNION ALL the two halves back for the full result. Same query semantics, honest statistics. Is it actually better than the hints, or just different?

One thing to get out of the way first: splitting customers alone does nothing on either engine (2,302ms SQL Server, 1,613ms Postgres). The skew never lived in customers. The WHERE tier = 'enterprise' filter already estimated those 10 rows correctly in the baseline. The skew lives in the fan-out of orders.customer_id, so only splitting orders can fix it. Aim at the table where the disease actually is, not the one it appears to describe.

Splitting orders works, but here is the full scoreboard, and the two columns do not agree:

Read each column separately, because forcing them into one verdict is exactly the mistake.

On SQL Server the split wins decisively. 193ms, faster than the 330ms covering-index result, and it needs no covering index. The reason is the same one that defined the SQL Server quagmire: its bottleneck was the 720K-row key lookup, and honest per-table statistics let the optimizer cost a plain batch-mode scan correctly and drop the lookup entirely. When the top-join estimate goes from 3,000x wrong to 900,001-vs-900,000 (essentially perfect), the engine derives a beautiful parallel batch-mode hash plan on its own, with no hints at all.

On Postgres the split loses (by a bit). The fair UNION ALL reconstruction is 391ms, which is 22% slower than the free SET enable_nestloop = off (321ms) and it costs you a full schema redesign: two order tables, a UNION ALL on every cross-tier query, write routing for new orders, and physically moving rows when a customer changes tier. Postgres never had the deep problem. Its only mistake was picking a nested loop over a hash join, and you can revoke that for free. Reaching for a table split here is cargo-culting a SQL-Server-shaped solution onto an engine that didn't need it.

The mechanism is identical on both engines. Honest statistics produce an honest estimate, and an honest estimate produces a good plan. (And the UNION ALL doesn't reintroduce the blowup, because the optimizer estimates each branch separately and both branches now have clean single-class statistics.) But "fix the cause" is only the best fix when the bad estimate was the binding constraint. On SQL Server it was, so the cause-fix is supreme. On Postgres the bad estimate only caused a recoverable algorithm choice, so the cheap symptom-fix dominates the expensive cause-fix. Same skew, same correct theory, opposite economics.

The honest wall: where this stops

Skew is genuinely one of the hardest things a database has to get right, and I'd rather you know the edges of what I measured than discover them in production.

1. Synthetic data is not your data. ExoBench generated exactly 10 enterprise customers owning exactly 80% of orders. If your real skew is 95% on 3 accounts, or 60% on 200, the multipliers move and possibly the winning plan with them. The shape of the asymmetry (Postgres wants a join-strategy fix, SQL Server wants an access-path fix) is robust, but the exact crossover is mine, not yours. Hand the AI your real distribution and re-run it.
2. The AI fills in everything you didn't specify, and it can fill in wrong. I gave it the schema and the 80/20 skew. it picked the row counts, the indexes, the data generators, the distributions for name and unit_price, the order of INSERT statements, and which EXPLAIN mode to use. Always ask it "show me the schema and the data shape you actually used." If the shape is wrong, the benchmark is confidently wrong, and a confidently wrong benchmark is worse than no benchmark.
3. These are two different managed platforms, not one box. Postgres ran on Neon, SQL Server on Azure SQL Database, comparable hardware but not identical. So read the within-engine before-and-after as solid, and treat the cross-engine absolute times as a reference point, not a head-to-head verdict. The asymmetry in which fix works is the finding, not "engine A beats engine B by Xms."
4. It's probabilistic and it optimizes what you give it. Two runs may explore different hint orders. And if the real answer is "this query shouldn't run on every page load," ExoBench will happily handraulic it down to 330ms and never tell you to cache it instead. It measures the query you brought. It doesn't ask whether the query should exist.
5. It doesn't fix infrastructure. Memory grants, connection pools, concurrent load, network latency, deployment topology. ExoBench measures the query and the plan, not your cluster.

What it does fix is the part where you take the optimization advice that rescued your Postgres database, apply it to your SQL Server database, and quietly get nothing, because the two engines failed for the same reason and recover for completely different ones, and nobody told you that because nobody measured both.

Try this on your own skewed table

Find your candidate. The aggregate over a tiered or fanned-out table that's slower than it has any right to be:

-- Postgres
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;

If the top result joins across a table where a few keys dominate the volume, customers and orders, accounts and events, tenants and rows, it's a skew question, and now you can settle it on each engine instead of guessing on both.

This is the exact question I'd point at your table. Head to exobench.ai to connect it to whatever you already talk to, Claude, Cursor, whatever, and paste a prompt shaped like the ones I used:

That last sentence is the one the folklore can't answer and you now can. The AI translates the whole thing into ExoBench tool calls, spins up both engines, and you read the plans side by side. Get started with ExoBench and your first benchmarks are free. No preview application, no waiting for approval.

That whale query that's been quietly costing you two seconds and a different headache on each database? You don't have to guess which lever fixes which engine. You can know in about 90 seconds, twice.