Re: Most-common value docs in PG 12

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