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
