optimizer_misestimate_cost_partition_table_joins.txt
text/plain
Filename: optimizer_misestimate_cost_partition_table_joins.txt
Type: text/plain
Part: 1
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)