Re: Row count estimation bug in BETWEEN?

Tom Lane-2 wrote
> The assumption is that the histogram represents a
> continuous distribution of values in which no one value occurs often
> enough to be interesting (if it did, it would be in the MCV list...).
> Therefore it does not matter much whether any specific histogram entry
> is exactly "=". And of course, for comparison values that are between
> histogram entries, we have no idea whatsoever whether there are any
> "=" entries in the table;

This assumption is correct for continuous types,
but in my example the type (bigint) is discrete (as some other types like
date,
numerics (with defined scale) and even varchar/text are), so the assumption
is wrong for it.

Tom Lane-2 wrote
> so even if the code did distinguish ">" from
> ">=", it would be unclear what to do with the knowledge.

The vague idea that popped into my head is:

As the code in convert_to_scalar already switches on the value type, a flag
to
distinguish ">=" operators from ">" operators could be added there. It would
use the equality of "a > const::sometype" to "a >=
next_value(const::sometype)",
i.e. that "a > 2::int" equals "a >= 3::int". So, corresponding convert_to...
functions would use "value+1" instead of "value" in my case, next date if
the type is date, "value+0.01" if type is numeric(n, 2), etc.

IMHO, the problem with these estimations is that they are horribly off.
I've searched the archives, and it seems that PostgreSQL's users are bitten
by it sometimes,
like: http://www.postgresql.org/message-id/4583.1358289018@sss.pgh.pa.us.

-----
WBR, Yaroslav Schekin.
--
View this message in context: http://postgresql.nabble.com/Row-count-estimation-bug-in-BETWEEN-tp5853687p5853938.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.