From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18588: Cannot force/let database use parallel execution in simple case. |
Date: | 2024-08-25 20:31:44 |
Message-ID: | CAK-MWwQjVLHzYgeAsgHocft5c1bsVdngomRQhpBKes+8mSuBFg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2024-08-26 06:42:36 | BUG #18589: pg_get_viewdef returns wrong query |
Previous Message | David G. Johnston | 2024-08-24 15:28:27 | Re: Bug or strange result of Max() on arrays containing NULL values |