Hi,
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.
I thought all tests related to meta-commands exist in psql.sql, but I
realize it's not true. For example, the test of \dRp does not exist in
psql.sql. Therefore, I moved the regression test of \dX to stats_ext.sql
to avoid the test failed in parallel.
Attached patches is following:
- 0001-v8-Add-dX-command-on-psql.patch
- 0002-Add-regression-test-of-dX-to-stats_ext.sql.patch
However, I feel the test of \dX is not elegant, so I'm going to try
creating another one since it would be better to be aware of the context
of existing extended stats tests.
I tried to create another version of the regression test (0003).
"\dX" was added after ANALYZE command or SELECT... from pg_statistic_ext.
Please find the attached file:
- 0003-Add-regression-test-of-dX-to-stats_ext.sql-another-ver
Both regression tests 0002 and 0003 are okay for me, I think.
Could you choose one?
Regards,
Tatsuro Yamada
From b22ebf34fc09e246f8d4cf408f76a6753f3d6bcb Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 10 Nov 2020 16:47:45 +0900
Subject: [PATCH] Add regression test of \dX to stats_ext.sql (another version)
---
src/test/regress/sql/stats_ext.sql | 27 ++++++++++++++++++++++++++-
1 file changed, 26 insertions(+), 1 deletion(-)
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 9781e590a3..c590dcc90a 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -49,6 +49,7 @@ CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM
ab1;
-- Let's also verify the pg_get_statisticsobjdef output looks sane.
SELECT pg_get_statisticsobjdef(oid) FROM pg_statistic_ext WHERE stxname =
'ab1_a_b_stats';
+\dX
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
@@ -60,9 +61,10 @@ ALTER TABLE ab1 DROP COLUMN a;
\d ab1
-- Ensure statistics are dropped when table is
SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
+\dX
DROP TABLE ab1;
SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
-
+\dX
-- Ensure things work sanely with SET STATISTICS 0
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
@@ -73,13 +75,16 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- setting statistics target 0 skips the statistics, without printing any
message, so check catalog
ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
\d ab1
+\dX+ ab1_a_b_stats
ANALYZE ab1;
SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxname = 'ab1_a_b_stats'
AND d.stxoid = s.oid;
+\dX+ ab1_a_b_stats
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
\d+ ab1
+\dX+ ab1_a_b_stats
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
ANALYZE ab1;
@@ -93,6 +98,7 @@ CREATE TABLE ab1c () INHERITS (ab1);
INSERT INTO ab1 VALUES (1,1);
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
+\dX+ ab1_a_b_stats
DROP TABLE ab1 CASCADE;
-- Verify supported object types for extended statistics
@@ -171,6 +177,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+ s10
-- minor improvement, make sure the ctid does not break the matching
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY
ctid, a, b');
@@ -202,6 +209,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+ s10
-- correct estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a,
b');
@@ -220,6 +228,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+
-- dropping the statistics results in under-estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a,
b');
@@ -261,6 +270,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM
functional_dependencies;
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE a = 1 AND b = ''1''');
@@ -274,6 +284,7 @@ INSERT INTO functional_dependencies (a, b, c, filler1)
SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000)
s(i);
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE a = 1 AND b = ''1''');
@@ -335,6 +346,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM
functional_dependencies;
ANALYZE functional_dependencies;
+\dx+ func_deps_stat
-- print the detected dependencies
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
@@ -437,6 +449,7 @@ CREATE STATISTICS functional_dependencies_multi_1
(dependencies) ON a, b FROM fu
CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM
functional_dependencies_multi;
ANALYZE functional_dependencies_multi;
+\dX+ functional_dependencies_multi_*
SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE 0 = a AND 0 = b');
@@ -472,6 +485,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists
WHERE a = 1 AND b =
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b
= ''1''');
@@ -536,6 +550,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists
WHERE a < ALL (ARRAY
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b
= ''1''');
@@ -591,6 +606,7 @@ SELECT d.stxdmcv IS NOT NULL
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dX+ mcv_lists_stats
-- check change of column type resets the MCV statistics
ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
@@ -598,6 +614,7 @@ ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b
= ''1''');
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b
= ''1''');
@@ -629,6 +646,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists
WHERE a IN (0, 1) AN
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL
AND b IS NULL');
@@ -650,6 +668,7 @@ SELECT m.*
pg_mcv_list_items(d.stxdmcv) m
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dX+ mcv_lists_stats
-- 2 distinct combinations with NULL values, all in the MCV list
TRUNCATE mcv_lists;
@@ -682,6 +701,7 @@ SELECT m.*
pg_mcv_list_items(d.stxdmcv) m
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dx+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR
d = ''x''');
@@ -714,6 +734,7 @@ CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
FROM mcv_lists_uuid;
ANALYZE mcv_lists_uuid;
+\dX+ mcv_lists_uuid_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a =
''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b =
''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
@@ -740,6 +761,7 @@ CREATE STATISTICS mcv_lists_arrays_stats (mcv) ON a, b, c
FROM mcv_lists_arrays;
ANALYZE mcv_lists_arrays;
+\dX+ mcv_lists_arrays_stats
-- mcv with bool
CREATE TABLE mcv_lists_bool (
@@ -768,6 +790,7 @@ CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c
FROM mcv_lists_bool;
ANALYZE mcv_lists_bool;
+\dX+ mcv_lists_bool_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE a AND b
AND c');
@@ -806,6 +829,7 @@ CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM
mcv_lists_multi;
CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi;
ANALYZE mcv_lists_multi;
+\dX+ mcv_lists_multi_*
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0');
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0
AND d = 0');
@@ -832,6 +856,7 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
FROM tststats.priv_test_tbl;
ANALYZE tststats.priv_test_tbl;
+\dX+ tststats.priv_test_stats
-- User with no access
CREATE USER regress_stats_user1;
--
2.16.5
