Re: PATCH: add support for IN and @> in functional-dependency statistics use

On Fri, Mar 13, 2020 at 08:42:49AM +0000, Dean Rasheed wrote:
> On Thu, 12 Mar 2020 at 17:30, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> >
> > I'm sorry, but I don't see how we could do this for arbitrary clauses. I
> > think we could do that for clauses that have equality semantics and
> > reference column values as a whole. So I think it's possible to do this
> > for IN clauses (which is what the first part of the patch does), but I
> > don't think we can do it for the containment operator.
> >
> > I.e. we can do that for
> >
> > WHERE a IN (...) AND b IN (...)
> >
>
> Hmm, the difficulty always comes back to the compatibility of the
> clauses though. It's easy to come up with artificial examples for
> which functional dependencies come up with bad estimates, even with
> just = and IN (...) operators. For example, given a perfect
> correlation like
>
> a | b
> -------
> 1 | 1
> 2 | 2
> 3 | 3
> : | :
>
> you only need to write a query like "WHERE a IN (1,3,5,7,9,...) AND b
> IN (2,4,6,8,...)" to get a very bad estimate from functional
> dependencies.

Wow, that is a very good example --- the arrays do not tie elements in
one array to elements in another array; good point. I get it now!

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +