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

On Fri, Aug 23, 2024 at 1:44 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Fri, 23 Aug 2024 at 08:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Ah, I traced through it, and here's what's happening: at small enough
> > estimated rowcounts, the parallel and non-parallel plans have fuzzily
> > the same cost (parallel is a shade cheaper, but only a shade).
> > Their other properties such as pathkeys are the same too. So we get
> > to the tie-breaking logic in add_path, and what breaks the tie is
> > the difference in parallel safety: the non-parallel plan is marked
> > parallel_safe and the parallel one (which by this point is a Gather)
> > is not.
>
> I played around with the attached script and set some breakpoints in
> cost_index(). I'm seeing the same thing as you with the parallel path
> being only slightly cheaper, but when looking at cost_index(), it's
> easy to see why.
>
> It's only the cpu_run_cost that's divided by the parallel_divisor. In
> this case, cpu_run_cost is just 7852.89 for the parallel path and the
> parallel_divisor is 2.4. The run_cost is not divided and is much
> higher at 501792, so dividing the CPU cost does not save much. Just a
> few thousand in half a million, which is why the plans are fuzzily the
> same cost.
>
> If I make the cpu_tuple_cost 0.02 instead of 0.01, I get the parallel
> plan. Possibly increasing effective_cache_size would be the best way
> for Maxim to get the parallel plan. I wonder if that's just left at
> the default 4GB... Not many people tune that.
>
> David

Thank you David about hint for cpu_tuple_cost
after some fiddling I found a sweep spot of cpu_tuple_cost for my
particular case when required query start using parallel plans and no
visible slowdown of other queries
in my case it cpu_tuple_cost=0.05

PS: After 25 years with PostgreSQL, correct tuning of *_costs
parameter - is still somewhere between black magic and shamanic ritual
of talking with the spirits of ancestors for me
and in border cases (like this one) performed via trial and error.

PPS: effective_cache_size is set to 75% of RAM (=shared buffer
size=298GB) so future increase of it seems unlikely to help.

--
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678