public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Felipe López Montes <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Simple query with Planner underestimating rows.
Date: Wed, 29 Jan 2025 10:05:50 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJPJu-0u221Bker1yn=AJ3xJbW0j29mSM=EvkA_SXecv75hqw@mail.gmail.com>
References: <CACJPJu-0u221Bker1yn=AJ3xJbW0j29mSM=EvkA_SXecv75hqw@mail.gmail.com>
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
view thread (4+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Simple query with Planner underestimating rows.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox