PostgreSQL: 16 Cores. This Query Uses One

I have seen this pattern many times in production PostgreSQL

systems. The team was already thinking about more hardware. /

One query took eighteen seconds, on a server with a hundred

Eighteen point four seconds, down to four point one.

That's more than four times faster, and it cost nothing in

hardware. Let me show you exactly how we got there.

shared_buffers was a hundred and twenty-eight megabytes. On a

hundred and twenty-eight gig server, that's about a tenth of

effective_cache_size was four gigs, so the planner thought

Here's the query. Nothing exotic. Top fifty customers by orders in the last ninety days. A join. A date filter. A group by. An order by. Normal reporting SQL. But it takes over eighteen thousand milliseconds, and it reads almost forty-six thousand pages from disk. So before you rewrite the query, stop.

The SQL is not the first suspect. The plan is.

And the plan tells you everything. This is EXPLAIN ANALYZE,

Look at the sort: "external merge", five hundred and forty-two

megabytes written to disk. That's work_mem being too small.

So here's the plan, in order. Five steps. Measure first, with EXPLAIN ANALYZE, always. Then fix the planner's view of memory. Then give PostgreSQL real cache. Then deal with the sorts. And finally, parallel workers. One change at a time. Measure after each one. Never touch two settings at once, or you'll never know which one actually helped.

It does not allocate memory. It's just the planner's estimate

Four gigs to ninety-six. About seventy-five percent of RAM.

Now the planner has a better chance to pick the right scan.

Setting two: shared_buffers. This is PostgreSQL's own cache,

the pages it keeps in memory so it doesn't go to disk.

The default was a hundred and twenty-eight megs. We move it to

Setting three: work_mem. This is the one that bites if you get

with parallel workers and a couple of sorts can use it several

So do not set it to a gigabyte globally. Fifty sessions, two

Setting four: parallel workers. You're raising the ceiling,

you're not forcing anything. The planner still decides whether

On a sixteen-core box, a default of two per gather is low. Four

(SLOW DOWN: 45-60s, this is the payoff, let it breathe)

Eighteen thousand two hundred thirty-four milliseconds.

Forty-five thousand eight hundred ninety-two disk reads.

PostgreSQL's shared_buffers, plus the operating system's page

work_mem is separate. It's per-operation scratch space, for

And disk is the fallback. It's the slowest thing in this

Measure first. Tell the planner the truth about your cache.

Give PostgreSQL enough memory to cache with. Fix sort spills

carefully, never globally. Raise the parallel ceiling.

(fast, ~20s) You fixed the server. Now protect the gain. Watch three things: your cache hit ratio, your heaviest queries in pg_stat_statements, and EXPLAIN with buffers. Tuning is not a one-time event. Measure again after the workload changes.

(fast, ~20s) The loop, for any slow query: Baseline. One setting. Reload or restart. Re-test. And if it got worse, roll it back. Your servers have capacity. Your defaults don't.