Re: multivariate statistics / patch v6

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: multivariate statistics / patch v6
Date: 2015-05-06 20:03:50
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

Attached is v6 of the multivariate stats, with a number of improvements:

1) fix of the contrib compile-time errors (reported by Jeff)

2) fix of pg_proc issues (reported by Jeff)

3) rebase to current master

4) fix a bunch of issues in the previous patches, due to referencing
some parts too early (e.g. histograms in the first patch, etc.)

5) remove the explicit DELETEs from pg_mv_statistic (in the regression
tests), this is now handled automatically by DROP TABLE etc.

6) number of performance optimizations in selectivity estimations:

(a) minimize calls to get_oprrest, significantly reducing
syscache calls

(b) significant reduction of palloc overhead in deserialization of
MCV lists and histograms

(c) use more compact serialized representation of MCV lists and
histograms, often removing ~50% of the size

(d) use histograms with limited deserialization, which also allows
caching function calls

(e) modified histogram bucket partitioning, resulting in more even
bucket distribution (i.e. producing buckets with more equal
density and about equal size of each dimension)

7) add functions for listing MCV list items and histogram buckets:

- pg_mv_mcvlist_items(oid)
- pg_mv_histogram_buckets(oid, type)

This is quite useful when analyzing the MCV lists / histograms.

8) improved support for OR clauses

9) allow calling pull_varnos() on expression trees containing
RestrictInfo nodes (not sure if this is the right fix, it's being
discussed in another thread)

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

Attachment Content-Type Size
0001-shared-infrastructure-and-functional-dependencies.patch text/x-patch 87.2 KB
0002-clause-reduction-using-functional-dependencies.patch text/x-patch 44.2 KB
0003-multivariate-MCV-lists.patch text/x-patch 121.1 KB
0004-multivariate-histograms.patch text/x-patch 146.5 KB
0005-multi-statistics-estimation.patch text/x-patch 91.6 KB
0006-teach-expression-walker-about-RestrictInfo-because-o.patch text/x-patch 891 bytes

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-05-06 20:05:16 Re: INSERT ... ON CONFLICT syntax issues
Previous Message Andrew Dunstan 2015-05-06 19:57:03 Re: Disabling trust/ident authentication configure option