Re: Introducing floating point cast into filter drastically changes row estimate

Merlin Moncure <mmoncure@gmail.com> writes:
> The following query runs fine: it estimates the returned rows pretty wel:
> postgres=# explain analyze  select * from foo where i > 100 and i < 10000;

> ...but if you introduce a floating point cast, it drastically changes
> the returned rows (why?):

> postgres=# explain analyze  select * from foo where i::float8 > 100
> and i::float8 < 10000;

The planner has stats about "i", but none about "i::float8", so you're
getting a default estimate in the second case.  It does, however,
realize that you're applying a range restriction condition to
"i::float8", and the default selectivity estimate for that is
intentionally pretty small.

            regards, tom lane