How does hash join estimation rows ? pg v14, it make wrong rows estimation
then leave nest loop lef join that make poor sql plan. A
-> Nested Loop Left Join
(cost=171112.69..475856.90 rows=1 width=521)
-> Nested Loop Left Join
(cost=171111.31..474489.54 rows=1 width=423)
-> Hash Join
(cost=171110.76..474488.93 rows=1 width=257) <<< here ,
actually the rows is 98000 ,but optimizer returns
Hash Cond:
(((ccsm.xxx_id)::text = (cc.xxx_id)::text) AND ((ccsm.xxx_key)::text =
(cc.account_key)::text)) <<< ccsm.xx_id and ccsm.xx_key are part of
primary key.
-> Seq Scan on
cs_xxxxx ccsm (cost=0.00..254328.08 rows=4905008 width=201)
-> Hash
(cost=167540.92..167540.92 rows=237989 width=115)
-> Index Scan
using cs_xxxx_test on cs_contract cc (cost=0.43..167540.92 rows=237989
width=115)
Index
Cond: ((xx_to > CURRENT_DATE) AND ((status)::text = ANY
('{Active,Inactive,Pending}'::text[])))
-> Index Scan using
cs_xxx_pk on cs_site cs (cost=0.56..0.61 rows=1 width=203)
Index Cond:
((xxx_key)::text = (ccsm.xxx_key)::text)
Thanks,
James
