Message-ID: <a383df07-989d-4903-8eb7-c93d4a16b628@gmail.com>
Date: Wed, 29 Jan 2025 10:05:50 +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>,
 pgsql-performance@lists.postgresql.org
References: 
 <CACJPJu-0u221Bker1yn=AJ3xJbW0j29mSM=EvkA_SXecv75hqw@mail.gmail.com>
Content-Language: en-AU
Cc: htamfids@gmail.com
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CACJPJu-0u221Bker1yn=AJ3xJbW0j29mSM=EvkA_SXecv75hqw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a383df07-989d-4903-8eb7-c93d4a16b628%40gmail.com>
Precedence: bulk

On 1/29/25 02:29, Felipe López Montes wrote:
> Hi all,
> 
> I am using PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
> 12.4.0, 64-bit.
> 
> I have been facing this issue past weeks and I don't know what else to 
> try, I have a very simple query where the planner is underestimating the 
> number of rows. So instead of estimating the ~ actual number of rows 
> (5120), it estimates only 1.
Your query represents a typical PostgreSQL issue: Here, I see two 
'almost' unique columns: participant_identifier and 
programme_identifier. The result is that the join cardinality on a 
unique column is predicted to be close to the size of the smaller relation.
But in the second join, you have a join by two columns with low selectivity:

ON t3.participant_identifier = t1.participant_identifier
   AND t3.programme_identifier = t2.programme_identifier;

Postgres doesn't gather dependency statistics on two or more columns and 
just multiplies the low selectivities of these clauses, reducing the 
number of rows to a possible minimum - 1.

What you can do? Right now, maybe only pg_hint_plan may help in such a 
situation. However, if you provide some test cases, we may check the 
forgotten feature [1], which enables extended statistics in join clause 
estimations and may push development efforts in that direction.

[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