Re: Use extended statistics to estimate (Var op Var) clauses

On Fri, Aug 20, 2021 at 3:32 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> Yeah, I agree this seems like the right approach (except I guess you
> meant "a != a" and not "a != 0").

Err, yes.

> Assuming we want to do something about
> these clauses at all - I'm still wondering if those clauses are common
> in practice or just synthetic.

Well, they are certainly less common than some things, but query
generators do a lot of wonky things.

Also, as a practical matter, it might be cheaper to do something about
them than to not do something about them. I don't really understand
the mechanism of operation of the patch, but I guess if somebody
writes "WHERE a = b", one thing you could do would be check whether
any of the MCVs for a are also MCVs for b, and if so you could
estimate something on that basis. If you happened to have extended
statistics for (a, b) then I guess you could do even better using, uh,
math, or something. But all of that sounds like hard work, and
checking whether "a" happens to be the same as "b" sounds super-cheap
by comparison.

If, as normally will be the case, the two sides are not the same, you
haven't really lost anything, because the expenditure of cycles to
test varno and varattno for equality must be utterly trivial in
comparison with fetching stats data and looping over MCV lists and
things. But if on occasion you find out that they are the same, then
you win! You can give a more accurate estimate with less computational
work.

--
Robert Haas
EDB: http://www.enterprisedb.com