From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: James Pang (chaolpan) <chaolpan@cisco.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: wrong rows estimation by hash join
Date: Sat, 10 Jun 2023 22:38:31 +0200
Message-ID: <8f79dacb-332e-7bde-e9e9-1a2319aa51f4@enterprisedb.com> (raw)
In-Reply-To: <PH0PR11MB51913DF51C3C91FF345B04BDD651A@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB51913DF51C3C91FF345B04BDD651A@PH0PR11MB5191.namprd11.prod.outlook.com>
Hi,
On 6/9/23 10:36, James Pang (chaolpan) wrote:
> 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
>
I doubt this is specific to hashjoins, we estimate cardinality the same
way for all joins (or more precisely, we estimate it before picking the
particular join method).
I'm just guessing, but I'd bet the join condition is correlated with the
filter on cs_contract:
> -> 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[])))
>
If you remove that condition, does the estimate improve?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, chaolpan@cisco.com, pgsql-performance@lists.postgresql.org
Subject: Re: wrong rows estimation by hash join
In-Reply-To: <8f79dacb-332e-7bde-e9e9-1a2319aa51f4@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox