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