Subject: Re: Consider low startup cost in add_partial_path

From: Robert Haas <[email-masked]>
Date: Thu, 19 Feb 2026 14:29:52 -0500

On Tue, Feb 17, 2026 at 2:44 AM Richard Guo <[email-masked]> wrote:
> Yeah, I agree that we should consider startup cost for partial paths,
> given that cost_gather (and cost_gather_merge) accounts for the
> subpath's startup_cost when calculating the cost of the Gather path.
>
> The changes LGTM.  I noticed one comment that may need to be updated
> accordingly:
>
>  *    As in add_path, the partial_pathlist is kept sorted with the cheapest
>  *    total path in front. ...
>
> This is not true anymore.  The partial_pathlist is now sorted by
> disabled_nodes and then by cheapest total cost.

Thanks. I have committed 0001 after adjusting that comment.

Here's the remaining patch once again. No comments on this one?

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Richard Guo <[email-masked]>
Date: Wed, 25 Feb 2026 15:35:05 +0900

> Here's the remaining patch once again. No comments on this one?

(Sorry I somehow lost track of this thread.)

I noticed that in this patch, add_partial_path_precheck duplicates the
entire logic of compare_path_costs_fuzzily.  I wonder if it would be
better to avoid this code duplication.

I'm thinking that maybe we could extract the core math of
compare_path_costs_fuzzily into an internal helper function that
operates on cost values (and disabled_nodes) rather than on Path
structures.  We could then call this helper directly from within
add_partial_path_precheck.

That said, I'm not quite sure if that refactoring is worth the churn,
or if there is a better way to do that.

Otherwise, the patch looks good to me.

- Richard

---

From: Robert Haas <[email-masked]>
Date: Wed, 4 Mar 2026 17:04:23 -0500

> add_partial_path_precheck.

Maybe, but it would be a ten-parameter function.

> Otherwise, the patch looks good to me.

Turns out that version had a few problems:

1. It didn't handle the case where costcmp was COSTS_BETTER{1|2} but
keyscmp was PATHKEYS_EQUAL correctly. It had logic like this: if
(costcmp == COSTS_BETTER1) { if (keyscmp == PATHKEYS_BETTER1)
remove_old = true; }. This says that if the new path is better on cost
and the new path is also better on pathkeys, throw away the old path.
That's too weak. It should instead say if (costcmp == COSTS_BETTER1) {
if (keyscmp != PATHKEYS_BETTER2) remove_old = true; }. That is, if the
new path is better cost and is EQUAL OR BETTER on pathkeys, throw away
the old path. As a result, v56 would have kept too many paths.

2. It failed to preserve an equivalent of the (old_path->total_cost >
new_path->total_cost * 1.0000000001) test. It now tests
compare_path_costs_fuzzily(new_path, old_path, 1.0000000001) ==
COSTS_BETTER1).

3. It wasn't pgindent-clean.

Fixing the first of these problems results in one additional
regression test change. The query is:

explain (costs off) select count(*)
  from tenk1 t1
  join tenk1 t2 on t1.unique1 = t2.unique2
  join tenk1 t3 on t2.unique1 = t3.unique1
   order by count(*);

The sides of the t1-t2 join get swapped: the driving table is chosen
to be t2 rather than t1. Without that fix, we keep both
hash_join(t1,t2) and hash_join(t2,t1), The former has a total cost
that is lower by a very small amount and therefore ends up first in
the partial pathlist and so wins. With that fix, we see that
hash_join(t2,t1) has an essentially equal total cost and a much lower
startup cost, so we keep only that one. In other words, this change
seems to be correct.

Here's v57.

---

From: Robert Haas <[email-masked]>
Date: Thu, 5 Mar 2026 13:17:07 -0500

> Here's v57.

I've instrumented the length of the partial pathlist across all
RelOptInfo objects in a run of the core regression test suite. Here's
the result:

without the patch: 1->9343 2->5859 3->38
with the v57 patch: 1->9306 2->5877 3->51 4->4 5->2

That seems very reasonable to me. It means that, across the test
suite, we retain 0.3% more paths than before, and the percentage of
partial pathlists of length 1 drops from 61.31% to 61.06%. That is a
very modest impact, and it has the upside that in some cases we might
get better plans, which is worth a lot.

In fact, looking through the regression test changes in the patch, the
changes to incremental_sort.out look like improvements: in the first
hunk, we now push a PartialAggregate beneath Gather Merge, allowing
more work to be done in parallel. In the second hunk, we now put the
cheaper scan on the inner side of a Parallel Hash Join instead of the
more expensive one. Since the inner side is the build table, the total
cost of that side becomes startup cost for hash join, so this change
is theoretically better. The total cost of the Parallel Hash Join
remains unchanged, while its startup cost drops from 612.32 to 264.32.
In this instance, that doesn't improve the startup or total cost for
the plan as a whole, but I believe that's just a consequence of the
fact that this particular query happens to be putting all of the rows
into a single group. In other cases, it might help.

The case in join_hash.out is similar: it's testing a join between two
tables which, at present, are exactly the same, so even the tiniest
perturbation in the costing formulas can upset the plan and cause it
not to test the right thing any more. In the patch, I change it so
that one side of the join has 2 rows and the other side has 3 rows,
and it then puts the 2-row side on the inner side of the parallel hash
join for the same reasons discussed in the previous paragraph. This
should work just as before but be more stable with these changes.

I feel like I've done all the due diligence that I can think of here
to be sure that this is correct and doesn't cause a path explosion or
plan regressions, so I plan to go ahead and commit this unless there
are objections RSN.

Thanks,

---

From: Robert Haas <[email-masked]>
Date: Mon, 9 Mar 2026 09:08:08 -0400

> plan regressions, so I plan to go ahead and commit this unless there

Committed.