Re: generic plan generate poor performance

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