diff options
| -rw-r--r-- | src/test/regress/expected/stats_ext.out | 31 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_ext.sql | 7 |
2 files changed, 38 insertions, 0 deletions
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 9fa659c71d1..e8439f5a341 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -536,6 +536,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE -- create statistics CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies; ANALYZE functional_dependencies; +-- print the detected dependencies +SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + dependencies +------------------------------------------------------------------------------------------------------------ + {"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 => 6": 1.000000, "3, 6 => 4": 1.000000} +(1 row) + SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'''); estimated | actual -----------+-------- @@ -697,6 +704,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi 102 | 714 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b'); + estimated | actual +-----------+-------- + 102 | 714 +(1 row) + SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0'); estimated | actual -----------+-------- @@ -709,6 +722,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi 1 | 64 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0'); + estimated | actual +-----------+-------- + 1 | 64 +(1 row) + -- create separate functional dependencies CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM functional_dependencies_multi; CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM functional_dependencies_multi; @@ -719,6 +738,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi 714 | 714 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b'); + estimated | actual +-----------+-------- + 714 | 714 +(1 row) + SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0'); estimated | actual -----------+-------- @@ -731,6 +756,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi 65 | 64 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0'); + estimated | actual +-----------+-------- + 65 | 64 +(1 row) + DROP TABLE functional_dependencies_multi; -- MCV lists CREATE TABLE mcv_lists ( diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 0ece39a2797..3e927822124 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -318,6 +318,9 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen ANALYZE functional_dependencies; +-- print the detected dependencies +SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'''); SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1'); @@ -391,8 +394,10 @@ ANALYZE functional_dependencies_multi; -- estimates without any functional dependencies 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'); SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0'); SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0'); -- create separate functional dependencies CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM functional_dependencies_multi; @@ -401,8 +406,10 @@ CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM fu ANALYZE 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'); SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0'); SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); +SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0'); DROP TABLE functional_dependencies_multi; |