3 min read
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.
Database Topic Archive
Slow Queries Articles
Query plans, EXPLAIN ANALYZE, planner regressions, pagination, joins, and statistics.
5 min read
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.
3 min read
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.
7 min read
EXPLAIN ANALYZE is readable once you know the order: find the slow node, compare estimates to actuals, check loops, then confirm buffers and waits.
5 min read
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.
5 min read
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.
6 min read
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.
5 min read
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.
6 min read
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.
4 min read
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.