Hi čt 29. 2. 2024 v 15:28 odesílatel James Pang <jamespang...@gmail.com> napsal:
> 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. > please, send result of EXPLAIN ANALYZE, try to run VACUUM ANALYZE before probably there will not good estimation > > 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, > > Regards Pavel > James >