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

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: PATCH: add support for IN and @> in functional-dependency statistics use
Date: 2020-03-14 18:45:35
Message-ID: 20200314184535.4jgkxcyg5yprvsra@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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

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. That would allow producing exactly the same estimates even with
functional dependencies - recognizing the OR clause as supported gets us
only half-way there, because we still use estimates for each clause (and
those will be slightly different).


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
estimate-OR-similar-to-IN-clauses.patch text/plain 8.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Rodrigo Ramírez Norambuena 2020-03-14 19:16:21 Re: [PATCH] Connection time for \conninfo
Previous Message James Coleman 2020-03-14 18:41:09 Re: [PATCH] Incremental sort (was: PoC: Partial sort)