Re: generic plan generate poor performance

Hi

čt 29. 2. 2024 v 15:28 odesílatel James Pang <jamespang886(at)gmail(dot)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
>