Re: default cardinality with non-existent value

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Igor Kustov <iakustov(at)yandex(dot)ru>
Cc: "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: default cardinality with non-existent value
Date: 2025-04-25 14:03:24
Message-ID: 1954269.1745589804@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Igor Kustov <iakustov(at)yandex(dot)ru> writes:
> I noticed that the optimizer expects one row when there is a
> condition on the field for which optimizer does not know the value
> based on statistics,

Actually, in this example it's probably estimating a selectivity
fraction of exactly zero (plus or minus roundoff error), but later
that gets clamped to the minimum allowed rowcount estimate of one row.
See var_eq_const's handling of the its-not-any-of-the-MCVs case:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/selfuncs.c;h=a96b1b9c0bc69e30865221c5e24e37c594f16d21;hb=HEAD#l413

The clamp-to-one-row bit is done by clamp_row_est:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/optimizer/path/costsize.c;h=60b0fcfb6be542552903f00643de82ac1e91cb80;hb=HEAD#l5348

regards, tom lane

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Álvaro Herrera 2025-05-10 19:13:13 Re: Detached partitioning tables with RF keys in latest minor version is changed
Previous Message Igor Kustov 2025-04-25 09:35:39 default cardinality with non-existent value