Hi,
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)
Thanks,
James
