From: Bruce Momjian <bruce@momjian.us>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Pierre Ducroquet <p.psql@pinaraf.info>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: PATCH: add support for IN and @> in functional-dependency statistics use
Date: Fri, 13 Mar 2020 10:19:21 -0400
Message-ID: <20200313141921.GC2309@momjian.us> (raw)
In-Reply-To: <CAEZATCVb76OcPrA_zkBLk9J3CWPN8bRNZTCtCehbm+RFj=W-Aw@mail.gmail.com>
References: <13902317.Eha0YfKkKy@pierred-pdoc>
<20200201142446.aq5zf3gawj3ynrc2@development>
<3937607.jCdSCb7NGl@pierred-pdoc>
<20200202184134.swoqkqlqorqolrqv@development>
<CAEZATCXnQtw-ZytE+rp1Uby9KMs0hWim-unMa-nMdmS5MMgtbQ@mail.gmail.com>
<20200312173047.stp55sxoj4sipn7q@development>
<CAEZATCVb76OcPrA_zkBLk9J3CWPN8bRNZTCtCehbm+RFj=W-Aw@mail.gmail.com>
On Fri, Mar 13, 2020 at 08:42:49AM +0000, Dean Rasheed wrote:
> On Thu, 12 Mar 2020 at 17:30, Tomas Vondra <tomas.vondra@2ndquadrant.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@momjian.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 +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bruce@momjian.us, dean.a.rasheed@gmail.com, tomas.vondra@2ndquadrant.com, p.psql@pinaraf.info, pgsql-hackers@lists.postgresql.org
Subject: Re: PATCH: add support for IN and @> in functional-dependency statistics use
In-Reply-To: <20200313141921.GC2309@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox