Adjust ndistinct for eqjoinsel
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t46240psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 05:48 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t46240_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t46240_1 && git checkout t46240_1Patchset v1 (message #1) is on t46240_1
Hi,
I run TPC-DS benchmark for Postgres and find the join size estimation has several problems.
For example, Ndistinct is key to join selectivity's estimation, this value does not take restrictions
of the rel, I hit some cases in the function eqjoinsel, nd is much larger than vardata.rel->rows.
Accurate estimation need good math model that considering dependency of join var and vars in restriction.
But at least, indistinct should not be greater than the number of rows.
See the attached patch to adjust nd in eqjoinsel.
Best,
Zhenghua Lyu
Zhenghua Lyu <zlyu@vmware.com> writes:
I run TPC-DS benchmark for Postgres and find the join size estimation has several problems.
For example, Ndistinct is key to join selectivity's estimation, this value does not take restrictions
of the rel, I hit some cases in the function eqjoinsel, nd is much larger than vardata.rel->rows.
Accurate estimation need good math model that considering dependency of join var and vars in restriction.
But at least, indistinct should not be greater than the number of rows.
See the attached patch to adjust nd in eqjoinsel.
We're very unlikely to accept this with no test case and no explanation
of why it's not an overcorrection. get_variable_numdistinct already
clamps its result to rel->tuples, and I think that by using rel->rows
instead you are probably double-counting the selectivity of the rel's
restriction clauses.
See the sad history of commit 7f3eba30c, which did something
pretty close to this and eventually got almost entirely reverted
(97930cf57, 0d3b231ee). I'd be the first to agree that better
estimates here would be great, but it's not as simple as it looks.
regards, tom lane