Re: partition table optimizer join cost misestimation

Andrei,
   Yes, from explain output, since optimizer already get the merge_append
cost but not take account into total cost, that make a big difference.  I
shared table DDLs and explain analyze,buffers output , I think  the data
maybe generated by other way to reproduce this issue. sorry for not sharing
the commercial production data here.

Andrei Lepikhov <lepihov@gmail.com> 於 2025年4月2日週三 下午7:03寫道：

> 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
>