Re: BUG #18588: Cannot force/let database use parallel execution in simple case.

On Fri, 23 Aug 2024 at 10:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> What Maxim is observing is that he's getting I/O concurrency from
> multiple workers; and that's an effect we're simply not accounting
> for here.  cost_seqscan excuses the same omission with
>
>          * It may be possible to amortize some of the I/O cost, but probably
>          * not very much, because most operating systems already do aggressive
>          * prefetching.  For now, we assume that the disk run cost can't be
>          * amortized at all.
>
> but I don't think this prefetch argument applies to index scans.

I don't think it does either. The problem is worse for index scans too
as the I/O costs are likely to be higher due to a larger portion of
reads being costed with random_page_cost.

I had a look in src/backend/optimizer/README to see if we've defined
what parallel plan costs mean and I didn't see anything under the
"Parallel Query and Partial Paths" heading. I don't see anything in
the header comment to struct Path either. I was hoping we could fall
back on some guidance as to what they mean.  The way parallel query
works today means that we could have 10 workers do 9x more work and
the plan could come up 10% cheaper than the equivalent serial plan.
It's really just the sanity of the selected number of workers that
protects plans from becoming too resource inefficient.

Since we don't normally backpatch cost changes, I think Maxim might
need to try tweaking effective_cache_size. Maybe we can revisit which
costs we divide by the worker count in master.

David