Re: Row count estimation bug in BETWEEN? - Mailing list pgsql-general

=?koi8-r?B?/cXLyc4g8dLP08zB1w==?= <ladayaroslav@yandex.ru> writes:
> I've noticed strange row count estimations in BETWEEN predicate:

> EXPLAIN
> SELECT *
>   FROM t1
>  WHERE f1 = 42;
> -->> Bitmap Heap Scan on t1  (cost=203.07..28792.94 rows=10662 width=24) <skipped>

> EXPLAIN
> SELECT *
>   FROM t1
>  WHERE f1 BETWEEN 42 AND 42;
> -->> Index Scan using table1_field1_idx on t1  (cost=0.44..8.46 rows=1 width=24) <skipped>

> Why row count estimations for two logically equivalent queries are so
> different?

PG doesn't try to estimate inequalities exactly, because it usually
doesn't make enough of a difference to matter.  Currently we don't
even bother to distinguish say ">" from ">=" for estimation purposes,
though certainly we would need to in order to deal with zero-width ranges
with any great amount of precision.

            regards, tom lane