Re: Should we update the random_page_cost default value?

On Mon, 2025-10-06 at 02:59 +0200, Tomas Vondra wrote:
> I wonder if it's time to consider updating the random_page_cost default
> value. There are multiple reasons to maybe do that.
>
> [experiments]
>
> Unless I did some silly mistakes, these results suggest the current 4.0
> value is a bit too low, and something like ~20 would be better even on
> SSDs.
>
> [...]
>
> It seems to me the current default is a bit too low, but changing a GUC
> this important is not trivial. So what should we do about it?

I have no reason to doubt your experiments and your reasoning.

However, my practical experience is that PostgreSQL tends to favor
sequential scans too much. Often, that happens together with a parallel
plan, and I find PostgreSQL with the default configuration prefer a plan
with two parallel workers performing a sequential scan with a ridiculously
selective (correctly estimated!) filter condition like 500 rows out of a
million over an index scan that is demonstrably faster.

I have no artificial reproducer for that, and I admit that I didn't hunt
down the reason why the planner might prefer such a plan. I just tell
people to lower random_page_cost, and the problem goes away. So I am
clearly fighting symptoms. Often, an alternative solution is to set
max_parallel_workers_per_gather to 0, which seems to suggest that perhaps
the suggestion from [1] is more interesting than I thought.

Anyway, I cannot remember ever having been in a situation where PostgreSQL
prefers a slow index scan, and I had to raise random_page_cost to get a
faster sequential scan. Perhaps that is because slow index scans are often
not drastically slower, perhaps I deal too little with purely analytical
queries.

Again, this doesn't invalidate any of what you said. I just wanted to
share my experiences.

Yours,
Laurenz Albe

[1]: https://postgr.es/m/a5916f83-de79-4a40-933a-fb0d9ba2f5a0%40app.fastmail.com