Message-ID: <a05e0fed-dc66-4873-ac8c-4a8352c4e69d@gmail.com>
Date: Wed, 2 Apr 2025 13:03:37 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: partition table optimizer join cost misestimation
To: James Pang <jamespang886@gmail.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a05e0fed-dc66-4873-ac8c-4a8352c4e69d%40gmail.com>
Precedence: bulk

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