From: Adrien Nayrat <adrien.nayrat@dalibo.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: PATCH: multivariate histograms and MCV lists
Date: Thu, 17 Aug 2017 12:06:32 +0200
Message-ID: <31ee8686-6d62-7f10-4129-7580ec74a710@dalibo.com> (raw)
In-Reply-To: <dfdac334-9cf2-2597-fb27-f0fb3753f435@2ndquadrant.com>
References: <dfdac334-9cf2-2597-fb27-f0fb3753f435@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.orgAttachments:doc.patch (text/x-patch, 1.9K) downloaddiff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 3a86577b0a..a4ab48cc81 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -6445,7 +6445,9 @@ SCRAM-SHA-256$<replaceable><iteration count></>:<replaceable><salt><
An array containing codes for the enabled statistic types;
valid values are:
<literal>d</literal> for n-distinct statistics,
- <literal>f</literal> for functional dependency statistics+ <literal>f</literal> for functional dependency statistics,+ <literal>m</literal> for mcv statistics,+ <literal>h</literal> for histogram statistics
</entry>
</row>
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgmlindex 8857fc7542..9faa7ee393 100644--- a/doc/src/sgml/planstats.sgml+++ b/doc/src/sgml/planstats.sgml@@ -653,7 +653,7 @@ Statistics objects:
<function>pg_mcv_list_items</> set-returning function.
<programlisting>
-SELECT * FROM pg_mcv_list_items((SELECT oid FROM pg_statistic_ext WHERE staname = 'stts2'));+SELECT * FROM pg_mcv_list_items((SELECT oid FROM pg_statistic_ext WHERE stxname = 'stts2'));
index | values | nulls | frequency
-------+---------+-------+-----------
0 | {0,0} | {f,f} | 0.01
@@ -783,7 +783,7 @@ EXPLAIN ANALYZE SELECT * FROM t WHERE a = 1 AND b = 1;
using a function called <function>pg_histogram_buckets</>.
<programlisting>
-test=# SELECT * FROM pg_histogram_buckets((SELECT oid FROM pg_statistic_ext WHERE staname = 'stts3'), 0);+test=# SELECT * FROM pg_histogram_buckets((SELECT oid FROM pg_statistic_ext WHERE stxname = 'stts3'), 0);
index | minvals | maxvals | nullsonly | mininclusive | maxinclusive | frequency | density | bucket_volume
-------+---------+---------+-----------+--------------+--------------+-----------+----------+---------------
0 | {0,0} | {3,1} | {f,f} | {t,t} | {f,f} | 0.01 | 1.68 | 0.005952
signature.asc (application/pgp-signature, 487B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: adrien.nayrat@dalibo.com, tomas.vondra@2ndquadrant.com
Subject: Re: PATCH: multivariate histograms and MCV lists
In-Reply-To: <31ee8686-6d62-7f10-4129-7580ec74a710@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox