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

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: PATCH: add support for IN and @> in functional-dependency statistics use
Date: 2020-03-05 02:34:14
Message-ID: 20200305023414.sux5a4ft75upw2bw@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi Pierre,

I've looked at this patch series, hoping to get it close to committable.
Here is a somewhat improved version of the patch series, split into 5
pieces. The first 4 parts are about applying functional dependencies to
ScalarArrayOpExpr clauses. The last part is about doing the same thing
for MCV lists, so it seemed fine to post it here.

0001 is the patch you posted back in October

0002 simplifies the handling logic a bit, because ScalarArrayOpExpr can
only have form (Var op Const) but not (Const op Var).

0003 fixes what I think is a bug - ScalarArrayOpExpr can represent three
different cases:

* Var op ANY ()
* Var IN () -- special case of ANY
* Var op ALL ()

I don't think functional dependencies can handle the ALL case, we need
to reject it by checking the useOr flag.

0004 adds queries to the stats_ext test suite, to test all of this (some
of the cases illustrate the need for 0003, I think)

0005 allows estimation of ScalarArrayOpExpr by MCV lists, including
regression tests etc.

Will you have time to look at this, particularly 0001-0004, but maybe
even the 0005 part?

As for the second part of your patch (the one allowing estimation of
array containment queries), I still think that's not something we can
reasonably do without also building statistics on elements (which is
what we have in pg_stats but not pg_stats_ext).


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

Attachment Content-Type Size
0001-Apply-functional-dependencies-to-ScalarArrayOpExpr.patch text/plain 1.8 KB
0002-Simplify-parsing-of-ScalarArrayOpExpr.patch text/plain 1.8 KB
0003-Add-regression-tests-for-ScalarArrayOpExpr-with-depe.patch text/plain 15.7 KB
0004-Fix-handling-of-ScalarArrayOpExpr-with-ALL-clause.patch text/plain 1.2 KB
0005-Apply-multi-column-MCV-lists-to-ScalarArrayOpExpr.patch text/plain 13.7 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2020-03-05 03:08:41 Re: Crash by targetted recovery
Previous Message Peter Geoghegan 2020-03-05 01:13:33 kill_prior_tuple and index scan costing