|
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 |
