partition table optimizer join cost misestimation

From: James Pang <jamespang886(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: partition table optimizer join cost misestimation
Date: 2025-04-02 10:18:57
Message-ID: CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Query: masking table and column names :

select ....
from tablea aa
inner join tableb bb on aa.ind1 = bb.ind1
inner join tablec cc on aa.ind2 = cc.ind2
where aa.ind3 in ($1)
order by cc.id3;

optimizer, it choose merge join with full index scan with a lot of rows and
huge IO needed,
looks like optimizer does not take into account the "Merge Append" huge
cost into the whole SQL plan cost,
so it thought only Sort and Merge join cost and make it to choose mergejoin
instead of nestloop.

--bad plan, it took hundreds of seconds to complete.
Sort (cost=9624.33..9677.60 rows=21306 width=981)
Sort Key: cc.id3
-> Nested Loop (cost=99.78..2717.44 rows=21306 width=981)
-> Merge Join (cost=99.35..2009.19 rows=21306 width=915)
Merge Cond: (cc.ind2 = aa.id2)
-> Merge Append (cost=8.15..17046177.32 rows=98194074
width=903) <<< merge append huge cost but looks like optimizer
does not take this into total plan cost yet.

--good plan, off mergejoin to make it chose nestloop and it took only 20
milliseconds to complete.

Sort (cost=19618.71..19671.98 rows=21306 width=981)
Sort Key: cc.ind2
-> Nested Loop (cost=0.72..12711.82 rows=21306 width=981)
-> Nested Loop (cost=0.29..12003.57 rows=21306 width=915)
-> Append (cost=0.29..42.46 rows=30 width=28)

Thanks,

James

Attachment Content-Type Size
optimizer_misestimate_cost_partition_table_joins.txt text/plain 23.5 KB
optimizer_misestimate_cost_partition_table_joins.txt text/plain 23.5 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrei Lepikhov 2025-04-02 11:03:37 Re: partition table optimizer join cost misestimation
Previous Message Pavel Stehule 2025-04-02 06:46:48 Re: Re: proposal: schema variables