<varlistentry>

<term><literal>\dX [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>

<listitem>

<para>

Lists extended statistics.

If <replaceable class="parameter">pattern</replaceable>

is specified, only those extended statistics whose names match the

pattern are listed.

If <literal>+</literal> is appended to the command name, each extended

statistics is listed with its size.

</para>

<para>

The column of the kind of extended stats (e.g. Ndistinct) shows some statuses.

"requested" means that it needs to collect statistics by <link

linkend="sql-analyze"><command>ANALYZE</command></link>.

"built" means <link linkend="sql-analyze"><command>ANALYZE</command></link> was

finished, and the planner can use it. NULL means that it doesn't exists.

</para>

</listitem>

</varlistentry>

case 'X': /* Extended Statistics */

success = listExtendedStats(pattern, show_verbose);

break;

listExtendedStats(const char *pattern, bool verbose)

{

PQExpBufferData buf;

PGresult *res;

printQueryOpt myopt = pset.popt;

if (pset.sversion < 100000)

{

char sverbuf[32];

pg_log_error("The server (version %s) does not support extended statistics.",

formatPGVersionNumber(pset.sversion, false,

sverbuf, sizeof(sverbuf)));

return true;

}

initPQExpBuffer(&buf);

printfPQExpBuffer(&buf,

"SELECT \n"

"es.stxnamespace::pg_catalog.regnamespace::text AS \"%s\", \n"

"es.stxname AS \"%s\", \n"

"pg_catalog.format('%%s FROM %%s', \n"

" (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"

" FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"

" JOIN pg_catalog.pg_attribute a \n"

" ON (es.stxrelid = a.attrelid \n"

" AND a.attnum = s.attnum \n"

" AND NOT a.attisdropped)), \n"

"es.stxrelid::regclass) AS \"%s\"",

gettext_noop("Schema"),

gettext_noop("Name"),

gettext_noop("Definition"));

/*

if (pset.sversion < 120000)

{

appendPQExpBuffer(&buf,

",\nCASE WHEN es.stxndistinct IS NOT NULL THEN 'built' \n"

" WHEN 'd' = any(es.stxkind) THEN 'requested' \n"

"END AS \"%s\", \n"

"CASE WHEN es.stxdependencies IS NOT NULL THEN 'built' \n"

" WHEN 'f' = any(es.stxkind) THEN 'requested' \n"

"END AS \"%s\"",

gettext_noop("Ndistinct"),

gettext_noop("Dependencies"));

}

else

{

appendPQExpBuffer(&buf,

",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built' \n"

" WHEN 'd' = any(es.stxkind) THEN 'requested' \n"

"END AS \"%s\", \n"

"CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built' \n"

" WHEN 'f' = any(es.stxkind) THEN 'requested' \n"

"END AS \"%s\", \n"

"CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built' \n"

" WHEN 'm' = any(es.stxkind) THEN 'requested' \n"

"END AS \"%s\"",

gettext_noop("Ndistinct"),

gettext_noop("Dependencies"),

gettext_noop("MCV"));

}

/* In verbose mode, print sizes of the extended statistics objects. */

if (verbose)

{

if (pset.sversion < 120000)

{

appendPQExpBuffer(&buf,

",\nCASE WHEN es.stxndistinct IS NOT NULL THEN \n"

" pg_catalog.pg_size_pretty(pg_catalog.length(es.stxndistinct)::bigint) \n"

" WHEN 'd' = any(es.stxkind) THEN '0 bytes' \n"

"END AS \"%s\", \n"

"CASE WHEN es.stxdependencies IS NOT NULL THEN \n"

" pg_catalog.pg_size_pretty(pg_catalog.length(es.stxdependencies)::bigint) \n"

" WHEN 'f' = any(es.stxkind) THEN '0 bytes' \n"

"END AS \"%s\"",

gettext_noop("Ndistinct_size"),

gettext_noop("Dependencies_size"));

}

else

{

appendPQExpBuffer(&buf,

",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN \n"

" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxdndistinct)::bigint) \n"

" WHEN 'd' = any(es.stxkind) THEN '0 bytes' \n"

"END AS \"%s\", \n"

"CASE WHEN esd.stxddependencies IS NOT NULL THEN \n"

" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxddependencies)::bigint) \n"

" WHEN 'f' = any(es.stxkind) THEN '0 bytes' \n"

"END AS \"%s\", \n"

"CASE WHEN esd.stxdmcv IS NOT NULL THEN \n"

" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxdmcv)::bigint) \n"

" WHEN 'm' = any(es.stxkind) THEN '0 bytes' \n"

"END AS \"%s\"",

gettext_noop("Ndistinct_size"),

gettext_noop("Dependencies_size"),

gettext_noop("MCV_size"));

}

}

if (pset.sversion < 120000)

{

appendPQExpBufferStr(&buf,

" \nFROM pg_catalog.pg_statistic_ext es \n"

"INNER JOIN pg_catalog.pg_class c \n"

"ON es.stxrelid = c.oid \n");

}

else

{

appendPQExpBufferStr(&buf,

" \nFROM pg_catalog.pg_statistic_ext es \n"

"LEFT JOIN pg_catalog.pg_statistic_ext_data esd \n"

"ON es.oid = esd.stxoid \n"

"INNER JOIN pg_catalog.pg_class c \n"

"ON es.stxrelid = c.oid \n");

}

processSQLNamePattern(pset.db, &buf, pattern, false,

false, NULL,

"es.stxname", NULL,

NULL);

appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");

res = PSQLexec(buf.data);

termPQExpBuffer(&buf);

if (!res)

return false;

myopt.nullPrint = NULL;

myopt.title = _("List of extended statistics");

myopt.translate_header = true;

printQuery(res, &myopt, pset.queryFout, false, pset.logfile);

PQclear(res);

return true;

}

extern bool listExtendedStats(const char *pattern, bool verbose);

fprintf(output, _(" \\dX[+] [PATTERN] list extended statistics\n"));

"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",

else if (TailMatchesCS("\\dX*"))

COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics, NULL);

-- Check printing info about extended statistics by \dX

create table stts_t1 (a int, b int);

create statistics stts_1 (ndistinct) on a, b from stts_t1;

create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;

create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;

create table stts_t2 (a int, b int, c int);

create statistics stts_4 on b, c from stts_t2;

create table stts_t3 (col1 int, col2 int, col3 int);

create statistics stts_hoge on col1, col2, col3 from stts_t3;

create schema stts_s1;

create schema stts_s2;

create statistics stts_s1.stts_foo on col1, col2 from stts_t3;

create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;

insert into stts_t1 select i,i from generate_series(1,100) i;

analyze stts_t1;

\dX

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV

----------+------------------------+--------------------------------------+-----------+--------------+-----------

public | func_deps_stat | a, b, c FROM functional_dependencies | | built |

public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | built

public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | built

public | mcv_lists_stats | a, b, d FROM mcv_lists | | | built

public | stts_1 | a, b FROM stts_t1 | built | |

public | stts_2 | a, b FROM stts_t1 | built | built |

public | stts_3 | a, b FROM stts_t1 | built | built | built

public | stts_4 | b, c FROM stts_t2 | requested | requested | requested

public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested

stts_s1 | stts_foo | col1, col2 FROM stts_t3 | requested | requested | requested

stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested

tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | built

(12 rows)

\dX stts_?

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV

--------+--------+-------------------+-----------+--------------+-----------

public | stts_1 | a, b FROM stts_t1 | built | |

public | stts_2 | a, b FROM stts_t1 | built | built |

public | stts_3 | a, b FROM stts_t1 | built | built | built

public | stts_4 | b, c FROM stts_t2 | requested | requested | requested

(4 rows)

\dX *stts_hoge

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV

--------+-----------+-------------------------------+-----------+--------------+-----------

public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested

(1 row)

\dX+

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size

----------+------------------------+--------------------------------------+-----------+--------------+-----------+----------------+-------------------+------------

public | func_deps_stat | a, b, c FROM functional_dependencies | | built | | | 106 bytes |

public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | built | | | 24 kB

public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | built | | | 386 bytes

public | mcv_lists_stats | a, b, d FROM mcv_lists | | | built | | | 294 bytes

public | stts_1 | a, b FROM stts_t1 | built | | | 13 bytes | |

public | stts_2 | a, b FROM stts_t1 | built | built | | 13 bytes | 40 bytes |

public | stts_3 | a, b FROM stts_t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes

public | stts_4 | b, c FROM stts_t2 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes

public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes

stts_s1 | stts_foo | col1, col2 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes

stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested | | 0 bytes | 0 bytes

tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | built | | | 686 bytes

(12 rows)

\dX+ stts_?

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size

--------+--------+-------------------+-----------+--------------+-----------+----------------+-------------------+------------

public | stts_1 | a, b FROM stts_t1 | built | | | 13 bytes | |

public | stts_2 | a, b FROM stts_t1 | built | built | | 13 bytes | 40 bytes |

public | stts_3 | a, b FROM stts_t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes

public | stts_4 | b, c FROM stts_t2 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes

(4 rows)

\dX+ *stts_hoge

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size

--------+-----------+-------------------------------+-----------+--------------+-----------+----------------+-------------------+----------

public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes

(1 row)

\dX+ stts_s2.stts_yama

List of extended statistics

Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size

---------+-----------+-------------------------+-----------+--------------+-----------+----------------+-------------------+----------

stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested | | 0 bytes | 0 bytes

(1 row)

drop table stts_t1, stts_t2, stts_t3;

drop schema stts_s1, stts_s2 cascade;

create table stts_t1 (a int, b int);

create statistics stts_1 (ndistinct) on a, b from stts_t1;

create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;

create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;

create table stts_t2 (a int, b int, c int);

create statistics stts_4 on b, c from stts_t2;

create table stts_t3 (col1 int, col2 int, col3 int);

create statistics stts_hoge on col1, col2, col3 from stts_t3;

create schema stts_s1;

create schema stts_s2;

create statistics stts_s1.stts_foo on col1, col2 from stts_t3;

create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;

insert into stts_t1 select i,i from generate_series(1,100) i;

analyze stts_t1;

\dX

\dX stts_?

\dX *stts_hoge

\dX+

\dX+ stts_?

\dX+ *stts_hoge

\dX+ stts_s2.stts_yama

drop table stts_t1, stts_t2, stts_t3;

drop schema stts_s1, stts_s2 cascade;