Re: list of extended statistics on psql - Mailing list pgsql-hackers
From | Tatsuro Yamada |
---|---|
Subject | Re: list of extended statistics on psql |
Date | |
Msg-id | c0939aba-3b12-b596-dd08-913dda4b40f0@nttcom.co.jp_1 Whole thread Raw |
In response to | Re: list of extended statistics on psql (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
On 2020/08/31 1:59, Tom Lane wrote: > Tomas Vondra <tomas.vondra@2ndquadrant.com> writes: >> On Sun, Aug 30, 2020 at 12:33:29PM -0400, Alvaro Herrera wrote: >>> I wonder how to report that. Knowing that psql \-commands are not meant >>> for anything other than human consumption, maybe we can use a format() >>> string that says "built: %d bytes" when \dX+ is used (for each stat type), >>> and just "built" when \dX is used. What do people think about this? > > Seems a little too cute to me. > >> I'd use the same approach as \d+, i.e. a separate column with the size. >> Maybe that'd mean too many columns, though. > > psql already has \d commands with so many columns that you pretty much > have to use \x mode to make them legible; \df+ for instance. I don't > mind if \dX+ is also in that territory. It'd be good though if plain > \dX can fit in a normal terminal window. Hmm. How about these instead of "built: %d bytes"? I added three columns (N_size, D_size, M_size) to show size. See below: =================== postgres=# \dX List of extended statistics Schema | Name | Definition | N_distinct | Dependencies | Mcv --------+-----------+----------------------------+------------+--------------+----------- public | stts_1 | a, b FROM t1 | | built | public | stts_2 | a, b FROM t1 | built | built | public | stts_3 | a, b FROM t1 | built | built | built public | stts_4 | b, c FROM t2 | not built | not built | not built public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built | not built (5 rows) postgres=# \dX+ List of extended statistics Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size --------+-----------+----------------------------+------------+--------------+-----------+--------+--------+-------- public | stts_1 | a, b FROM t1 | | built | | | 40 | public | stts_2 | a, b FROM t1 | built | built | | 13 | 40 | public | stts_3 | a, b FROM t1 | built | built | built | 13 | 40 | 6126 public | stts_4 | b, c FROM t2 | not built | not built | not built | | | public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built | not built | | | =================== I used this query to get results of "\dX+". =================== SELECT stxnamespace::pg_catalog.regnamespace AS "Schema", stxname AS "Name", format('%s FROM %s', (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)), stxrelid::regclass) AS "Definition", CASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built' WHEN 'd' = any(stxkind) THEN 'not built' END AS "N_distinct", CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built' WHEN 'f' = any(stxkind) THEN 'not built' END AS "Dependencies", CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built' WHEN 'm' = any(stxkind) THEN 'not built' END AS "Mcv", pg_catalog.length(stxdndistinct) AS "N_size", pg_catalog.length(stxddependencies) AS "D_size", pg_catalog.length(stxdmcv) AS "M_size" FROM pg_catalog.pg_statistic_ext es INNER JOIN pg_catalog.pg_class c ON stxrelid = c.oid LEFT JOIN pg_catalog.pg_statistic_ext_data esd ON es.oid = esd.stxoid ORDER BY 1, 2; =================== Attached patch includes: - Replace "Columns" and "Table" column with "Definition" - Show the status (built/not built/null) of extended stats by using pg_statistic_ext_data - Add "\dX+" command to show size of extended stats Please find the attached file! :-D Thanks, Tatsuro Yamada
Attachment
pgsql-hackers by date: