Slow Queries PostgreSQL Articles

For latest-five-orders-per-customer against a selective driving set, a LATERAL join over the right composite index reads hundreds of rows where a window function churns through millions. The catch is knowing when that flips.

After five custom executions PostgreSQL starts weighing a cached generic plan against what those custom plans cost — and on skewed data the generic plan it settles on can be terrible. How the heuristic works, how to diagnose it, and when plan_cache_mode saves you.

The planner is only as good as its statistics. When autoanalyze lags behind a bulk load, the bad plans arrive long before the next analyze run does.

A single PostgreSQL query can consume work_mem many times over. Understanding the per-node multiplier is the difference between fast sorts and an OOM-killed primary.

The planner picked a nested loop that ran 400,000 times because n_distinct was off by a factor of 30. Raising one column's statistics target fixed the estimate — and the plan — in a single ANALYZE.

MySQL leans on the slow query log and performance_schema digests; PostgreSQL leans on pg_stat_statements and auto_explain. Here is how the workflows map, and where each side needs supplementing.

MySQL gives every connection a thread; PostgreSQL gives every connection a process. That one difference changes capacity planning, pooling strategy, and what breaks under transaction pooling.

Deep OFFSET pagination punishes InnoDB and PostgreSQL alike. Here is how keyset pagination works in each engine, including composite cursors, row-value comparisons, and covering-index tricks.

The planner picked a nested loop that ran 400,000 times because n_distinct was off by a factor of 30. Raising one column's statistics target fixed the estimate — and the plan — in a single ANALYZE.

For latest-five-orders-per-customer against a selective driving set, a LATERAL join over the right composite index reads hundreds of rows where a window function churns through millions. The catch is knowing when that flips.

After five custom executions PostgreSQL starts weighing a cached generic plan against what those custom plans cost — and on skewed data the generic plan it settles on can be terrible. How the heuristic works, how to diagnose it, and when plan_cache_mode saves you.

JIT compilation is supposed to make big PostgreSQL queries faster, but bad cost estimates fire it up on cheap ones. Here is how to spot the regression and switch it off safely.

The wrong-index ORDER BY LIMIT regression bites MySQL and PostgreSQL alike: the planner walks an ordering index expecting early matches that never come. Here is why LIMIT tempts both optimizers, and how to diagnose and fix it in each.

MySQL EXPLAIN and PostgreSQL EXPLAIN describe query plans in different vocabularies with different cost models. This guide translates between them and shows what each output quietly hides.

The planner assumes your columns are independent. When they're actually correlated, its row estimates collapse, it picks a nested loop over a hash join, and a fast query becomes a slow one. CREATE STATISTICS fixes the math.

We partitioned a table by month and the dashboard was still slow. Partitioning only helps when the planner can prune — and ours couldn't, because the queries didn't filter on the partition key.

The PostgreSQL planner is only as good as its estimates. Extended statistics help when correlated columns, multi-column filters, and skewed values make ordinary statistics lie.

Prepared statements reduce parse and planning overhead, but generic plans can become a production regression when tenant skew, partial indexes, and parameter-sensitive predicates arrive.

pg_stat_statements is where PostgreSQL performance work becomes concrete. The trick is ranking by impact, variance, I/O, and calls instead of staring at one dramatic slow query.

EXPLAIN ANALYZE is not just a plan tree. With BUFFERS and real row counts, it becomes a production trace for estimates, I/O, loops, spills, and wasted work.

EXPLAIN ANALYZE is readable once you know the order: find the slow node, compare estimates to actuals, check loops, then confirm buffers and waits.

A nested loop join is fast or slow depending on the row counts. Here is when it is the right plan, when it is not, and how to read the EXPLAIN output to tell.

Hash joins are the workhorse for large equi-joins. They are also the join type that quietly spills to disk when work_mem is too small, turning a fast query into a slow one.

Planner problems usually start with one wrong estimate. Learn how to spot the first lie in the plan, then fix the statistics, predicate, or index that caused it.

CTEs read beautifully and sometimes cost beautifully too. The behavior changed in Postgres 12, and most teams I work with have not updated their mental model.

Window functions are the right tool for ranked-per-group queries. They are also surprisingly fragile in performance. Here is how to write them so they actually scale.

DISTINCT ON is a Postgres-specific shortcut for "give me one row per group, sorted some way." It is fast, it is pleasant, and the version in your head is probably wrong.

OFFSET pagination gets slower the deeper you go. Keyset pagination is constant time. Here is how to switch over and the cases where you cannot.

A practical slow-query incident workflow: find the fingerprint, prove the bottleneck, avoid the tempting wrong fix, and ship the smallest change that actually holds.

Cloud SQL maintenance windows are mostly fine and occasionally not. Here is what happens during them, what gets restarted, and how to make sure your application survives.

N+1 is the most common ORM-induced performance bug. The query count tells the story; the application code makes it impossible to spot at review time.

Prepared statements skip the planning step on repeated execution. Sometimes that is a 5x speedup. Sometimes it is a 50x slowdown. Knowing the difference matters.

Bad plans usually start with bad row estimates. Fix the first wrong estimate and the rest of the plan often stops looking mysterious.

Plan regressions are painful because the SQL did not change. The work is proving the plan changed, finding the estimate or stats shift, and restoring the safe path.

auto_explain is for the slow plan you cannot reproduce later. It captures the execution plan when the bad thing actually happens.