we create statistics (dependencies,distinct) on (cccid,sssid); with real bind variables , it make good plan of Hash join , but when it try to generic plan, it automatically convert to Nestloop and then very poor sql performance. why generic plan change to to a poor plan "nestloop" ? how to fix that.
explain execute j2eemtgatdlistsql16(27115336789879,15818676);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=11513.05..25541.17 rows=773 width=1111)
Hash Cond: ((a.sssid = b.sssid) AND (a.cccid = b.cccid) AND (a.uuid = b.uuid))
-> Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a (cost=0.43..14010.19 rows=773 width=1059)
Index Cond: ((cccfid = '27115336789879'::bigint) AND (sssid = '15818676'::bigint))
Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
-> Hash (cost=11330.73..11330.73 rows=10393 width=51)
-> Index Scan using idx_mtgccclstext_cccsssid_j2 on mtgcccclistextj2 b (cost=0.43..11330.73 rows=10393 width=51)
Index Cond: ((cccid = '27115336789879'::bigint) AND (siteid = '15818676'::bigint))
(8 rows)
explain execute j2eemtgatdlistsql16(27115336789879,15818676);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.87..289.53 rows=14 width=1111)
-> Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a (cost=0.43..251.94 rows=14 width=1059)
Index Cond: ((cccid = $1) AND (sssid = $2))
Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
-> Index Scan using idx_mtgccclstext_cccsssid_j2 on mtgcccclistextj2 b (cost=0.43..2.66 rows=1 width=51)
Index Cond: ((cccid = a.cccid) AND (cccid = $1) AND (sssid = a.sssid) AND (sssid = $2))
Filter: (a.uuid = uuid)
(7 rows)