wrong rows estimation by hash join

From: "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: wrong rows estimation by hash join
Date: 2023-06-09 08:36:01
Message-ID: PH0PR11MB51913DF51C3C91FF345B04BDD651A@PH0PR11MB5191.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2023-06-10 20:38:31 Re: wrong rows estimation by hash join
Previous Message Satalabaha Postgres 2023-06-05 11:55:33 Re: Weird behavior of INSERT QUERY