Description:
According to the manual (https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html), information_schema.column_statistcis table has HISTOGRAM column of type JSON. The structure of the document is complex enough, so explicit use of JSON functions is needed to get the details, like in this example from the manual:
ysql> SELECT
TABLE_NAME, COLUMN_NAME,
HISTOGRAM->>'$."data-type"' AS 'data-type',
JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
I'd prefer to stay relational and know nothing about JSONB while requesting details about column-level statistics for the relational tables, so I suggest to create view(s) (and maybe extra stored functions) to be able to get all the detail about the histogram data as columns directly avaiable in SQL without anyt JSON functions.
We may also need a separate view for representing the details about the individual buckets in the histogram data for a given column. They may be even base64-encoded for string columns etc.
How to repeat:
Try to get the number of buckets in the histogram data, or specific value in the column that is present in 25% of rows, or find out when the histogram data were collected, in pure SQL.
Suggested fix:
Provide a set of views and/or stored functions to get information about the histogram data for the columns, so that one does not need to know JSON or code anything at client side to get all the histogram data.
