Thread

1. generic plan generate poor performance

   James Pang <jamespang886@gmail.com> — 2024-02-29T14:27:42Z

   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
   

2. Re: generic plan generate poor performance

   Pavel Stehule <pavel.stehule@gmail.com> — 2024-02-29T14:38:28Z

   Hi
   
   čt 29. 2. 2024 v 15:28 odesílatel James Pang <jamespang886@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
   >