| 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: | Whole Thread | Raw Message | 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
| 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 |