diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/stats_ext.out | 41 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_ext.sql | 22 |
2 files changed, 63 insertions, 0 deletions
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 8288ee1b938..e5b9994c24d 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -161,6 +161,47 @@ CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1; ANALYZE ab1; DROP TABLE ab1 CASCADE; NOTICE: drop cascades to table ab1c +-- Tests for stats with inheritance +CREATE TABLE stxdinh(a int, b int); +CREATE TABLE stxdinh1() INHERITS(stxdinh); +CREATE TABLE stxdinh2() INHERITS(stxdinh); +INSERT INTO stxdinh SELECT mod(a,50), mod(a,100) FROM generate_series(0, 1999) a; +INSERT INTO stxdinh1 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a; +INSERT INTO stxdinh2 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a; +VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2; +-- Ensure non-inherited stats are not applied to inherited query +-- Without stats object, it looks like this +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2'); + estimated | actual +-----------+-------- + 400 | 150 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0'); + estimated | actual +-----------+-------- + 3 | 40 +(1 row) + +CREATE STATISTICS stxdinh ON a, b FROM stxdinh; +VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2; +-- Since the stats object does not include inherited stats, it should not +-- affect the estimates +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2'); + estimated | actual +-----------+-------- + 400 | 150 +(1 row) + +-- Dependencies are applied at individual relations (within append), so +-- this estimate changes a bit because we improve estimates for the parent +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0'); + estimated | actual +-----------+-------- + 22 | 40 +(1 row) + +DROP TABLE stxdinh, stxdinh1, stxdinh2; -- Verify supported object types for extended statistics CREATE schema tststats; CREATE TABLE tststats.t (a int, b int, c text); diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 80751723193..a547ca0d138 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -106,6 +106,28 @@ CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1; ANALYZE ab1; DROP TABLE ab1 CASCADE; +-- Tests for stats with inheritance +CREATE TABLE stxdinh(a int, b int); +CREATE TABLE stxdinh1() INHERITS(stxdinh); +CREATE TABLE stxdinh2() INHERITS(stxdinh); +INSERT INTO stxdinh SELECT mod(a,50), mod(a,100) FROM generate_series(0, 1999) a; +INSERT INTO stxdinh1 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a; +INSERT INTO stxdinh2 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a; +VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2; +-- Ensure non-inherited stats are not applied to inherited query +-- Without stats object, it looks like this +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2'); +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0'); +CREATE STATISTICS stxdinh ON a, b FROM stxdinh; +VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2; +-- Since the stats object does not include inherited stats, it should not +-- affect the estimates +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2'); +-- Dependencies are applied at individual relations (within append), so +-- this estimate changes a bit because we improve estimates for the parent +SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0'); +DROP TABLE stxdinh, stxdinh1, stxdinh2; + -- Verify supported object types for extended statistics CREATE schema tststats; |