Slow Queries Articles

A p90 latency report drifted 40ms after a rewrite for MySQL compatibility, because PostgreSQL's percentile_cont interpolates and the hand-rolled MySQL replacement picked the nearest row. Window functions look portable across the two engines; the frame, aggregate, and ordered-set corners are…

A nightly aggregation wrote 4.1GB of temp files on PostgreSQL and nobody noticed until the disk alert; on MySQL the same class of query pushed Created_tmp_disk_tables up 40,000 an hour. Here is when each engine spills, how to see it,…

A report query flipped from an index range scan to a full scan over a weekend, with zero deploys and zero data imports. Both engines re-estimated the same table and got different answers. Here is how MySQL and PostgreSQL gather…

A report query ran in 26 seconds on PostgreSQL 11 and 480 milliseconds on MySQL 8 with identical SQL, then got slow again on PostgreSQL 12 for a new reason. The WITH clause means different things to each planner, and…

Codebases that grew up on MySQL 5.x are full of workarounds for missing CTEs and window functions. Here is what those queries were compensating for, and their modern rewrites.

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.

The ticket said search was fast for admins and took nine seconds for tenants — same query, same table. The difference was a policy that never appeared in any SQL anyone had written.

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…