Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf969678

Browse files
committed
Improve test coverage for functional dependencies
The regression tests for functional dependencies were only using clausesof the form (Var op Const), i.e. with Var on the left side. This addsa couple of queries with Var on the right, to test other code paths.It also prints one of the functional dependencies, to test the data typeoutput function. The functional dependencies are "perfect" with degreeof 1.0 so this should be stable.Discussion:https://www.postgresql.org/message-id/flat/20200113230008.g67iyk4cs3xbnjju@developmentAuthor: Tomas Vondra
1 parent87c9c25 commitf969678

File tree

2 files changed

+38
-0
lines changed

2 files changed

+38
-0
lines changed

‎src/test/regress/expected/stats_ext.out

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -536,6 +536,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
536536
-- create statistics
537537
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
538538
ANALYZE functional_dependencies;
539+
-- print the detected dependencies
540+
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
541+
dependencies
542+
------------------------------------------------------------------------------------------------------------
543+
{"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 => 6": 1.000000, "3, 6 => 4": 1.000000}
544+
(1 row)
545+
539546
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
540547
estimated | actual
541548
-----------+--------
@@ -697,6 +704,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi
697704
102 | 714
698705
(1 row)
699706

707+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
708+
estimated | actual
709+
-----------+--------
710+
102 | 714
711+
(1 row)
712+
700713
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
701714
estimated | actual
702715
-----------+--------
@@ -709,6 +722,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi
709722
1 | 64
710723
(1 row)
711724

725+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
726+
estimated | actual
727+
-----------+--------
728+
1 | 64
729+
(1 row)
730+
712731
-- create separate functional dependencies
713732
CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM functional_dependencies_multi;
714733
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
719738
714 | 714
720739
(1 row)
721740

741+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
742+
estimated | actual
743+
-----------+--------
744+
714 | 714
745+
(1 row)
746+
722747
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
723748
estimated | actual
724749
-----------+--------
@@ -731,6 +756,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi
731756
65 | 64
732757
(1 row)
733758

759+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
760+
estimated | actual
761+
-----------+--------
762+
65 | 64
763+
(1 row)
764+
734765
DROP TABLE functional_dependencies_multi;
735766
-- MCV lists
736767
CREATE TABLE mcv_lists (

‎src/test/regress/sql/stats_ext.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -318,6 +318,9 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen
318318

319319
ANALYZE functional_dependencies;
320320

321+
-- print the detected dependencies
322+
SELECT dependenciesFROM pg_stats_extWHERE statistics_name='func_deps_stat';
323+
321324
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b =''1''');
322325

323326
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;
391394

392395
-- estimates without any functional dependencies
393396
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
397+
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
394398
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
395399
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
400+
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
396401

397402
-- create separate functional dependencies
398403
CREATE STATISTICS functional_dependencies_multi_1 (dependencies)ON a, bFROM functional_dependencies_multi;
@@ -401,8 +406,10 @@ CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM fu
401406
ANALYZE functional_dependencies_multi;
402407

403408
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
409+
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
404410
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
405411
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
412+
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
406413

407414
DROPTABLE functional_dependencies_multi;
408415

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp