public inbox for [email protected]
help / color / mirror / Atom feedpartition table optimizer join cost misestimation
5+ messages / 2 participants
[nested] [flat]
* partition table optimizer join cost misestimation
@ 2025-04-02 10:18 James Pang <[email protected]>
2025-04-02 11:03 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: James Pang @ 2025-04-02 10:18 UTC (permalink / raw)
To: [email protected]
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
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 , so it thought only
Sort and Merge join cost. that make
--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)
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
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.
Sort Key: cc.ind2
-> Index Scan using tablec_p1970_ind2_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..24653.56 rows=158631 width=861)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..25078.61 rows=157041 width=861)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..26336.20 rows=162959 width=868)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..25405.45 rows=158288 width=866)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..26346.47 rows=185312 width=837)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..60430.87 rows=370927 width=918)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..24798.45 rows=153782 width=862)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..23069.31 rows=144129 width=854)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..22787.87 rows=144273 width=847)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..23177.21 rows=143080 width=848)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..3028058.01 rows=21053820 width=904)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..4543050.66 rows=31569066 width=908)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..4343543.37 rows=30268060 width=902)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..1757981.66 rows=12160953 width=902)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..184239.04 rows=1317120 width=867)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..7078.83 rows=46619 width=869)
-> Index Scan using tablec_p20250403_ind2_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250404_ind2_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250405_ind2_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250406_ind2_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250407_ind2_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250408_ind2_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250409_ind2_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250410_ind2_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250411_ind2_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250412_ind2_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250413_ind2_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250414_ind2_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250415_ind2_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532)
-> Sort (cost=43.19..43.27 rows=30 width=28)
Sort Key: aa.id2
-> Append (cost=0.29..42.46 rows=30 width=28)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
--good plan
set enable_mergejoin=off
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
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)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Append (cost=0.00..378.67 rows=2003 width=903)
-> Seq Scan on tablec_p1970 cc_1 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..3.90 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..4.06 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..4.04 rows=5 width=868)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..4.03 rows=5 width=866)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..2.86 rows=3 width=837)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..6.47 rows=10 width=918)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..4.13 rows=5 width=862)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..3.69 rows=4 width=854)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..3.71 rows=4 width=847)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..3.93 rows=4 width=848)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..76.33 rows=460 width=904)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..101.72 rows=617 width=908)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..95.34 rows=573 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..45.70 rows=264 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..5.72 rows=21 width=867)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..3.02 rows=4 width=869)
Index Cond: (ind2 = aa.ind2)
-> Seq Scan on tablec_p20250403 cc_18 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250404 cc_19 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250405 cc_20 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250406 cc_21 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250407 cc_22 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250408 cc_23 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250409 cc_24 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250410 cc_25 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250411 cc_26 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250412 cc_27 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250413 cc_28 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250414 cc_29 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250415 cc_30 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
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 , so it thought only
Sort and Merge join cost. that make
--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)
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
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.
Sort Key: cc.ind2
-> Index Scan using tablec_p1970_ind2_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..24653.56 rows=158631 width=861)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..25078.61 rows=157041 width=861)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..26336.20 rows=162959 width=868)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..25405.45 rows=158288 width=866)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..26346.47 rows=185312 width=837)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..60430.87 rows=370927 width=918)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..24798.45 rows=153782 width=862)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..23069.31 rows=144129 width=854)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..22787.87 rows=144273 width=847)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..23177.21 rows=143080 width=848)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..3028058.01 rows=21053820 width=904)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..4543050.66 rows=31569066 width=908)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..4343543.37 rows=30268060 width=902)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..1757981.66 rows=12160953 width=902)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..184239.04 rows=1317120 width=867)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..7078.83 rows=46619 width=869)
-> Index Scan using tablec_p20250403_ind2_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250404_ind2_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250405_ind2_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250406_ind2_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250407_ind2_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250408_ind2_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250409_ind2_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250410_ind2_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250411_ind2_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250412_ind2_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250413_ind2_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250414_ind2_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250415_ind2_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532)
-> Sort (cost=43.19..43.27 rows=30 width=28)
Sort Key: aa.id2
-> Append (cost=0.29..42.46 rows=30 width=28)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
--good plan
set enable_mergejoin=off
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
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)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Append (cost=0.00..378.67 rows=2003 width=903)
-> Seq Scan on tablec_p1970 cc_1 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..3.90 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..4.06 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..4.04 rows=5 width=868)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..4.03 rows=5 width=866)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..2.86 rows=3 width=837)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..6.47 rows=10 width=918)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..4.13 rows=5 width=862)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..3.69 rows=4 width=854)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..3.71 rows=4 width=847)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..3.93 rows=4 width=848)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..76.33 rows=460 width=904)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..101.72 rows=617 width=908)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..95.34 rows=573 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..45.70 rows=264 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..5.72 rows=21 width=867)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..3.02 rows=4 width=869)
Index Cond: (ind2 = aa.ind2)
-> Seq Scan on tablec_p20250403 cc_18 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250404 cc_19 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250405 cc_20 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250406 cc_21 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250407 cc_22 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250408 cc_23 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250409 cc_24 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250410 cc_25 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250411 cc_26 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250412 cc_27 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250413 cc_28 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250414 cc_29 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250415 cc_30 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
Attachments:
[text/plain] optimizer_misestimate_cost_partition_table_joins.txt (23.5K, ../../CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com/3-optimizer_misestimate_cost_partition_table_joins.txt)
download | inline:
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 , so it thought only
Sort and Merge join cost. that make
--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)
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
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.
Sort Key: cc.ind2
-> Index Scan using tablec_p1970_ind2_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..24653.56 rows=158631 width=861)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..25078.61 rows=157041 width=861)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..26336.20 rows=162959 width=868)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..25405.45 rows=158288 width=866)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..26346.47 rows=185312 width=837)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..60430.87 rows=370927 width=918)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..24798.45 rows=153782 width=862)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..23069.31 rows=144129 width=854)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..22787.87 rows=144273 width=847)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..23177.21 rows=143080 width=848)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..3028058.01 rows=21053820 width=904)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..4543050.66 rows=31569066 width=908)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..4343543.37 rows=30268060 width=902)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..1757981.66 rows=12160953 width=902)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..184239.04 rows=1317120 width=867)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..7078.83 rows=46619 width=869)
-> Index Scan using tablec_p20250403_ind2_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250404_ind2_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250405_ind2_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250406_ind2_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250407_ind2_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250408_ind2_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250409_ind2_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250410_ind2_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250411_ind2_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250412_ind2_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250413_ind2_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250414_ind2_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250415_ind2_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532)
-> Sort (cost=43.19..43.27 rows=30 width=28)
Sort Key: aa.id2
-> Append (cost=0.29..42.46 rows=30 width=28)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
--good plan
set enable_mergejoin=off
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
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)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Append (cost=0.00..378.67 rows=2003 width=903)
-> Seq Scan on tablec_p1970 cc_1 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..3.90 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..4.06 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..4.04 rows=5 width=868)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..4.03 rows=5 width=866)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..2.86 rows=3 width=837)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..6.47 rows=10 width=918)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..4.13 rows=5 width=862)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..3.69 rows=4 width=854)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..3.71 rows=4 width=847)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..3.93 rows=4 width=848)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..76.33 rows=460 width=904)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..101.72 rows=617 width=908)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..95.34 rows=573 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..45.70 rows=264 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..5.72 rows=21 width=867)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..3.02 rows=4 width=869)
Index Cond: (ind2 = aa.ind2)
-> Seq Scan on tablec_p20250403 cc_18 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250404 cc_19 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250405 cc_20 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250406 cc_21 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250407 cc_22 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250408 cc_23 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250409 cc_24 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250410 cc_25 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250411 cc_26 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250412 cc_27 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250413 cc_28 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250414 cc_29 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250415 cc_30 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
[text/plain] optimizer_misestimate_cost_partition_table_joins.txt (23.5K, ../../CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com/4-optimizer_misestimate_cost_partition_table_joins.txt)
download | inline:
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 , so it thought only
Sort and Merge join cost. that make
--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)
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
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.
Sort Key: cc.ind2
-> Index Scan using tablec_p1970_ind2_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..24653.56 rows=158631 width=861)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..25078.61 rows=157041 width=861)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..26336.20 rows=162959 width=868)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..25405.45 rows=158288 width=866)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..26346.47 rows=185312 width=837)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..60430.87 rows=370927 width=918)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..24798.45 rows=153782 width=862)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..23069.31 rows=144129 width=854)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..22787.87 rows=144273 width=847)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..23177.21 rows=143080 width=848)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..3028058.01 rows=21053820 width=904)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..4543050.66 rows=31569066 width=908)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..4343543.37 rows=30268060 width=902)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..1757981.66 rows=12160953 width=902)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..184239.04 rows=1317120 width=867)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..7078.83 rows=46619 width=869)
-> Index Scan using tablec_p20250403_ind2_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250404_ind2_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250405_ind2_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250406_ind2_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250407_ind2_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250408_ind2_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250409_ind2_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250410_ind2_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250411_ind2_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250412_ind2_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250413_ind2_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250414_ind2_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532)
-> Index Scan using tablec_p20250415_ind2_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532)
-> Sort (cost=43.19..43.27 rows=30 width=28)
Sort Key: aa.id2
-> Append (cost=0.29..42.46 rows=30 width=28)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows
=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
--good plan
set enable_mergejoin=off
explain execute slowsql9(123456789);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
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)
-> Index Scan using tablea_p1970_ind1_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250318_ind1_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250319_ind1_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250320_ind1_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250321_ind1_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250322_ind1_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250323_ind1_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250324_ind1_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250325_ind1_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=1 widt
h=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250326_ind1_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250327_ind1_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250328_ind1_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250329_ind1_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250330_ind1_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250331_ind1_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250401_ind1_idx on tablea_p20250401 aa_16 (cost=0.28..2.50 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Index Scan using tablea_p20250402_ind1_idx on tablea_p20250402 aa_17 (cost=0.14..2.36 rows=1 wid
th=28)
Index Cond: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28)
Filter: (ind1 = '112619686'::bigint)
-> Append (cost=0.00..378.67 rows=2003 width=903)
-> Seq Scan on tablec_p1970 cc_1 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Index Scan using tablec_p20250318_ind2_idx on tablec_p20250318 cc_2 (cost=0.29..3.90 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250319_ind2_idx on tablec_p20250319 cc_3 (cost=0.29..4.06 rows=5 width=861)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250320_ind2_idx on tablec_p20250320 cc_4 (cost=0.29..4.04 rows=5 width=868)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250321_ind2_idx on tablec_p20250321 cc_5 (cost=0.29..4.03 rows=5 width=866)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250322_ind2_idx on tablec_p20250322 cc_6 (cost=0.42..2.86 rows=3 width=837)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250323_ind2_idx on tablec_p20250323 cc_7 (cost=0.42..6.47 rows=10 width=918)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250324_ind2_idx on tablec_p20250324 cc_8 (cost=0.29..4.13 rows=5 width=862)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250325_ind2_idx on tablec_p20250325 cc_9 (cost=0.29..3.69 rows=4 width=854)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250326_ind2_idx on tablec_p20250326 cc_10 (cost=0.29..3.71 rows=4 width=847)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250327_ind2_idx on tablec_p20250327 cc_11 (cost=0.29..3.93 rows=4 width=848)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250328_ind2_idx on tablec_p20250328 cc_12 (cost=0.44..76.33 rows=460 width=904)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250329_ind2_idx on tablec_p20250329 cc_13 (cost=0.44..101.72 rows=617 width=908)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250330_ind2_idx on tablec_p20250330 cc_14 (cost=0.44..95.34 rows=573 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250331_ind2_idx on tablec_p20250331 cc_15 (cost=0.43..45.70 rows=264 width=902)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250401_ind2_idx on tablec_p20250401 cc_16 (cost=0.43..5.72 rows=21 width=867)
Index Cond: (ind2 = aa.ind2)
-> Index Scan using tablec_p20250402_ind2_idx on tablec_p20250402 cc_17 (cost=0.29..3.02 rows=4 width=869)
Index Cond: (ind2 = aa.ind2)
-> Seq Scan on tablec_p20250403 cc_18 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250404 cc_19 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250405 cc_20 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250406 cc_21 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250407 cc_22 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250408 cc_23 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250409 cc_24 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250410 cc_25 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250411 cc_26 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250412 cc_27 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250413 cc_28 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250414 cc_29 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Seq Scan on tablec_p20250415 cc_30 (cost=0.00..0.00 rows=1 width=532)
Filter: (aa.ind2 = ind2)
-> Memoize (cost=0.43..2.65 rows=1 width=42)
Cache Key: aa.ind1
Cache Mode: logical
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=42)
Index Cond: (ind1 = aa.ind1)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition table optimizer join cost misestimation
2025-04-02 10:18 partition table optimizer join cost misestimation James Pang <[email protected]>
@ 2025-04-02 11:03 ` Andrei Lepikhov <[email protected]>
2025-04-03 00:46 ` Re: partition table optimizer join cost misestimation James Pang <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Andrei Lepikhov @ 2025-04-02 11:03 UTC (permalink / raw)
To: James Pang <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition table optimizer join cost misestimation
2025-04-02 10:18 partition table optimizer join cost misestimation James Pang <[email protected]>
2025-04-02 11:03 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
@ 2025-04-03 00:46 ` James Pang <[email protected]>
2025-04-03 06:32 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: James Pang @ 2025-04-03 00:46 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; [email protected]
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 <[email protected]> 於 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
>
\d+ tablea
Partitioned table "mct.tablea"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+-----------------------------+-----------+----------+-----------+----------+-------------+--------------+-------------
mmm_id | bigint | | not null | | plain | | |
clu_id | bigint | | | | plain | | |
dddd_id | bigint | | | | plain | | |
zzz_id | bigint | | | | plain | | |
ssr_id | bigint | | | | plain | | |
ssr_type | bigint | | | | plain | | |
mmm_stat | integer | | | | plain | | |
time | timestamp without time zone | | not null | | plain | | |
protocol | integer | | | | plain | | |
uuun | character varying(20) | | | | extended | | |
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | | |
mmm_sss_id | bigint | | | | plain | | |
remote_addr | character varying(20) | | | | extended | | |
Partition key: RANGE ("time")
Indexes:
"tablea_pkey" PRIMARY KEY, btree (mmm_id, "time")
"tablea_idx1" btree (zzz_id, mmm_sss_id, mmm_stat)
"tablea_idx2" btree (mmm_sss_id)
Publications:
"xxxx_24503"
Triggers:
xxxxx AFTER DELETE ON tablea FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_xxxxxx()
tr_tablea_update BEFORE UPDATE ON tablea FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxxx
tus_update()
Partitions: tablea_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO ('2023-09-07 00:00:00'),
tablea_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO ('2025-03-20 00:00:00'),
tablea_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO ('2025-03-21 00:00:00'),
tablea_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO ('2025-03-22 00:00:00'),
tablea_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO ('2025-03-23 00:00:00'),
tablea_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO ('2025-03-24 00:00:00'),
tablea_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO ('2025-03-25 00:00:00'),
tablea_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO ('2025-03-26 00:00:00'),
tablea_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO ('2025-03-27 00:00:00'),
tablea_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO ('2025-03-28 00:00:00'),
tablea_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO ('2025-03-29 00:00:00'),
tablea_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO ('2025-03-30 00:00:00'),
tablea_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO ('2025-03-31 00:00:00'),
tablea_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO ('2025-04-01 00:00:00'),
tablea_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-04-02 00:00:00'),
tablea_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO ('2025-04-03 00:00:00'),
tablea_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO ('2025-04-04 00:00:00'),
tablea_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO ('2025-04-05 00:00:00'),
tablea_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO ('2025-04-06 00:00:00'),
tablea_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO ('2025-04-07 00:00:00'),
tablea_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO ('2025-04-08 00:00:00'),
tablea_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO ('2025-04-09 00:00:00'),
tablea_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO ('2025-04-10 00:00:00'),
tablea_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO ('2025-04-11 00:00:00'),
tablea_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO ('2025-04-12 00:00:00'),
tablea_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO ('2025-04-13 00:00:00'),
tablea_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO ('2025-04-14 00:00:00'),
tablea_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO ('2025-04-15 00:00:00'),
tablea_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO ('2025-04-16 00:00:00'),
tablea_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO ('2025-04-17 00:00:00')
\d+ tableb
Table "tableb"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Descr
iption
------------------+-----------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------------
---------------------
sss_id | bigint | | not null | | plain | | |
zzz_id | bigint | | not null | | plain | | |
sss_ip | character varying(80) | | not null | | extended | | |
sss_type | bigint | | not null | | plain | | |
sss_hostname | character varying(80) | | | | extended | | |
sss_field1 | character varying(256) | | | | extended | | |
sss_field2 | character varying(256) | | | | extended | | |
server_field3 | bigint | | | 0 | plain | | |
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | | |
sss_field4 | character varying(512) | | | | extended | | |
sss_field5 | character varying(512) | | | | extended | | |
sss_field6 | bigint | | | 0 | plain | | |
key_sss | integer | | | 1 | plain | | |
ssstype | character varying(32) | | | | extended | | |
keyword | character varying(256) | | | | extended | | |
is_from_ab | smallint | | | 0 | plain | | |
rrr_id | bigint | | | 0 | plain | | |
instance_type | bigint | | | 0 | plain | | |
cc_id | character varying(255) | | | | extended | | |
pool_sys_id | character varying(50) | | | ''::character varying | extended | | |
memo | character varying(255) | | | '{}'::character varying | extended | | |
vvv | smallint | | | 0 | plain | | |
real_ip | character varying(100) | | | ''::character varying | extended | | |
vvv_name | character varying(100) | | | | extended | | |
pool_id | bigint | | | | plain | | |
ttt_info | character varying(100) | | | | extended | | |
label | character varying | | | '{}'::character varying | extended | | |
exp_mmm | integer | | | 0 | plain | | |
Indexes:
"tableb_pkey" PRIMARY KEY, btree (sss_id)
"idx_serverip" btree (sss_ip)
"idxvserver" btree (zzz_id, sss_type)
Foreign-key constraints:
"tableb_zzz_fk" FOREIGN KEY (zzz_id) REFERENCES tablez(zzzz_id)
Referenced by:
TABLE "xxxx_setting" CONSTRAINT "xxxxxx_fk" FOREIGN KEY (sss_id) REFERENCES tableb(sss_id)
TABLE "yyyyy" CONSTRAINT "yyyyyy_fk" FOREIGN KEY (ssss_id) REFERENCES tableb(sss_id)
Publications:
"pggmct_24503"
Triggers:
xxxx_update BEFORE UPDATE ON xxxxx FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxx()
Replica Identity: FULL
Access method: heap
\d+ tablec
Partitioned table "mct.tablec"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats
target | Description
------------------+-----------------------------+-----------+----------+-----------+----------+-------------+-------
-------+-------------
ccc_id | bigint | | not null | | plain | |
|
sss_id | bigint | | | | plain | |
|
cccments | character varying(1024) | | not null | | extended | |
|
tablec_time | timestamp without time zone | | not null | sysdate() | plain | |
|
tablec_field1 | integer | | not null | 0 | plain | |
|
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | |
|
Partition key: RANGE (tablec_time)
Indexes:
"tablec_pkey" PRIMARY KEY, btree (comment_id, tablec_time)
"sss_id_idx" btree (sss_id)
Triggers:
tr_tablec_update BEFORE UPDATE ON tablec FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigge
r_fct_tr_tablec_update()
Partitions: tablec_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO ('2023-09-07 00:00:00'),
tablec_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO ('2025-03-20 00:00:00'),
tablec_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO ('2025-03-21 00:00:00'),
tablec_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO ('2025-03-22 00:00:00'),
tablec_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO ('2025-03-23 00:00:00'),
tablec_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO ('2025-03-24 00:00:00'),
tablec_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO ('2025-03-25 00:00:00'),
tablec_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO ('2025-03-26 00:00:00'),
tablec_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO ('2025-03-27 00:00:00'),
tablec_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO ('2025-03-28 00:00:00'),
tablec_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO ('2025-03-29 00:00:00'),
tablec_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO ('2025-03-30 00:00:00'),
tablec_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO ('2025-03-31 00:00:00'),
tablec_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO ('2025-04-01 00:00:00'),
tablec_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-04-02 00:00:00'),
tablec_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO ('2025-04-03 00:00:00'),
tablec_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO ('2025-04-04 00:00:00'),
tablec_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO ('2025-04-05 00:00:00'),
tablec_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO ('2025-04-06 00:00:00'),
tablec_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO ('2025-04-07 00:00:00'),
tablec_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO ('2025-04-08 00:00:00'),
tablec_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO ('2025-04-09 00:00:00'),
tablec_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO ('2025-04-10 00:00:00'),
tablec_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO ('2025-04-11 00:00:00'),
tablec_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO ('2025-04-12 00:00:00'),
tablec_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO ('2025-04-13 00:00:00'),
tablec_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO ('2025-04-14 00:00:00'),
tablec_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO ('2025-04-15 00:00:00'),
tablec_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO ('2025-04-16 00:00:00'),
tablec_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO ('2025-04-17 00:00:00')
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
Sort (cost=9054.03..9107.10 rows=21225 width=980) (actual time=388717.679..388723.642 rows=11 loops=1)
Sort Key: cc.ccc_id
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Join (cost=112.75..2174.05 rows=21225 width=980) (actual time=388713.431..388719.421 rows=11 loops=1)
Merge Cond: (cc.sss_id = aa.mmm_id)
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Append (cost=7.99..16975829.82 rows=97820562 width=903) (actual time=7.886..383344.932 rows=96215798 loops=1)
Sort Key: cc.ind2
Buffers: shared hit=19406200 read=1583893 dirtied=9 written=4190
I/O Timings: read=36703.274 write=58.618
-> Index Scan using tablec_p1970_sss_id_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532) (actual time=0.006..0.007 rows=0
loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250318_sss_id_idx on tablec_p20250318 cc_2 (cost=0.29..24517.70 rows=158631 width=861) (actual time=0.2
19..407.243 rows=15455 loops=1)
Buffers: shared hit=3229 read=262
I/O Timings: read=38.162
-> Index Scan using tablec_p20250319_sss_id_idx on tablec_p20250319 cc_3 (cost=0.29..24929.48 rows=157041 width=861) (actual time=0.0
08..414.683 rows=16361 loops=1)
Buffers: shared hit=3498 read=222
I/O Timings: read=24.644
-> Index Scan using tablec_p20250320_sss_id_idx on tablec_p20250320 cc_4 (cost=0.29..26183.42 rows=162959 width=868) (actual time=0.3
58..386.108 rows=18463 loops=1)
Buffers: shared hit=3797 read=221
I/O Timings: read=29.677
-> Index Scan using tablec_p20250321_sss_id_idx on tablec_p20250321 cc_5 (cost=0.29..25258.03 rows=158288 width=866) (actual time=0.2
47..422.228 rows=17652 loops=1)
Buffers: shared hit=3649 read=258
I/O Timings: read=37.714
-> Index Scan using tablec_p20250322_sss_id_idx on tablec_p20250322 cc_6 (cost=0.42..26301.22 rows=185312 width=837) (actual time=0.0
07..88.273 rows=3582 loops=1)
Buffers: shared hit=680 read=19
I/O Timings: read=2.968
-> Index Scan using tablec_p20250323_sss_id_idx on tablec_p20250323 cc_7 (cost=0.42..60137.20 rows=370927 width=918) (actual time=0.7
50..652.735 rows=19659 loops=1)
Buffers: shared hit=3832 read=263
I/O Timings: read=42.237
-> Index Scan using tablec_p20250324_sss_id_idx on tablec_p20250324 cc_8 (cost=0.29..24645.04 rows=153782 width=862) (actual time=0.6
42..442.535 rows=18488 loops=1)
Buffers: shared hit=3530 read=328
I/O Timings: read=41.069
-> Index Scan using tablec_p20250325_sss_id_idx on tablec_p20250325 cc_9 (cost=0.29..22930.03 rows=144129 width=854) (actual time=0.7
57..355.577 rows=15002 loops=1)
Buffers: shared hit=2908 read=195
I/O Timings: read=25.421
-> Index Scan using tablec_p20250326_sss_id_idx on tablec_p20250326 cc_10 (cost=0.29..22642.78 rows=144273 width=847) (actual time=0.
542..397.982 rows=16996 loops=1)
Buffers: shared hit=3342 read=307
I/O Timings: read=39.785
-> Index Scan using tablec_p20250327_sss_id_idx on tablec_p20250327 cc_11 (cost=0.29..23007.96 rows=143080 width=848) (actual time=0.
479..333.069 rows=18090 loops=1)
Buffers: shared hit=3499 read=290
I/O Timings: read=27.177
-> Index Scan using tablec_p20250328_sss_id_idx on tablec_p20250328 cc_12 (cost=0.44..3027818.84 rows=21053820 width=904) (actual tim
e=0.010..51767.500 rows=21045366 loops=1)
Buffers: shared hit=3891912 read=625578 written=2452
I/O Timings: read=15198.004 write=31.926
-> Index Scan using tablec_p20250329_sss_id_idx on tablec_p20250329 cc_13 (cost=0.44..4542850.00 rows=31569066 width=908) (actual tim
e=0.493..34196.241 rows=31570870 loops=1)
Buffers: shared hit=6181410 read=624887 written=745
I/O Timings: read=2279.724 write=13.038
-> Index Scan using tablec_p20250330_sss_id_idx on tablec_p20250330 cc_14 (cost=0.44..4343188.38 rows=30268060 width=902) (actual tim
e=0.009..204102.864 rows=30298328 loops=1)
Buffers: shared hit=6575789 read=225742 written=236
I/O Timings: read=13574.608 write=3.893
-> Index Scan using tablec_p20250331_sss_id_idx on tablec_p20250331 cc_15 (cost=0.43..1757701.95 rows=12160953 width=902) (actual tim
e=0.982..78204.583 rows=12177280 loops=1)
Buffers: shared hit=2533546 read=99131 written=757
I/O Timings: read=5231.356 write=9.761
-> Index Scan using tablec_p20250401_sss_id_idx on tablec_p20250401 cc_16 (cost=0.42..134605.16 rows=990226 width=847) (actual time=0
.008..2424.281 rows=964217 loops=1)
Buffers: shared hit=191564 read=6190 dirtied=9
I/O Timings: read=110.728
-> Index Scan using tablec_p20250402_sss_id_idx on tablec_p20250402 cc_17 (cost=0.12..2.34 rows=1 width=532) (actual time=0.003..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250403_sss_id_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250404_sss_id_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532) (actual time=0.247..0.24
7 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250405_sss_id_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250406_sss_id_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532) (actual time=0.442..0.44
5 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250407_sss_id_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250408_sss_id_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250409_sss_id_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532) (actual time=0.433..0.43
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250410_sss_id_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250411_sss_id_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250412_sss_id_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532) (actual time=0.252..0.25
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250413_sss_id_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532) (actual time=0.333..0.33
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250414_sss_id_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532) (actual time=0.323..0.32
4 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250415_sss_id_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532) (actual time=0.315..0.67
8 rows=0 loops=1)
Buffers: shared hit=1
-> Sort (cost=56.94..57.01 rows=30 width=61) (actual time=7.127..12.496 rows=1 loops=1)
Sort Key: aa.mmm_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Nested Loop (cost=0.72..56.20 rows=30 width=61) (actual time=6.216..12.488 rows=1 loops=1)
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Append (cost=0.29..40.09 rows=30 width=28) (actual time=5.704..11.974 rows=1 loops=1)
Buffers: shared hit=32 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p1970_mmm_sss_id_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28) (actual time=0.921..0.922 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250318_mmm_sss_id_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.005..0.010 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250319_mmm_sss_id_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.250 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250320_mmm_sss_id_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250321_mmm_sss_id_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.006 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250322_mmm_sss_id_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.271 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250323_mmm_sss_id_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.005 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250324_mmm_sss_id_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.505..0.857 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250325_mmm_sss_id_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250326_mmm_sss_id_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.368..0.820 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250327_mmm_sss_id_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.662..0.664 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250328_mmm_sss_id_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.310..0.311 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250329_mmm_sss_id_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.424..0.712 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250330_mmm_sss_id_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28) (actual time=0.682..0.683 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250331_mmm_sss_id_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.943..1.262 rows=1 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p20250401_mmm_sss_id_idx on tablea_p20250401 aa_16 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.414..0.414 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Seq Scan on tablea_p20250402 aa_17 (cost=0.00..0.00 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28) (actual time=0.266..0.267 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28) (actual time=0.153..0.507 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.003 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28) (actual time=0.042..0.042 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.066 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Memoize (cost=0.43..2.65 rows=1 width=41) (actual time=0.509..0.510 rows=1 loops=1)
Cache Key: aa.sss_id
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=4
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=41) (actual time=0.504..0.504 rows=1 loops=1)
Index Cond: (sss_id = aa.sss_id)
Buffers: shared hit=4
Planning Time: 7.588 ms
Execution Time: 388786.455 ms
(186 rows)
Attachments:
[text/plain] table_and_explain_buffers.txt (32.1K, ../../CAHgTRfdOsgSkHcgB+q-=M-+hb_jyyV1icwTSaCHFbby8C-CUxQ@mail.gmail.com/3-table_and_explain_buffers.txt)
download | inline:
\d+ tablea
Partitioned table "mct.tablea"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+-----------------------------+-----------+----------+-----------+----------+-------------+--------------+-------------
mmm_id | bigint | | not null | | plain | | |
clu_id | bigint | | | | plain | | |
dddd_id | bigint | | | | plain | | |
zzz_id | bigint | | | | plain | | |
ssr_id | bigint | | | | plain | | |
ssr_type | bigint | | | | plain | | |
mmm_stat | integer | | | | plain | | |
time | timestamp without time zone | | not null | | plain | | |
protocol | integer | | | | plain | | |
uuun | character varying(20) | | | | extended | | |
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | | |
mmm_sss_id | bigint | | | | plain | | |
remote_addr | character varying(20) | | | | extended | | |
Partition key: RANGE ("time")
Indexes:
"tablea_pkey" PRIMARY KEY, btree (mmm_id, "time")
"tablea_idx1" btree (zzz_id, mmm_sss_id, mmm_stat)
"tablea_idx2" btree (mmm_sss_id)
Publications:
"xxxx_24503"
Triggers:
xxxxx AFTER DELETE ON tablea FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_xxxxxx()
tr_tablea_update BEFORE UPDATE ON tablea FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxxx
tus_update()
Partitions: tablea_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO ('2023-09-07 00:00:00'),
tablea_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO ('2025-03-20 00:00:00'),
tablea_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO ('2025-03-21 00:00:00'),
tablea_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO ('2025-03-22 00:00:00'),
tablea_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO ('2025-03-23 00:00:00'),
tablea_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO ('2025-03-24 00:00:00'),
tablea_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO ('2025-03-25 00:00:00'),
tablea_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO ('2025-03-26 00:00:00'),
tablea_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO ('2025-03-27 00:00:00'),
tablea_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO ('2025-03-28 00:00:00'),
tablea_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO ('2025-03-29 00:00:00'),
tablea_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO ('2025-03-30 00:00:00'),
tablea_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO ('2025-03-31 00:00:00'),
tablea_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO ('2025-04-01 00:00:00'),
tablea_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-04-02 00:00:00'),
tablea_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO ('2025-04-03 00:00:00'),
tablea_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO ('2025-04-04 00:00:00'),
tablea_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO ('2025-04-05 00:00:00'),
tablea_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO ('2025-04-06 00:00:00'),
tablea_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO ('2025-04-07 00:00:00'),
tablea_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO ('2025-04-08 00:00:00'),
tablea_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO ('2025-04-09 00:00:00'),
tablea_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO ('2025-04-10 00:00:00'),
tablea_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO ('2025-04-11 00:00:00'),
tablea_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO ('2025-04-12 00:00:00'),
tablea_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO ('2025-04-13 00:00:00'),
tablea_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO ('2025-04-14 00:00:00'),
tablea_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO ('2025-04-15 00:00:00'),
tablea_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO ('2025-04-16 00:00:00'),
tablea_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO ('2025-04-17 00:00:00')
\d+ tableb
Table "tableb"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Descr
iption
------------------+-----------------------------+-----------+----------+-------------------------+----------+-------------+--------------+-------------------
---------------------
sss_id | bigint | | not null | | plain | | |
zzz_id | bigint | | not null | | plain | | |
sss_ip | character varying(80) | | not null | | extended | | |
sss_type | bigint | | not null | | plain | | |
sss_hostname | character varying(80) | | | | extended | | |
sss_field1 | character varying(256) | | | | extended | | |
sss_field2 | character varying(256) | | | | extended | | |
server_field3 | bigint | | | 0 | plain | | |
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | | |
sss_field4 | character varying(512) | | | | extended | | |
sss_field5 | character varying(512) | | | | extended | | |
sss_field6 | bigint | | | 0 | plain | | |
key_sss | integer | | | 1 | plain | | |
ssstype | character varying(32) | | | | extended | | |
keyword | character varying(256) | | | | extended | | |
is_from_ab | smallint | | | 0 | plain | | |
rrr_id | bigint | | | 0 | plain | | |
instance_type | bigint | | | 0 | plain | | |
cc_id | character varying(255) | | | | extended | | |
pool_sys_id | character varying(50) | | | ''::character varying | extended | | |
memo | character varying(255) | | | '{}'::character varying | extended | | |
vvv | smallint | | | 0 | plain | | |
real_ip | character varying(100) | | | ''::character varying | extended | | |
vvv_name | character varying(100) | | | | extended | | |
pool_id | bigint | | | | plain | | |
ttt_info | character varying(100) | | | | extended | | |
label | character varying | | | '{}'::character varying | extended | | |
exp_mmm | integer | | | 0 | plain | | |
Indexes:
"tableb_pkey" PRIMARY KEY, btree (sss_id)
"idx_serverip" btree (sss_ip)
"idxvserver" btree (zzz_id, sss_type)
Foreign-key constraints:
"tableb_zzz_fk" FOREIGN KEY (zzz_id) REFERENCES tablez(zzzz_id)
Referenced by:
TABLE "xxxx_setting" CONSTRAINT "xxxxxx_fk" FOREIGN KEY (sss_id) REFERENCES tableb(sss_id)
TABLE "yyyyy" CONSTRAINT "yyyyyy_fk" FOREIGN KEY (ssss_id) REFERENCES tableb(sss_id)
Publications:
"pggmct_24503"
Triggers:
xxxx_update BEFORE UPDATE ON xxxxx FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigger_fct_tr_xxxx()
Replica Identity: FULL
Access method: heap
\d+ tablec
Partitioned table "mct.tablec"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats
target | Description
------------------+-----------------------------+-----------+----------+-----------+----------+-------------+-------
-------+-------------
ccc_id | bigint | | not null | | plain | |
|
sss_id | bigint | | | | plain | |
|
cccments | character varying(1024) | | not null | | extended | |
|
tablec_time | timestamp without time zone | | not null | sysdate() | plain | |
|
tablec_field1 | integer | | not null | 0 | plain | |
|
lastmodifiedtime | timestamp without time zone | | | sysdate() | plain | |
|
Partition key: RANGE (tablec_time)
Indexes:
"tablec_pkey" PRIMARY KEY, btree (comment_id, tablec_time)
"sss_id_idx" btree (sss_id)
Triggers:
tr_tablec_update BEFORE UPDATE ON tablec FOR EACH ROW WHEN (USER !~~ 'SPLEX%'::text) EXECUTE FUNCTION trigge
r_fct_tr_tablec_update()
Partitions: tablec_p1970 FOR VALUES FROM ('1970-01-01 00:00:00') TO ('2023-09-07 00:00:00'),
tablec_p20250319 FOR VALUES FROM ('2025-03-19 00:00:00') TO ('2025-03-20 00:00:00'),
tablec_p20250320 FOR VALUES FROM ('2025-03-20 00:00:00') TO ('2025-03-21 00:00:00'),
tablec_p20250321 FOR VALUES FROM ('2025-03-21 00:00:00') TO ('2025-03-22 00:00:00'),
tablec_p20250322 FOR VALUES FROM ('2025-03-22 00:00:00') TO ('2025-03-23 00:00:00'),
tablec_p20250323 FOR VALUES FROM ('2025-03-23 00:00:00') TO ('2025-03-24 00:00:00'),
tablec_p20250324 FOR VALUES FROM ('2025-03-24 00:00:00') TO ('2025-03-25 00:00:00'),
tablec_p20250325 FOR VALUES FROM ('2025-03-25 00:00:00') TO ('2025-03-26 00:00:00'),
tablec_p20250326 FOR VALUES FROM ('2025-03-26 00:00:00') TO ('2025-03-27 00:00:00'),
tablec_p20250327 FOR VALUES FROM ('2025-03-27 00:00:00') TO ('2025-03-28 00:00:00'),
tablec_p20250328 FOR VALUES FROM ('2025-03-28 00:00:00') TO ('2025-03-29 00:00:00'),
tablec_p20250329 FOR VALUES FROM ('2025-03-29 00:00:00') TO ('2025-03-30 00:00:00'),
tablec_p20250330 FOR VALUES FROM ('2025-03-30 00:00:00') TO ('2025-03-31 00:00:00'),
tablec_p20250331 FOR VALUES FROM ('2025-03-31 00:00:00') TO ('2025-04-01 00:00:00'),
tablec_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-04-02 00:00:00'),
tablec_p20250402 FOR VALUES FROM ('2025-04-02 00:00:00') TO ('2025-04-03 00:00:00'),
tablec_p20250403 FOR VALUES FROM ('2025-04-03 00:00:00') TO ('2025-04-04 00:00:00'),
tablec_p20250404 FOR VALUES FROM ('2025-04-04 00:00:00') TO ('2025-04-05 00:00:00'),
tablec_p20250405 FOR VALUES FROM ('2025-04-05 00:00:00') TO ('2025-04-06 00:00:00'),
tablec_p20250406 FOR VALUES FROM ('2025-04-06 00:00:00') TO ('2025-04-07 00:00:00'),
tablec_p20250407 FOR VALUES FROM ('2025-04-07 00:00:00') TO ('2025-04-08 00:00:00'),
tablec_p20250408 FOR VALUES FROM ('2025-04-08 00:00:00') TO ('2025-04-09 00:00:00'),
tablec_p20250409 FOR VALUES FROM ('2025-04-09 00:00:00') TO ('2025-04-10 00:00:00'),
tablec_p20250410 FOR VALUES FROM ('2025-04-10 00:00:00') TO ('2025-04-11 00:00:00'),
tablec_p20250411 FOR VALUES FROM ('2025-04-11 00:00:00') TO ('2025-04-12 00:00:00'),
tablec_p20250412 FOR VALUES FROM ('2025-04-12 00:00:00') TO ('2025-04-13 00:00:00'),
tablec_p20250413 FOR VALUES FROM ('2025-04-13 00:00:00') TO ('2025-04-14 00:00:00'),
tablec_p20250414 FOR VALUES FROM ('2025-04-14 00:00:00') TO ('2025-04-15 00:00:00'),
tablec_p20250415 FOR VALUES FROM ('2025-04-15 00:00:00') TO ('2025-04-16 00:00:00'),
tablec_p20250416 FOR VALUES FROM ('2025-04-16 00:00:00') TO ('2025-04-17 00:00:00')
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
Sort (cost=9054.03..9107.10 rows=21225 width=980) (actual time=388717.679..388723.642 rows=11 loops=1)
Sort Key: cc.ccc_id
Sort Method: quicksort Memory: 45kB
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Join (cost=112.75..2174.05 rows=21225 width=980) (actual time=388713.431..388719.421 rows=11 loops=1)
Merge Cond: (cc.sss_id = aa.mmm_id)
Buffers: shared hit=19406236 read=1583894 dirtied=9 written=4190
I/O Timings: read=36703.795 write=58.618
-> Merge Append (cost=7.99..16975829.82 rows=97820562 width=903) (actual time=7.886..383344.932 rows=96215798 loops=1)
Sort Key: cc.ind2
Buffers: shared hit=19406200 read=1583893 dirtied=9 written=4190
I/O Timings: read=36703.274 write=58.618
-> Index Scan using tablec_p1970_sss_id_idx on tablec_p1970 cc_1 (cost=0.12..2.34 rows=1 width=532) (actual time=0.006..0.007 rows=0
loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250318_sss_id_idx on tablec_p20250318 cc_2 (cost=0.29..24517.70 rows=158631 width=861) (actual time=0.2
19..407.243 rows=15455 loops=1)
Buffers: shared hit=3229 read=262
I/O Timings: read=38.162
-> Index Scan using tablec_p20250319_sss_id_idx on tablec_p20250319 cc_3 (cost=0.29..24929.48 rows=157041 width=861) (actual time=0.0
08..414.683 rows=16361 loops=1)
Buffers: shared hit=3498 read=222
I/O Timings: read=24.644
-> Index Scan using tablec_p20250320_sss_id_idx on tablec_p20250320 cc_4 (cost=0.29..26183.42 rows=162959 width=868) (actual time=0.3
58..386.108 rows=18463 loops=1)
Buffers: shared hit=3797 read=221
I/O Timings: read=29.677
-> Index Scan using tablec_p20250321_sss_id_idx on tablec_p20250321 cc_5 (cost=0.29..25258.03 rows=158288 width=866) (actual time=0.2
47..422.228 rows=17652 loops=1)
Buffers: shared hit=3649 read=258
I/O Timings: read=37.714
-> Index Scan using tablec_p20250322_sss_id_idx on tablec_p20250322 cc_6 (cost=0.42..26301.22 rows=185312 width=837) (actual time=0.0
07..88.273 rows=3582 loops=1)
Buffers: shared hit=680 read=19
I/O Timings: read=2.968
-> Index Scan using tablec_p20250323_sss_id_idx on tablec_p20250323 cc_7 (cost=0.42..60137.20 rows=370927 width=918) (actual time=0.7
50..652.735 rows=19659 loops=1)
Buffers: shared hit=3832 read=263
I/O Timings: read=42.237
-> Index Scan using tablec_p20250324_sss_id_idx on tablec_p20250324 cc_8 (cost=0.29..24645.04 rows=153782 width=862) (actual time=0.6
42..442.535 rows=18488 loops=1)
Buffers: shared hit=3530 read=328
I/O Timings: read=41.069
-> Index Scan using tablec_p20250325_sss_id_idx on tablec_p20250325 cc_9 (cost=0.29..22930.03 rows=144129 width=854) (actual time=0.7
57..355.577 rows=15002 loops=1)
Buffers: shared hit=2908 read=195
I/O Timings: read=25.421
-> Index Scan using tablec_p20250326_sss_id_idx on tablec_p20250326 cc_10 (cost=0.29..22642.78 rows=144273 width=847) (actual time=0.
542..397.982 rows=16996 loops=1)
Buffers: shared hit=3342 read=307
I/O Timings: read=39.785
-> Index Scan using tablec_p20250327_sss_id_idx on tablec_p20250327 cc_11 (cost=0.29..23007.96 rows=143080 width=848) (actual time=0.
479..333.069 rows=18090 loops=1)
Buffers: shared hit=3499 read=290
I/O Timings: read=27.177
-> Index Scan using tablec_p20250328_sss_id_idx on tablec_p20250328 cc_12 (cost=0.44..3027818.84 rows=21053820 width=904) (actual tim
e=0.010..51767.500 rows=21045366 loops=1)
Buffers: shared hit=3891912 read=625578 written=2452
I/O Timings: read=15198.004 write=31.926
-> Index Scan using tablec_p20250329_sss_id_idx on tablec_p20250329 cc_13 (cost=0.44..4542850.00 rows=31569066 width=908) (actual tim
e=0.493..34196.241 rows=31570870 loops=1)
Buffers: shared hit=6181410 read=624887 written=745
I/O Timings: read=2279.724 write=13.038
-> Index Scan using tablec_p20250330_sss_id_idx on tablec_p20250330 cc_14 (cost=0.44..4343188.38 rows=30268060 width=902) (actual tim
e=0.009..204102.864 rows=30298328 loops=1)
Buffers: shared hit=6575789 read=225742 written=236
I/O Timings: read=13574.608 write=3.893
-> Index Scan using tablec_p20250331_sss_id_idx on tablec_p20250331 cc_15 (cost=0.43..1757701.95 rows=12160953 width=902) (actual tim
e=0.982..78204.583 rows=12177280 loops=1)
Buffers: shared hit=2533546 read=99131 written=757
I/O Timings: read=5231.356 write=9.761
-> Index Scan using tablec_p20250401_sss_id_idx on tablec_p20250401 cc_16 (cost=0.42..134605.16 rows=990226 width=847) (actual time=0
.008..2424.281 rows=964217 loops=1)
Buffers: shared hit=191564 read=6190 dirtied=9
I/O Timings: read=110.728
-> Index Scan using tablec_p20250402_sss_id_idx on tablec_p20250402 cc_17 (cost=0.12..2.34 rows=1 width=532) (actual time=0.003..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250403_sss_id_idx on tablec_p20250403 cc_18 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250404_sss_id_idx on tablec_p20250404 cc_19 (cost=0.12..2.34 rows=1 width=532) (actual time=0.247..0.24
7 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250405_sss_id_idx on tablec_p20250405 cc_20 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250406_sss_id_idx on tablec_p20250406 cc_21 (cost=0.12..2.34 rows=1 width=532) (actual time=0.442..0.44
5 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250407_sss_id_idx on tablec_p20250407 cc_22 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250408_sss_id_idx on tablec_p20250408 cc_23 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250409_sss_id_idx on tablec_p20250409 cc_24 (cost=0.12..2.34 rows=1 width=532) (actual time=0.433..0.43
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250410_sss_id_idx on tablec_p20250410 cc_25 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250411_sss_id_idx on tablec_p20250411 cc_26 (cost=0.12..2.34 rows=1 width=532) (actual time=0.002..0.00
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250412_sss_id_idx on tablec_p20250412 cc_27 (cost=0.12..2.34 rows=1 width=532) (actual time=0.252..0.25
2 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250413_sss_id_idx on tablec_p20250413 cc_28 (cost=0.12..2.34 rows=1 width=532) (actual time=0.333..0.33
3 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250414_sss_id_idx on tablec_p20250414 cc_29 (cost=0.12..2.34 rows=1 width=532) (actual time=0.323..0.32
4 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using tablec_p20250415_sss_id_idx on tablec_p20250415 cc_30 (cost=0.12..2.34 rows=1 width=532) (actual time=0.315..0.67
8 rows=0 loops=1)
Buffers: shared hit=1
-> Sort (cost=56.94..57.01 rows=30 width=61) (actual time=7.127..12.496 rows=1 loops=1)
Sort Key: aa.mmm_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Nested Loop (cost=0.72..56.20 rows=30 width=61) (actual time=6.216..12.488 rows=1 loops=1)
Buffers: shared hit=36 read=1
I/O Timings: read=0.521
-> Append (cost=0.29..40.09 rows=30 width=28) (actual time=5.704..11.974 rows=1 loops=1)
Buffers: shared hit=32 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p1970_mmm_sss_id_idx on tablea_p1970 aa_1 (cost=0.29..2.51 rows=1 width=
28) (actual time=0.921..0.922 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250318_mmm_sss_id_idx on tablea_p20250318 aa_2 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.005..0.010 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250319_mmm_sss_id_idx on tablea_p20250319 aa_3 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.250 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250320_mmm_sss_id_idx on tablea_p20250320 aa_4 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250321_mmm_sss_id_idx on tablea_p20250321 aa_5 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.006 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250322_mmm_sss_id_idx on tablea_p20250322 aa_6 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.271 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250323_mmm_sss_id_idx on tablea_p20250323 aa_7 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.004..0.005 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250324_mmm_sss_id_idx on tablea_p20250324 aa_8 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.505..0.857 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250325_mmm_sss_id_idx on tablea_p20250325 aa_9 (cost=0.28..2.50 rows=
1 width=28) (actual time=0.430..0.431 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250326_mmm_sss_id_idx on tablea_p20250326 aa_10 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.368..0.820 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250327_mmm_sss_id_idx on tablea_p20250327 aa_11 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.662..0.664 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250328_mmm_sss_id_idx on tablea_p20250328 aa_12 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.310..0.311 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250329_mmm_sss_id_idx on tablea_p20250329 aa_13 (cost=0.28..2.50 rows
=1 width=28) (actual time=0.424..0.712 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250330_mmm_sss_id_idx on tablea_p20250330 aa_14 (cost=0.28..2.49 rows
=1 width=28) (actual time=0.682..0.683 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Index Scan using tablea_p20250331_mmm_sss_id_idx on tablea_p20250331 aa_15 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.943..1.262 rows=1 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2 read=1
I/O Timings: read=0.521
-> Index Scan using tablea_p20250401_mmm_sss_id_idx on tablea_p20250401 aa_16 (cost=0.27..2.49 rows
=1 width=28) (actual time=0.414..0.414 rows=0 loops=1)
Index Cond: (mmm_sss_id = $1)
Buffers: shared hit=2
-> Seq Scan on tablea_p20250402 aa_17 (cost=0.00..0.00 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250403 aa_18 (cost=0.00..0.00 rows=1 width=28) (actual time=0.266..0.267 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250404 aa_19 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250405 aa_20 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250406 aa_21 (cost=0.00..0.00 rows=1 width=28) (actual time=0.153..0.507 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250407 aa_22 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250408 aa_23 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250409 aa_24 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.003 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250410 aa_25 (cost=0.00..0.00 rows=1 width=28) (actual time=0.042..0.042 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250411 aa_26 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250412 aa_27 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.066 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250413 aa_28 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250414 aa_29 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Seq Scan on tablea_p20250415 aa_30 (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (mmm_sss_id = $1)
-> Memoize (cost=0.43..2.65 rows=1 width=41) (actual time=0.509..0.510 rows=1 loops=1)
Cache Key: aa.sss_id
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=4
-> Index Scan using tableb_pkey on tableb bb (cost=0.42..2.64 rows=1 width=41) (actual time=0.504..0.504 rows=1 loops=1)
Index Cond: (sss_id = aa.sss_id)
Buffers: shared hit=4
Planning Time: 7.588 ms
Execution Time: 388786.455 ms
(186 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition table optimizer join cost misestimation
2025-04-02 10:18 partition table optimizer join cost misestimation James Pang <[email protected]>
2025-04-02 11:03 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
2025-04-03 00:46 ` Re: partition table optimizer join cost misestimation James Pang <[email protected]>
@ 2025-04-03 06:32 ` Andrei Lepikhov <[email protected]>
2025-04-03 12:38 ` Re: partition table optimizer join cost misestimation James Pang <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Andrei Lepikhov @ 2025-04-03 06:32 UTC (permalink / raw)
To: James Pang <[email protected]>; [email protected]
On 4/3/25 02:46, James Pang wrote:
> 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.
I think it is almost enough to identify the issue. Let me ask you the
following questions:
1. Can you provide min and max values in columns cc.sss_id and aa.mmm_id?
2. How often do you analyze your *parent* tables tablea and tablec?
Remember, if you want to build statistics on a partitioned table (not a
partition), you have to explicitly call
ANALYZE tablea,tablec;
mentioning these tables in the analyze list.
3. May you provide a dump of pg_statistic on attributes cc.sss_id and
aa.mmm_id?
4. Is there a possibility of changing a single code line and rebuilding
your DB instance to check a conjecture?
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition table optimizer join cost misestimation
2025-04-02 10:18 partition table optimizer join cost misestimation James Pang <[email protected]>
2025-04-02 11:03 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
2025-04-03 00:46 ` Re: partition table optimizer join cost misestimation James Pang <[email protected]>
2025-04-03 06:32 ` Re: partition table optimizer join cost misestimation Andrei Lepikhov <[email protected]>
@ 2025-04-03 12:38 ` James Pang <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: James Pang @ 2025-04-03 12:38 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; [email protected]
Follow your suggestion to increase statistics_target (I increase
target_statistic to 1000 for aa.mmm_id and cc.sss_id ,analyze tablea,
tablec again), optimizer choose the good SQL plan.
Thanks,
James
Andrei Lepikhov <[email protected]> 於 2025年4月3日週四 下午4:44寫道：
> On 4/3/25 10:04, James Pang wrote:
> > one more comments, for vacuum/analyze, we enable autovacuum=on, that may
> > sometimes automatically analyze part of partition table directly.
> I see some incoherence in data provided. The ranges of joining columns
> intersects only partially:
>
> cc.sss_id: 5 100 001 101 - 7 999 999 601
> aa.mmm_id: 2 005 242 651 - 5 726 786 022
>
> So, the intersection range 5100001101 - 5726786022 - is about 10% of the
> whole range.
> But I don't see it in the column statistics you provided. And Postgres
> may do the same.
> So, at first, I do recommend increasing default_statistics_target or
> just statistics_target on partitioned tables only. For such big tables I
> usually set it at least to the 2500.
> Also, don't trust in autovacuum on partitioned table - to make an
> analyse it needs to lock each partition which is highly unlikely to happen.
> So, increase stat target, make ANALYZE tablea, tablec and let me know
> what will happen. May be after the analyse statistics will be more
> consistent.
>
> --
> regards, Andrei Lepikhov
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-03 12:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-02 10:18 partition table optimizer join cost misestimation James Pang <[email protected]>
2025-04-02 11:03 ` Andrei Lepikhov <[email protected]>
2025-04-03 00:46 ` James Pang <[email protected]>
2025-04-03 06:32 ` Andrei Lepikhov <[email protected]>
2025-04-03 12:38 ` James Pang <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox