Re: Should we update the random_page_cost default value?

On 10/6/25 07:34, Laurenz Albe wrote:
> 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.
>

I don't doubt your experience, but to draw any conclusions from this
report we'd need to understand why it happens. I suspect there's some
estimation issue (likely unrelated to random_page_cost), or some effect
our cost model does not / can't consider. And lowering random_page_cost
simply compensates for the earlier error.

> 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.
>

I mentioned a couple factors mitigating the worst effects at the end of
my initial message.

The "range" where we cost seqscan and indexscan in the wrong way is not
that wide. In the NVMe RAID0 case we should flip at 0.1%, but we flip
(based on cost) at ~1.5%. At which point index scan is 10x more
expensive than seq scan.

Maybe people don't do queries in this range too often? On the 500M table
it's ~5M rows, which for OLTP seems like a lot and for OLAP it's
probably too little. Real data is likely more correlated (and thus
benefit from caching).

And of course, we don't flip between seqscan/indexscan, we also have
bitmapscan, and that behaves much more reasonably.

regards

--
Tomas Vondra