generic plan generate poor performance

From: James Pang <jamespang886(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: generic plan generate poor performance
Date: 2024-02-29 14:27:42
Message-ID: CAHgTRffJ-EriTgeQQCX7xK6mgC0ZAZzpvjMnsHULHyH42S-Hhg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2024-02-29 14:38:28 Re: generic plan generate poor performance
Previous Message Tomas Vondra 2024-02-28 14:53:55 Re: Fwd: extend statistics help reduce index scan a lot of shared buffer hits.