From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Dean Rasheed <dean.a.rasheed@gmail.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: Wed, 18 Mar 2020 16:55:43 +0100
Message-ID: <20200318155543.n72l4lj5kkcklbik@development> (raw)
In-Reply-To: <20200317153706.z6pdtijr25iyr424@development>
References: <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>
<20200313160944.aqrbkyf7tkasoxfk@development>
<20200314152159.5webesf6pfluatqj@development>
<20200314184535.4jgkxcyg5yprvsra@development>
<CAEZATCVV2NM0S=hZ1_qH-9B_psrC+5Tqx2xjmL9kHc9EsACSyA@mail.gmail.com>
<20200317153706.z6pdtijr25iyr424@development>
On Tue, Mar 17, 2020 at 04:37:06PM +0100, Tomas Vondra wrote:
>On Tue, Mar 17, 2020 at 12:42:52PM +0000, Dean Rasheed wrote:
>>On Sat, 14 Mar 2020 at 18:45, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>>
>>>I realized there's one more thing that probably needs discussing.
>>>Essentially, these two clause types are the same:
>>>
>>> a IN (1, 2, 3)
>>>
>>> (a = 1 OR a = 2 OR a = 3)
>>>
>>>but with 8f321bd1 we only recognize the first one as compatible with
>>>functional dependencies. It was always the case that we estimated those
>>>two clauses a bit differently, but the differences were usually small.
>>>But now that we recognize IN as compatible with dependencies, the
>>>difference may be much larger, which bugs me a bit ...
>>>
>>>So I wonder if we should recognize the special form of an OR clause,
>>>with all Vars referencing to the same attribute etc. and treat this as
>>>supported by functional dependencies - the attached patch does that.
>>>MCV lists there's already no difference because OR clauses are
>>>supported.
>>>
>>
>>Makes sense, and the patch looks straightforward enough.
>>
>>>The question is whether we want to do this, and whether we should also
>>>teach the per-column estimates to recognize this special case of IN
>>>clause.
>>
>>I'm not convinced about that second part though. I'd say that
>>recognising the OR clause for functional dependencies is sufficient to
>>prevent the large differences in estimates relative to the equivalent
>>IN clauses. The small differences between the way that OR and IN
>>clauses are handled have always been there, and I think that changing
>>that is out of scope for this work.
>>
>
>Not sure. I think the inconsistency between plan and extended stats may
>be a bit surprising, but I agree that issue may be negligible.
>
OK, I've pushed the change recognizing the special case of OR clauses as
supported by functional dependencies. I've left the estimation of the
clause itself as it's, we can address that in the future if needed.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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: tomas.vondra@2ndquadrant.com, dean.a.rasheed@gmail.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: <20200318155543.n72l4lj5kkcklbik@development>
* 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