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