Horton

Re: PATCH: multivariate histograms and MCV lists

Adrien Nayrat <adrien.nayrat@dalibo.com>

View thread View on postgr.es
From: Adrien Nayrat <adrien.nayrat@dalibo.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2017-08-17T10:06:32Z
Lists: pgsql-hackers

Attachments

On 08/14/2017 12:48 AM, Tomas Vondra wrote:
> Hi all,
> 
> For PostgreSQL 10 we managed to get the basic CREATE STATISTICS bits in
> (grammar, infrastructure, and two simple types of statistics). See:
> 
>     https://commitfest.postgresql.org/13/852/
> 
> This patch presents a rebased version of the remaining parts, adding more
> complex statistic types (MCV lists and histograms), and hopefully some
> additional improvements.
> 
> The code was rebased on top of current master, and I've made various
> improvements to match how the committed parts were reworked. So the basic idea
> and shape remains the same, the tweaks are mostly small.
> 
> 
> regards
> 
> 
> 
> 

Hello,

There is no check of "statistics type/kind" in pg_stats_ext_mcvlist_items and
pg_histogram_buckets.

select stxname,stxkind from pg_statistic_ext ;
  stxname  | stxkind
-----------+---------
 stts3     | {h}
 stts2     | {m}

So you can call :

SELECT * FROM pg_mcv_list_items((SELECT oid FROM pg_statistic_ext WHERE stxname
= 'stts3'));

SELECT * FROM pg_histogram_buckets((SELECT oid FROM pg_statistic_ext WHERE
stxname = 'stts2'), 0);

Both crashes.

Unfotunately, I don't have the knowledge to produce a patch :/

Small fix in documentation, patch attached.


Thanks!

-- 
Adrien NAYRAT

http://dalibo.com - http://dalibo.org

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