public inbox for [email protected]
help / color / mirror / Atom feeddefault cardinality with non-existent value
2+ messages / 2 participants
[nested] [flat]
* default cardinality with non-existent value
@ 2025-04-25 09:35 Igor Kustov <[email protected]>
2025-04-25 14:03 ` Re: default cardinality with non-existent value Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Igor Kustov @ 2025-04-25 09:35 UTC (permalink / raw)
To: [email protected] <[email protected]>
<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>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: default cardinality with non-existent value
2025-04-25 09:35 default cardinality with non-existent value Igor Kustov <[email protected]>
@ 2025-04-25 14:03 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-04-25 14:03 UTC (permalink / raw)
To: Igor Kustov <[email protected]>; +Cc: [email protected] <[email protected]>
Igor Kustov <[email protected]> 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=a96b...
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...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-25 14:03 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-25 09:35 default cardinality with non-existent value Igor Kustov <[email protected]>
2025-04-25 14:03 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox