Re: partition table optimizer join cost misestimation

On 4/2/25 12:18, James Pang wrote:
> Hi,
>    Postgresq v14.8, we found optimizer doest not take "merge append"
> cost into sql plan total cost and then make a bad sql plan. attached
> please find details.
I suppose there is a different type of issue.
MegeJoin sometimes doesn't need to scan the whole inner or outer side
(see the MergeScanSelCache structure and how it is used in the cost
estimation routine).

So, the cost can be less because the optimizer predicted that only a
small part of the Append will be scanned and used some sort of
interpolation between startup cost and total cost.

But to be sure, could you send the results of EXPLAIN ANALYZE VERBOSE?
If you also send the data to reproduce the case, we may find the source
of the problem more precisely.

--
regards, Andrei Lepikhov