Subject: default cardinality with non-existent value

From: Igor Kustov <[email-masked]>
Date: Fri, 25 Apr 2025 12:35:39 +0300

hello all,

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, e.g.

postgres=# create table t1 (a int, b int);

CREATE TABLE

postgres=# insert into t1 values (generate_series(1,1000),1);

INSERT 0 1000

and so on...

postgres=# insert into t1 values (generate_series(4001,5000),5);

INSERT 0 1000

postgres=# analyze t1;

ANALYZE

postgres=# explain select * from t1 where b=5;

QUERY PLAN

------------------------------------------------------

Seq Scan on t1  (cost=0.00..85.50 rows=1000 width=8)

Filter: (b = 5)

(2 rows)

postgres=# explain select * from t1 where b=6;

QUERY PLAN

---------------------------------------------------

Seq Scan on t1  (cost=0.00..85.50 rows=1 width=8)

Filter: (b = 6)

(2 rows)

do you know if there is a formula for such a cardinality based on the size of the table or is it always one row?

can you point to the source code location?

Thanks,

Igor

---

From: Tom Lane <[email-masked]>
Date: Fri, 25 Apr 2025 10:03:24 -0400

Igor Kustov <[email-masked]> 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