WhyPostgresLiestoYouAftertheFirstQueryRun

The Moment Everything Felt Wrong

You've been there. You run a query against your Postgres database, it comes back in milliseconds, and you feel like a genius. You show your colleague. You pat yourself on the back. Then you run it again — maybe after a bit more data has come in, maybe after a week of normal application traffic — and suddenly the same query is dragging its feet like it's walking through wet concrete. You haven't changed a single line of SQL. The schema is identical. But something has shifted underneath you, and you can't quite put your finger on what.

This isn't a ghost story. It's not random either. There's a very specific, very logical reason this happens, and once you understand it, you'll never look at Postgres the same way again. The culprit is the query planner — specifically, how the query planner uses statistics to make decisions, and how those statistics can become stale, misleading, or just plain wrong as your data evolves.

I've found this to be one of the most misunderstood performance problems in production systems, precisely because it doesn't announce itself loudly. It just makes things slower, quietly, over time.

How Postgres Decides to Run Your Query

Before we get into why things go wrong, we need to understand what's actually happening when you hit execute. Postgres doesn't just blindly execute your SQL. It first hands it off to the query planner (sometimes called the query optimizer), which is essentially a sophisticated decision-making engine. The planner's job is to figure out the most efficient way to get you the data you asked for.

Think of it like a delivery driver planning a route. You give them a list of addresses, and they have to figure out the fastest order to visit them. A good driver uses real-world knowledge — which roads are congested, which neighborhoods are close together — to make smart choices. Postgres's planner uses statistics about your data to make the same kind of smart choices: which index to use, whether to scan a whole table or just part of it, in what order to join tables together.

The key phrase there is "statistics about your data." The planner doesn't inspect every row in your table every time you run a query — that would be absurdly slow. Instead, it consults a pre-computed summary of your data stored in a system catalog called pg_statistic. This is the foundation everything else is built on, and it's also where things start to go sideways.

What Are Statistics, Actually?

When Postgres collects statistics — which it does automatically through a background process called autovacuum — it's building a rough portrait of your data. It samples your table and records things like: how many distinct values exist in a column, what the most common values are, how values are distributed across a histogram. It's not reading every row. It's sampling.

The default statistics target in Postgres — controlled by default_statistics_target — is 100 samples per column. That number sounds reasonable until you're dealing with a table with 50 million rows and highly skewed data distributions. A sample of 100 doesn't tell you much about the outliers, the edge cases, or the specific value ranges your queries are hitting in production.

Here's the critical insight: the query planner trusts these statistics completely. It uses them to estimate how many rows a particular filter will return. These estimates are called row count estimates, and they drive everything. If the planner thinks your WHERE status = 'pending' clause will return 500 rows, it might choose a fast index scan. If it thinks the same clause returns 4 million rows, it'll choose a sequential scan instead — reading the whole table in one big sweep. Both can be the right choice. But if the estimate is wrong, the plan is wrong, and your query suffers.

The First Run Trap

Here's where the title of this article starts to make sense. When you first run a query — especially right after loading data or creating a new table — you might be hitting a very specific, almost paradoxical scenario.

If statistics haven't been collected yet, Postgres falls back on default estimates. It will guess things like "probably about 1000 rows in this table" or "this filter probably matches about 30% of rows." These are hardcoded fallbacks, and they can actually land close to reality in a brand new, small dataset. Your first query runs fast because the default assumptions happen to be true enough — the table is small, the plan works, everything feels snappy.

Then your data grows. Autovacuum collects statistics, but maybe not frequently enough. Or the distribution of your data changes dramatically — maybe status = 'pending' used to match 200 rows, but now it matches 8 million because your application has been hammering new records into the database all week. The planner looks at stale statistics, sees an old estimate, and generates a plan that made sense last Tuesday but is catastrophically wrong today.

-- Check what Postgres thinks about a specific column's statistics
SELECT
  attname AS column_name,
  n_distinct,
  correlation,
  most_common_vals,
  most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';

Run this query on your table. Look at the most_common_vals and most_common_freqs columns. If those numbers don't match what you know about your actual data distribution, you've found your problem. The planner is working with a lie.

EXPLAIN ANALYZE Is Your Truth Serum

The single most powerful tool for diagnosing this problem is EXPLAIN ANALYZE. Most developers know EXPLAIN — it shows you the query plan. But ANALYZE actually runs the query and compares what the planner estimated with what actually happened. That comparison is where the smoking gun lives.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '7 days';

When you run this, look at the output carefully. Every node in the plan shows two numbers: rows=X (the estimate) and actual rows=Y (the reality). When those numbers are wildly different — say, the planner estimated 150 rows but actually got 2.3 million — that's called cardinality misestimation. Cardinality just means "number of rows," and when Postgres gets it badly wrong, the downstream plan falls apart.

I've personally debugged production incidents where the planner estimated 45 rows and the query returned 6 million. The plan chosen was a nested loop join — a strategy that's brilliant for small datasets and absolute torture for large ones. The fix wasn't in the SQL. It was in the statistics.

Why Autovacuum Isn't Enough on Its Own

You might be thinking: "But Postgres has autovacuum. Doesn't that keep statistics fresh?" Yes, autovacuum does trigger ANALYZE automatically, but it operates on thresholds. By default, autovacuum runs ANALYZE on a table after 20% of its rows have been changed. That sounds aggressive, but do the math: if you have a table with 100 million rows, autovacuum won't kick in until 20 million rows have been modified. That could be days of drift. Meanwhile, your query plans are running on increasingly stale information.

There are also tables that grow continuously with inserts but never see updates or deletes. Append-only logging tables are a classic example. Because autovacuum is primarily triggered by dead tuples (which come from updates and deletes), these tables can go a very long time without statistics being refreshed, even while their row counts balloon dramatically.

The practical fix is to be more aggressive with your statistics collection. You can manually trigger it anytime:

-- Manually refresh statistics on a specific table
ANALYZE orders;

-- For a specific column that you know is heavily queried
ANALYZE orders (status, created_at);

You can also increase the statistics target for specific columns that are frequently used in WHERE clauses or joins. Higher targets mean more samples, which means better estimates — at the cost of slightly more time to analyze:

-- Increase statistics target for the status column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

-- Then run ANALYZE to apply the new target
ANALYZE orders;

This is particularly valuable for columns with skewed distributions — where a handful of values appear way more often than others. Postgres needs a bigger sample to understand skew accurately.

The Multi-Column Statistics Problem

Here's a subtlety that trips up even experienced Postgres users. Even with perfect single-column statistics, the planner can still go wrong when you filter on multiple columns at once. By default, Postgres assumes that different columns are statistically independent of each other — that knowing the value of one column tells you nothing about the value of another.

In real life, that's almost never true. If you have a country column and a city column, they're obviously correlated — knowing a city is "Paris" tells you a lot about what the country probably is. Postgres doesn't know this unless you tell it explicitly using extended statistics:

-- Create extended statistics to capture correlation between columns
CREATE STATISTICS orders_status_region_stats (dependencies)
ON status, region
FROM orders;

-- Then analyze the table so Postgres uses the new statistics
ANALYZE orders;

The dependencies keyword tells Postgres to track functional dependencies between these columns — meaning it learns that certain values of one column tend to appear with certain values of another. This dramatically improves row count estimates for queries that filter on both columns simultaneously, which in turn leads to better query plans.

This feature was added in Postgres 10 and often goes unused, even by teams who are otherwise pretty sophisticated with their database tuning.

When the Plan Changes on You Mid-Flight

One of the most disorienting experiences is when a query plan changes between deployments or even between hours of the day. You didn't change anything. The schema is the same. But suddenly the query is hitting a sequential scan instead of an index. This is almost always the statistics problem manifesting in a slightly different way.

Postgres doesn't cache query plans in the traditional sense for ad-hoc queries. Every time you run a raw SQL query, it goes through the planning phase fresh. That means if your statistics have changed — because autovacuum ran, because you manually ran ANALYZE, because you loaded a big batch of new records — the planner might choose a completely different plan. Sometimes that's good. Sometimes it's a disaster.

In my experience, the most dangerous scenario is a sudden load of new data that shifts the distribution dramatically without an immediate statistics refresh. You load 10 million rows from a data import at midnight. The planner doesn't know yet. It makes plans based on the old picture of the world. Your morning traffic hits, the estimates are wildly off, and your DBA gets paged at 7am because the application is grinding to a halt.

The solution here is to make statistics refresh a first-class part of your data pipeline. Any time you do a bulk insert, a large delete, or a significant data migration, run ANALYZE on the affected tables before the application starts hitting them again. Treat it like flushing a cache after an update.

Practical Steps to Stop Getting Burned

Understanding this problem is half the battle. Acting on it is the other half. There are a few concrete habits that I've seen transform query performance for teams dealing with this issue.

First, build EXPLAIN (ANALYZE, BUFFERS) into your debugging workflow by default — not just when something is slow, but whenever you're writing new queries against tables with significant data. Pay attention to the gap between estimated and actual rows. If that gap is more than an order of magnitude, your statistics need attention. Make this a habit and you'll catch problems before they hit production.

Second, be thoughtful about your autovacuum settings for your most critical tables. You can tune autovacuum at the table level, making it more aggressive for tables that change frequently or are large:

-- Make autovacuum analyze more aggressively for a high-traffic table
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,  -- trigger after 1% of rows change
  autovacuum_analyze_threshold = 1000       -- or after 1000 rows change, whichever is smaller
);

Third, consider monitoring pg_stat_user_tables. The column last_analyze and last_autoanalyze tell you exactly when statistics were last collected for each table. If you see a table that's critical to your application but hasn't been analyzed in several days — and you know it's been receiving heavy writes — that's a warning sign.

The relationship between your data and your query performance isn't static. It evolves. The faster your data grows or changes shape, the faster your statistics drift away from reality, and the more Postgres's plans diverge from what's actually optimal. Treating statistics refresh as a maintenance chore you do occasionally is what gets you into trouble. Treating it as an ongoing, intentional part of database operations is what keeps your queries honest.

Postgres isn't lying to you maliciously. It's doing the best it can with the information it has. Your job is to make sure that information stays accurate.