Thread

1. default cardinality with non-existent value

   Igor Kustov <iakustov@yandex.ru> — 2025-04-25T09:35:39Z

   <div>hello all,</div><div> </div><div>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.</div><div> </div><div><div>postgres=# create table t1 (a int, b int);</div><div>CREATE TABLE</div><div>postgres=# insert into t1 values (generate_series(1,1000),1);</div><div>INSERT 0 1000</div><div>and so on...</div><div>postgres=# insert into t1 values (generate_series(4001,5000),5);</div><div>INSERT 0 1000</div><div>postgres=# analyze t1;</div><div>ANALYZE</div><div>postgres=# explain select * from t1 where b=5;</div><div>                      QUERY PLAN</div><div>------------------------------------------------------</div><div> Seq Scan on t1  (cost=0.00..85.50 rows=1000 width=8)</div><div>   Filter: (b = 5)</div><div>(2 rows)</div><div> </div><div>postgres=# explain select * from t1 where b=6;</div><div>                    QUERY PLAN</div><div>---------------------------------------------------</div><div> Seq Scan on t1  (cost=0.00..85.50 rows=1 width=8)</div><div>   Filter: (b = 6)</div><div>(2 rows)</div><div> </div><div>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?</div><div>can you point to the source code location?</div><div> </div><div>Thanks,</div><div>Igor</div></div>

2. Re: default cardinality with non-existent value

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-25T14:03:24Z

   Igor Kustov <iakustov@yandex.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