Horton

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

Tomas Vondra <tomas.vondra@2ndquadrant.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>, Bruce Momjian <bruce@momjian.us>, Alvaro Herrera <alvherre@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, Mark Dilger <hornschnorter@gmail.com>, Adrien Nayrat <adrien.nayrat@dalibo.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-01-11T21:18:32Z
Lists: pgsql-hackers 
On 1/10/19 4:20 PM, Dean Rasheed wrote:
> ...
>
> So perhaps what we should do for multivariate stats is simply use the
> relative standard error approach (i.e., reuse the patch in [2] with a
> 20% RSE cutoff). That had a lot of testing at the time, against a wide
> range of data distributions, and proved to be very good, not to
> mention being very simple.
> 
> That approach would encompass both groups more and less common than
> the base frequency, because it relies entirely on the group appearing
> enough times in the sample to infer that any errors on the resulting
> estimates will be reasonably well controlled. It wouldn't actually
> look at the base frequency at all in deciding which items to keep.
> 

I've been looking at this approach today, and I'm a bit puzzled. That
patch essentially uses SRE to compute mincount like this:

    mincount = n*(N-n) / (N-n+0.04*n*(N-1))

and then includes all items more common than this threshold. How could
that handle items significantly less common than the base frequency?

Or did you mean to use the SRE, but in some different way?

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Convert pre-existing stats_ext tests to new style

  2. Add support for multivariate MCV lists

  3. Improve ANALYZE's strategy for finding MCVs.

  4. Clone extended stats in CREATE TABLE (LIKE INCLUDING ALL)

  5. Try again to fix accumulation of parallel worker instrumentation.

  6. Adjust psql \d query to avoid use of @> operator.

  7. Message style fixes

  8. Add security checks to selectivity estimation functions