Horton

Re: Additional improvements to extended statistics

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-03-08T19:17:10Z
Lists: pgsql-hackers 
On Fri, 6 Mar 2020 at 12:58, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
> Here is a rebased version of this patch series. I've polished the first
> two parts a bit - estimation of OR clauses and (Var op Var) clauses.
>

Hi,

I've been looking over the first patch (OR list support). It mostly
looks reasonable to me, except there's a problem with the way
statext_mcv_clauselist_selectivity() combines multiple stat_sel values
into the final result -- in the OR case, it needs to start with sel =
0, and then apply the OR formula to factor in each new estimate. I.e.,
this isn't right for an OR list:

        /* Factor the estimate from this MCV to the oveall estimate. */
        sel *= stat_sel;

(Oh and there's a typo in that comment: s/oveall/overall/).

For example, with the regression test data, this isn't estimated well:

  SELECT * FROM mcv_lists_multi WHERE a = 0 OR b = 0 OR c = 0 OR d = 0;

Similarly, if no extended stats can be applied it needs to return 0
not 1, for example this query on the test data:

  SELECT * FROM mcv_lists WHERE a = 1 OR a = 2 OR d IS NOT NULL;

It might also be worth adding a couple more regression test cases like these.

Regards,
Dean

Commits

  1. Improve estimation of ANDs under ORs using extended statistics.

  2. Improve estimation of OR clauses using multiple extended statistics.

  3. Improve estimation of OR clauses using extended statistics.

  4. Prevent functional dependency estimates from exceeding column estimates.

  5. Fix wording of several extended stats comments

  6. Improve test coverage for multi-column MCV lists

  7. Improve test coverage for functional dependencies