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)