From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Subject: Re: Most-common value docs in PG 12
Date: Wed, 28 Aug 2019 20:25:41 +0200
Message-ID: <20190828182541.zuexunho3hgb7vlw@development> (raw)
In-Reply-To: <20190828162238.GA8360@momjian.us>
References: <20190828162238.GA8360@momjian.us>
On Wed, Aug 28, 2019 at 12:22:38PM -0400, Bruce Momjian wrote:
>Our docs for most-common values in PG 12 has:
>
>--> CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
>
> ANALYZE zipcodes;
>
> SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
> pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
>
> index | values | nulls | frequency | base_frequency
> -------+------------------------+-------+-----------+----------------
>--> 0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05
> 1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05
> 2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133
> 3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113
>
>It seems pg_mcv_list_items() reports the column names in the order they
>appear in the table, not in the order they appear in the CREATE
>STATISTICS statement. Same for psql \d:
>
> \d zipcodes
> Table "public.zipcodes"
> Column | Type | Collation | Nullable | Default
> ---------+------+-----------+----------+---------
> city | text | | |
> state | text | | |
> zipcode | text | | |
> Statistics objects:
>--> "public"."stts3" (mcv) ON city, state FROM zipcodes
>
>
>If this is so, why don't we show the CREATE STATISTICS example as
>city/state, and not state/city?
>
Yes, we deduplicate the attributes and store them sorted by attnum. I'm
not sure it makes sense to change the example to match this order, which
is mostly an implementation detail, though. It might be better to point
out the order may not exactly match CREATE STATISTICS, and point users to
what e.g. "\d" shows (because that will show the order as stored in the
system catalog).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-docs@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, bruce@momjian.us
Subject: Re: Most-common value docs in PG 12
In-Reply-To: <20190828182541.zuexunho3hgb7vlw@development>
* 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