Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kbsd9-0005eO-D5 for pgsql-hackers@arkaria.postgresql.org; Sun, 08 Nov 2020 21:53:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kbsd6-0001ON-8D for pgsql-hackers@arkaria.postgresql.org; Sun, 08 Nov 2020 21:53:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kbsd5-0001OF-GY for pgsql-hackers@lists.postgresql.org; Sun, 08 Nov 2020 21:53:43 +0000 Received: from mail-ej1-x642.google.com ([2a00:1450:4864:20::642]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kbsd1-0005p3-E4 for pgsql-hackers@lists.postgresql.org; Sun, 08 Nov 2020 21:53:41 +0000 Received: by mail-ej1-x642.google.com with SMTP id za3so9530285ejb.5 for ; Sun, 08 Nov 2020 13:53:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=RiC+6APsHNYr58dO2/phrvRmb47jQKokyTPygJSjZVI=; b=YAT3xSlol1KU9BWc8vPa+i1I26NZsZ+IQDZfSEdw1CKu2gEDIolV4e/lVWgnjXFL6o 82FCiFEwRqTx8lOJLJKiRz76i8bNd6dPw6MyxyPhcJPbRUiVkGRP1ogJkcZtuhpa0gw3 ZD/PFQ3bHrnNclMaIcihgSLkp3yF5pWptygQXbZavUt9y0G4VZNIc18ryQsZBHGX+3Ep +l3D8/RzFbiG6uI2Gz8dWc1C9Ftu2Rpds5cAKf16Bea0Q07KXurQLvCEcExBmVqnY+qT xXMIM0vrGVuhquVTchEGj69VGXFegfKh6GqsoZADJ3rQLHAwX3zMl4qUjH4d8tChABI8 +jaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=RiC+6APsHNYr58dO2/phrvRmb47jQKokyTPygJSjZVI=; b=l+8PLoGQrOTIBNUkNlvvKpQR9Izh6o2HfHb86GObWqXsquytQ4Ap0kxmaxVSi3ZIXE W3t+4S+1GOcWpEzgSlD1u8ktUx6PI+WEQp7n2qZATPwNjNxusqG8tZGeGRCFeABeG4LM UzeWwYDZPI0uMYUx07wJLUQQEDNbtBLabjD3rbXfjL14YWZuyhTX+3VsRk4fiwqyDM3G di139U2a+FVyvmb5GtJW2mknMITTLHdy5mDJM3VVOwUPAuxgvu3TjPzExw+CN4OUFlFx TGtIsEhEkmK39Vn0egHbAfPRA3tAerpDujuZBhzsTIonM/hRDuFGgKybOvom/4fo98nC WTJA== X-Gm-Message-State: AOAM533t5lIz6VqR2fuiDQrhSuz6rowVCLn7O+llYB3auPCInlH8GKZS 0/XBBwrPyhrDP4V5QIQ39FKGZRKlZD3XX2iS2vKq3Wi4ssmGwjvPXn4HPdHBGA64ee0hJohRfqN 0bUPsXbB7zWQ9iYhqjPy67v3Curxrcczkd7i3e8gJVT9C+/4cVtThjUpMyI3SaHnKeAoOUsuFgS O+obOe5NJ2L/wmRqBiyv469bTJ4hhhyKPrnrVgd0l7qXL4AmxixcWh3rfQwxpwu0OYQpAXPCa8I I6BITKT4Um1Xsm7r0Z5npucm64bSEZhwPnkuUowvCZw7Lq9cYHMFnWSOWY= X-Google-Smtp-Source: ABdhPJw9s7RWT3Y/wo3gh0Tf2W+01M8kd6tybXf3OzDmrysFjVjfqq+lv95DkDSca5vfmM2as80mdA== X-Received: by 2002:a17:906:1804:: with SMTP id v4mr12413297eje.201.1604872417301; Sun, 08 Nov 2020 13:53:37 -0800 (PST) Received: from [10.137.0.18] (ip-86-49-245-76.net.upcbroadband.cz. [86.49.245.76]) by smtp.gmail.com with ESMTPSA id u13sm7016499edx.38.2020.11.08.13.53.35 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 08 Nov 2020 13:53:36 -0800 (PST) Subject: Re: list of extended statistics on psql To: Tatsuro Yamada , Tomas Vondra Cc: Michael Paquier , Pavel Stehule , Alvaro Herrera , Julien Rouhaud , PostgreSQL Hackers References: <20200831153053.a343pjgumahiejhw@development> <20200831161809.GA31337@alvherre.pgsql> <20200831163200.jp6eqbaaupb6hxac@development> <20200917055531.GV2873@paquier.xyz> <20200930061947.GD1996@paquier.xyz> <2a3173fb-8571-3779-2916-d16a170750f0@nttcom.co.jp_1> <20201028190601.bbshe6dhp32767q7@development> <027134cc-4c99-2878-4385-c0786283c5ea@nttcom.co.jp_1> <831cda41-2299-51f7-12c3-9bf9d3328a58@nttcom.co.jp_1> From: Tomas Vondra Message-ID: <9ed1643b-5c04-640b-70fa-ad7984972cd0@enterprisedb.com> Date: Sun, 8 Nov 2020 22:53:34 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.4.0 MIME-Version: 1.0 In-Reply-To: <831cda41-2299-51f7-12c3-9bf9d3328a58@nttcom.co.jp_1> Content-Type: multipart/mixed; boundary="------------F870F824F71D962507EFC374" Content-Language: en-US X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------F870F824F71D962507EFC374 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hi, I took a look at this today, and I think the code is ready, but the regression test needs a bit more work: 1) It's probably better to use somewhat more specific names for the objects, especially when created in public schema. It decreases the chance of a collision with other tests (which may be hard to notice because of timing). I suggest we use "stts_" prefix or something like that, per the attached 0002 patch. (0001 is just the v7 patch) 2) The test is failing intermittently because it's executed in parallel with stats_ext test, which is also creating extended statistics. So depending on the timing the \dX may list some of the stats_ext stuff. I'm not sure what to do about this. Either this part needs to be moved to a separate test executed in a different group, or maybe we should simply move it to stats_ext. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --------------F870F824F71D962507EFC374 Content-Type: text/x-patch; charset=UTF-8; name="0001-v7-20201108.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-v7-20201108.patch" From 1a7e99c931c0b3ed5560abd3ff656412f8b353f1 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sun, 8 Nov 2020 21:53:54 +0100 Subject: [PATCH 1/2] v7 --- doc/src/sgml/ref/psql-ref.sgml | 14 ++++ src/bin/psql/command.c | 3 + src/bin/psql/describe.c | 100 +++++++++++++++++++++++++++++ src/bin/psql/describe.h | 3 + src/bin/psql/help.c | 1 + src/bin/psql/tab-complete.c | 4 +- src/test/regress/expected/psql.out | 77 ++++++++++++++++++++++ src/test/regress/sql/psql.sql | 29 +++++++++ 8 files changed, 230 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 221a967bfe..fd860776af 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1918,6 +1918,20 @@ testdb=> + + + \dX [ pattern ] + + + Lists extended statistics. + If pattern + is specified, only those extended statistics whose names match the pattern + are listed. + If + is appended to the command name, each extended statistics + is listed with its size. + + + \dy[+] [ pattern ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index c7a83d5dfc..c6f1653cb7 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -930,6 +930,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) else success = listExtensions(pattern); break; + case 'X': /* Extended Statistics */ + success = listExtendedStats(pattern, show_verbose); + break; case 'y': /* Event Triggers */ success = listEventTriggers(pattern, show_verbose); break; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 07d640021c..1807324542 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -4397,6 +4397,106 @@ listEventTriggers(const char *pattern, bool verbose) return true; } +/* + * \dX + * + * Briefly describes extended statistics. + */ +bool +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\", \n" + "CASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built' \n" + " WHEN 'd' = any(es.stxkind) THEN 'defined' \n" + "END AS \"%s\", \n" + "CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built' \n" + " WHEN 'f' = any(es.stxkind) THEN 'defined' \n" + "END AS \"%s\", \n" + "CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built' \n" + " WHEN 'm' = any(es.stxkind) THEN 'defined' \n" + "END AS \"%s\"", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Definition"), + gettext_noop("N_distinct"), + gettext_noop("Dependencies"), + gettext_noop("Mcv")); + + if (verbose) + { + appendPQExpBuffer(&buf, + ",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN \n" + " pg_catalog.pg_size_pretty(pg_catalog.length(stxdndistinct)::bigint) \n" + " WHEN 'd' = any(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(stxddependencies)::bigint) \n" + " WHEN 'f' = any(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(stxdmcv)::bigint) \n" + " WHEN 'm' = any(stxkind) THEN '0 bytes' \n" + "END AS \"%s\" ", + gettext_noop("N_size"), + gettext_noop("D_size"), + gettext_noop("M_size")); + } + + 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, + "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; +} + /* * \dC * diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index f0e3ec957c..89b13c3f0c 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -102,6 +102,9 @@ extern bool listExtensions(const char *pattern); /* \dx+ */ extern bool listExtensionContents(const char *pattern); +/* \dX */ +extern bool listExtendedStats(const char *pattern, bool verbose); + /* \dy */ extern bool listEventTriggers(const char *pattern, bool verbose); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index af829282e6..ea249bf96d 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -267,6 +267,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\du[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\dv[S+] [PATTERN] list views\n")); fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n")); + fprintf(output, _(" \\dX[+] [PATTERN] list extended statistics\n")); fprintf(output, _(" \\dy [PATTERN] list event triggers\n")); fprintf(output, _(" \\l[+] [PATTERN] list databases\n")); fprintf(output, _(" \\sf[+] FUNCNAME show a function's definition\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5238a960f7..30a8ada7d2 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1500,7 +1500,7 @@ psql_completion(const char *text, int start, int end) "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", - "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", + "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding", "\\endif", "\\errverbose", "\\ev", "\\f", @@ -3851,6 +3851,8 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL); else if (TailMatchesCS("\\dx*")) COMPLETE_WITH_QUERY(Query_for_list_of_extensions); + else if (TailMatchesCS("\\dX*")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics, NULL); else if (TailMatchesCS("\\dm*")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL); else if (TailMatchesCS("\\dE*")) diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index daac0ff49d..701f2dbe59 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -5072,3 +5072,80 @@ List of access methods hash | uuid_ops | uuid | uuid | 2 | uuid_hash_extended (5 rows) +-- check printing info about extended statistics +create table t1 (a int, b int); +create statistics stts_1 (dependencies) on a, b from t1; +create statistics stts_2 (dependencies, ndistinct) on a, b from t1; +create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1; +create table t2 (a int, b int, c int); +create statistics stts_4 on b, c from t2; +create table hoge (col1 int, col2 int, col3 int); +create statistics stts_hoge on col1, col2, col3 from hoge; +create schema foo; +create schema yama; +create statistics foo.stts_foo on col1, col2 from hoge; +create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge; +insert into t1 select i,i from generate_series(1,100) i; +analyze t1; +\dX + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+----------------------------+------------+--------------+--------- + foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined + 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 | defined | defined | defined + public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined + yama | stts_yama | col1, col3 FROM hoge | defined | | defined +(7 rows) + +\dX stts_? + 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 | defined | defined | defined +(4 rows) + +\dX *hoge + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+----------------------------+------------+--------------+--------- + public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined +(1 row) + +\dX+ + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+----------------------------+------------+--------------+---------+----------+----------+------------ + foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | + public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | + public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes + public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + yama | stts_yama | col1, col3 FROM hoge | defined | | defined | 0 bytes | | 0 bytes +(7 rows) + +\dX+ stts_? + 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 bytes | + public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | + public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes + public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes +(4 rows) + +\dX+ *hoge + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+----------------------------+------------+--------------+---------+---------+---------+--------- + public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes +(1 row) + +drop table t1, t2, hoge; +drop schema foo, yama; diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 47b28d2a07..dc9f4b2159 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1225,3 +1225,32 @@ drop role regress_partitioning_role; \dAo * pg_catalog.jsonb_path_ops \dAp+ btree float_ops \dAp * pg_catalog.uuid_ops + +-- check printing info about extended statistics +create table t1 (a int, b int); +create statistics stts_1 (dependencies) on a, b from t1; +create statistics stts_2 (dependencies, ndistinct) on a, b from t1; +create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1; + +create table t2 (a int, b int, c int); +create statistics stts_4 on b, c from t2; + +create table hoge (col1 int, col2 int, col3 int); +create statistics stts_hoge on col1, col2, col3 from hoge; + +create schema foo; +create schema yama; +create statistics foo.stts_foo on col1, col2 from hoge; +create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge; + +insert into t1 select i,i from generate_series(1,100) i; +analyze t1; + +\dX +\dX stts_? +\dX *hoge +\dX+ +\dX+ stts_? +\dX+ *hoge +drop table t1, t2, hoge; +drop schema foo, yama; -- 2.26.2 --------------F870F824F71D962507EFC374 Content-Type: text/x-patch; charset=UTF-8; name="0002-rename-objects-in-regression-test-20201108.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-rename-objects-in-regression-test-20201108.patch" From 9a6f15a7d531afa28f8cd8f67edbe301a8ea7ed5 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sun, 8 Nov 2020 22:37:29 +0100 Subject: [PATCH 2/2] rename objects in regression test --- src/test/regress/expected/psql.out | 120 ++++++++++++++--------------- src/test/regress/sql/psql.sql | 36 ++++----- 2 files changed, 78 insertions(+), 78 deletions(-) diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 701f2dbe59..27c73445d5 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -5073,79 +5073,79 @@ List of access methods (5 rows) -- check printing info about extended statistics -create table t1 (a int, b int); -create statistics stts_1 (dependencies) on a, b from t1; -create statistics stts_2 (dependencies, ndistinct) on a, b from t1; -create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1; -create table t2 (a int, b int, c int); -create statistics stts_4 on b, c from t2; -create table hoge (col1 int, col2 int, col3 int); -create statistics stts_hoge on col1, col2, col3 from hoge; -create schema foo; -create schema yama; -create statistics foo.stts_foo on col1, col2 from hoge; -create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge; -insert into t1 select i,i from generate_series(1,100) i; -analyze t1; +create table stts_t1 (a int, b int); +create statistics stts_1 (dependencies) on a, b from stts_t1; +create statistics stts_2 (dependencies, ndistinct) on a, b from stts_t1; +create statistics stts_3 (dependencies, ndistinct, 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 (ndistinct, 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 | N_distinct | Dependencies | Mcv ---------+-----------+----------------------------+------------+--------------+--------- - foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined - 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 | defined | defined | defined - public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined - yama | stts_yama | col1, col3 FROM hoge | defined | | defined + List of extended statistics + Schema | Name | Definition | N_distinct | 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 | defined | defined | defined + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined + stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined + stts_s2 | stts_yama | col1, col3 FROM stts_t3 | defined | | defined (7 rows) \dX stts_? - 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 | defined | defined | defined + List of extended statistics + Schema | Name | Definition | N_distinct | 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 | defined | defined | defined (4 rows) -\dX *hoge - List of extended statistics - Schema | Name | Definition | N_distinct | Dependencies | Mcv ---------+-----------+----------------------------+------------+--------------+--------- - public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined +\dX *stts_hoge + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+-------------------------------+------------+--------------+--------- + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined (1 row) \dX+ - List of extended statistics - Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size ---------+-----------+----------------------------+------------+--------------+---------+----------+----------+------------ - foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes - public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | - public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | - public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes - public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes - public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes - yama | stts_yama | col1, col3 FROM hoge | defined | | defined | 0 bytes | | 0 bytes + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +---------+-----------+-------------------------------+------------+--------------+---------+----------+----------+------------ + public | stts_1 | a, b FROM stts_t1 | | built | | | 40 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 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + stts_s2 | stts_yama | col1, col3 FROM stts_t3 | defined | | defined | 0 bytes | | 0 bytes (7 rows) \dX+ stts_? - 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 bytes | - public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | - public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes - public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+--------+-------------------+------------+--------------+---------+----------+----------+------------ + public | stts_1 | a, b FROM stts_t1 | | built | | | 40 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 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes (4 rows) -\dX+ *hoge - List of extended statistics - Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size ---------+-----------+----------------------------+------------+--------------+---------+---------+---------+--------- - public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes +\dX+ *stts_hoge + List of extended statistics + Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+-------------------------------+------------+--------------+---------+---------+---------+--------- + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes (1 row) -drop table t1, t2, hoge; -drop schema foo, yama; +drop table stts_t1, stts_t2, stts_t3; +drop schema stts_s1, stts_s2; diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index dc9f4b2159..5ebfe184a4 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1227,30 +1227,30 @@ drop role regress_partitioning_role; \dAp * pg_catalog.uuid_ops -- check printing info about extended statistics -create table t1 (a int, b int); -create statistics stts_1 (dependencies) on a, b from t1; -create statistics stts_2 (dependencies, ndistinct) on a, b from t1; -create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1; +create table stts_t1 (a int, b int); +create statistics stts_1 (dependencies) on a, b from stts_t1; +create statistics stts_2 (dependencies, ndistinct) on a, b from stts_t1; +create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from stts_t1; -create table t2 (a int, b int, c int); -create statistics stts_4 on b, c from t2; +create table stts_t2 (a int, b int, c int); +create statistics stts_4 on b, c from stts_t2; -create table hoge (col1 int, col2 int, col3 int); -create statistics stts_hoge on col1, col2, col3 from hoge; +create table stts_t3 (col1 int, col2 int, col3 int); +create statistics stts_hoge on col1, col2, col3 from stts_t3; -create schema foo; -create schema yama; -create statistics foo.stts_foo on col1, col2 from hoge; -create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge; +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 (ndistinct, mcv) on col1, col3 from stts_t3; -insert into t1 select i,i from generate_series(1,100) i; -analyze t1; +insert into stts_t1 select i,i from generate_series(1,100) i; +analyze stts_t1; \dX \dX stts_? -\dX *hoge +\dX *stts_hoge \dX+ \dX+ stts_? -\dX+ *hoge -drop table t1, t2, hoge; -drop schema foo, yama; +\dX+ *stts_hoge +drop table stts_t1, stts_t2, stts_t3; +drop schema stts_s1, stts_s2; -- 2.26.2 --------------F870F824F71D962507EFC374--