From: Tom Lane <tgl@sss.pgh.pa.us>
To: Philippe BEAUDOIN <phb.emaj@free.fr>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Underestimated number of output rows with an aggregate function
Date: Sun, 15 Oct 2023 12:37:29 -0400
Message-ID: <2091880.1697387849@sss.pgh.pa.us> (raw)
In-Reply-To: <cee9a6fc-f378-457e-8fec-f933f25f22b5@free.fr>
References: <cee9a6fc-f378-457e-8fec-f933f25f22b5@free.fr>
Philippe BEAUDOIN <phb.emaj@free.fr> writes:
> During my analysis, I realized that the output rows estimate of the
> second CTE is really bad, leading to a bad plan for the next CTE.
> I reproduced the issue in a very small test case with a simplified
> query. Attached is a shell script and its output.
Yeah. If you try it you'll see that the estimates for the
"keys.c1 = tbl.c1" and "keys.seq = tbl.seq" clauses are spot-on
individually. The problem is that the planner assumes that they
are independent clauses, so it multiplies those selectivities together.
In reality, because seq is already unique, the condition on c1 adds
no additional selectivity.
If seq is guaranteed unique in your real application, you could just
drop the condition on c1. Otherwise I'm not sure about a good
answer. In principle creating extended stats on c1 and seq should
help, but I think we don't yet apply those for join clauses.
A partial answer could be to defeat application of the table's
statistics by writing
JOIN keys ON (keys.c1 = tbl.c1+0 AND keys.seq = tbl.seq+0)
For me this gives an output estimate of 3000 rows, which is still not
great but should at least prevent choice of an insane plan at the
next join level. However, it pessimizes the plan for this query
itself a little bit (about doubling the runtime).
> For the hash join node, the plan shows a "Inner Unique: true" property.
> I wonder if this is normal.
Sure. The output of the WITH is visibly unique on c1.
regards, tom lane
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: tgl@sss.pgh.pa.us, phb.emaj@free.fr
Subject: Re: Underestimated number of output rows with an aggregate function
In-Reply-To: <2091880.1697387849@sss.pgh.pa.us>
* 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