Message-ID: <0b75eb10-5a48-4bea-a5d1-898c52dcb932@gmail.com>
Date: Wed, 29 Jan 2025 15:57:15 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Simple query with Planner underestimating rows.
To: =?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= <xocas89@gmail.com>
References: 
 <CACJPJu-0u221Bker1yn=AJ3xJbW0j29mSM=EvkA_SXecv75hqw@mail.gmail.com>
 <a383df07-989d-4903-8eb7-c93d4a16b628@gmail.com>
 <CACJPJu8CT7wDgKXpx9b7HjavywsjqWUt8Zd3f=bFBa_3tYRs1Q@mail.gmail.com>
Content-Language: en-AU
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CACJPJu8CT7wDgKXpx9b7HjavywsjqWUt8Zd3f=bFBa_3tYRs1Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/0b75eb10-5a48-4bea-a5d1-898c52dcb932%40gmail.com>
Precedence: bulk

On 1/29/25 15:32, Felipe López Montes wrote:
> Hi,
> 
> Thank you for your reply,
> 
> I have already tried creating such statistics on t3.programme_identifier 
> and t3.participant_identifier and the plan is the same, however I cannot 
> create them for the right part of the join as they are from different 
> tables (t1 and t2)
Of course, because for now, a join clause can't be estimated by extended 
statistics. It applies only to a scan filter (clause referencing only 
one relation).
I have meant that with the development patch [1] applied, you may create 
two statistics on t3(participant_identifier,programme_identifier) and 
t2(participant_identifier,programme_identifier). These statistics would 
then be used to estimate the join clause and may resolve the problem.

[1] using extended statistics to improve join estimates
https://www.postgresql.org/message-id/flat/c8c0ff31-3a8a-7562-bbd3-78b2ec65f16c%40enterprisedb.com

-- 
regards, Andrei Lepikhov