Message-ID: <8e629488-cb14-5f17-adb3-c02205ebacab@enterprisedb.com>
Date: Mon, 21 Aug 2023 12:56:18 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more
 than factor 500
Content-Language: en-US
To: Jian Guo <gjian@vmware.com>, Hans Buschmann <buschmann@nidsa.net>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Cc: Zhenghua Lyu <zlyu@vmware.com>
References: <4504e67078d648cdac3651b2960da6e7@nidsa.net>
 <SN6PR05MB5199BB4BB8C982BB519B6660C417A@SN6PR05MB5199.namprd05.prod.outlook.com>
 <46a7d950-7ca7-8ab9-76dd-f0bc7611c609@enterprisedb.com>
 <SN6PR05MB5199C0095508555B7D8D0D23C41EA@SN6PR05MB5199.namprd05.prod.outlook.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <SN6PR05MB5199C0095508555B7D8D0D23C41EA@SN6PR05MB5199.namprd05.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8e629488-cb14-5f17-adb3-c02205ebacab%40enterprisedb.com>
Precedence: bulk

On 8/21/23 10:16, Jian Guo wrote:
> Hi hackers,
> 
> I found a new approach to fix this issue, which seems better, so I would
> like to post another version of the patch here. The origin patch made
> the assumption of the values of Vars from CTE must be unique, which
> could be very wrong. This patch examines variables for Vars inside CTE,
> which avoided the bad assumption, so the results could be much more
> accurate.
> 

No problem with posting a reworked patch to the same thread, but I'll
repeat my suggestion to register this in the CF app [1]. The benefit is
that people are more likely to notice the patch and also cfbot [2] will
run regression tests.

[1] https://commitfest.postgresql.org
[2] http://cfbot.cputube.org/


-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company