regression.diffs
application/octet-stream
Filename: regression.diffs
Type: application/octet-stream
Part: 0
Message:
Re: multivariate statistics v11
*** /home/jjanes/pgsql/git/src/test/regress/expected/mv_dependencies.out 2016-03-08 18:08:45.275328461 -0800
--- /home/jjanes/pgsql/git/src/test/regress/results/mv_dependencies.out 2016-03-08 18:17:34.914707058 -0800
***************
*** 21,26 ****
--- 21,28 ----
ERROR: unrecognized STATISTICS option "unknown_option"
-- correct command
CREATE STATISTICS s1 ON functional_dependencies (a, b, c) WITH (dependencies);
+ ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index"
+ DETAIL: Key (staname, stanamespace)=(s1, 2200) already exists.
-- random data (no functional dependencies)
INSERT INTO functional_dependencies
SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i);
***************
*** 29,36 ****
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! t | f |
! (1 row)
TRUNCATE functional_dependencies;
-- a => b, a => c, b => c
--- 31,37 ----
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! (0 rows)
TRUNCATE functional_dependencies;
-- a => b, a => c, b => c
***************
*** 41,48 ****
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3, 2 => 3
! (1 row)
TRUNCATE functional_dependencies;
-- a => b, a => c
--- 42,48 ----
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! (0 rows)
TRUNCATE functional_dependencies;
-- a => b, a => c
***************
*** 53,60 ****
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3
! (1 row)
TRUNCATE functional_dependencies;
-- check explain (expect bitmap index scan, not plain index scan)
--- 53,59 ----
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! (0 rows)
TRUNCATE functional_dependencies;
-- check explain (expect bitmap index scan, not plain index scan)
***************
*** 66,83 ****
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3, 2 => 3
! (1 row)
EXPLAIN (COSTS off)
SELECT * FROM functional_dependencies WHERE a = 10 AND b = 5;
! QUERY PLAN
! ---------------------------------------------
! Bitmap Heap Scan on functional_dependencies
! Recheck Cond: ((a = 10) AND (b = 5))
! -> Bitmap Index Scan on fdeps_idx
! Index Cond: ((a = 10) AND (b = 5))
! (4 rows)
DROP TABLE functional_dependencies;
-- varlena type (text)
--- 65,79 ----
FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
deps_enabled | deps_built | pg_mv_stats_dependencies_show
--------------+------------+-------------------------------
! (0 rows)
EXPLAIN (COSTS off)
SELECT * FROM functional_dependencies WHERE a = 10 AND b = 5;
! QUERY PLAN
! -------------------------------------------------------
! Index Scan using fdeps_idx on functional_dependencies
! Index Cond: ((a = 10) AND (b = 5))
! (2 rows)
DROP TABLE functional_dependencies;
-- varlena type (text)
***************
*** 103,172 ****
INSERT INTO functional_dependencies
SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i);
ANALYZE functional_dependencies;
! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps)
! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
! deps_enabled | deps_built | pg_mv_stats_dependencies_show
! --------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3, 2 => 3
! (1 row)
!
! TRUNCATE functional_dependencies;
! -- a => b, a => c
! INSERT INTO functional_dependencies
! SELECT i/10, i/150, i/200 FROM generate_series(1,10000) s(i);
! ANALYZE functional_dependencies;
! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps)
! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
! deps_enabled | deps_built | pg_mv_stats_dependencies_show
! --------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3
! (1 row)
!
! TRUNCATE functional_dependencies;
! -- check explain (expect bitmap index scan, not plain index scan)
! INSERT INTO functional_dependencies
! SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i);
! CREATE INDEX fdeps_idx ON functional_dependencies (a, b);
! ANALYZE functional_dependencies;
! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps)
! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
! deps_enabled | deps_built | pg_mv_stats_dependencies_show
! --------------+------------+-------------------------------
! t | t | 1 => 2, 1 => 3, 2 => 3
! (1 row)
!
! EXPLAIN (COSTS off)
! SELECT * FROM functional_dependencies WHERE a = '10' AND b = '5';
! QUERY PLAN
! ------------------------------------------------------------
! Bitmap Heap Scan on functional_dependencies
! Recheck Cond: ((a = '10'::text) AND (b = '5'::text))
! -> Bitmap Index Scan on fdeps_idx
! Index Cond: ((a = '10'::text) AND (b = '5'::text))
! (4 rows)
!
! DROP TABLE functional_dependencies;
! -- NULL values (mix of int and text columns)
! CREATE TABLE functional_dependencies (
! a INT,
! b TEXT,
! c INT,
! d TEXT
! );
! CREATE STATISTICS s3 ON functional_dependencies (a, b, c, d) WITH (dependencies);
! INSERT INTO functional_dependencies
! SELECT
! mod(i, 100),
! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END),
! mod(i, 400),
! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END)
! FROM generate_series(1,10000) s(i);
! ANALYZE functional_dependencies;
! SELECT deps_enabled, deps_built, pg_mv_stats_dependencies_show(stadeps)
! FROM pg_mv_statistic WHERE starelid = 'functional_dependencies'::regclass;
! deps_enabled | deps_built | pg_mv_stats_dependencies_show
! --------------+------------+----------------------------------------
! t | t | 2 => 1, 3 => 1, 3 => 2, 4 => 1, 4 => 2
! (1 row)
!
! DROP TABLE functional_dependencies;
--- 99,108 ----
INSERT INTO functional_dependencies
SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i);
ANALYZE functional_dependencies;
! WARNING: terminating connection because of crash of another server process
! DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
! HINT: In a moment you should be able to reconnect to the database and repeat your command.
! server closed the connection unexpectedly
! This probably means the server terminated abnormally
! before or while processing the request.
! connection to server was lost
======================================================================
*** /home/jjanes/pgsql/git/src/test/regress/expected/mv_mcv.out 2016-03-08 18:08:45.299328161 -0800
--- /home/jjanes/pgsql/git/src/test/regress/results/mv_mcv.out 2016-03-08 18:17:34.643710446 -0800
***************
*** 80,207 ****
EXPLAIN (COSTS off)
SELECT * FROM mcv_list WHERE a = 10 AND b = 5;
! QUERY PLAN
! --------------------------------------------
! Bitmap Heap Scan on mcv_list
! Recheck Cond: ((a = 10) AND (b = 5))
! -> Bitmap Index Scan on mcv_idx
! Index Cond: ((a = 10) AND (b = 5))
! (4 rows)
!
! DROP TABLE mcv_list;
! -- varlena type (text)
! CREATE TABLE mcv_list (
! a TEXT,
! b TEXT,
! c TEXT
! );
! CREATE STATISTICS s2 ON mcv_list (a, b, c) WITH (mcv);
! -- random data
! INSERT INTO mcv_list
! SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | f |
! (1 row)
!
! TRUNCATE mcv_list;
! -- a => b, a => c, b => c
! INSERT INTO mcv_list
! SELECT i/10, i/100, i/200 FROM generate_series(1,10000) s(i);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | t | nitems=1000
! (1 row)
!
! TRUNCATE mcv_list;
! -- a => b, a => c
! INSERT INTO mcv_list
! SELECT i/10, i/150, i/200 FROM generate_series(1,10000) s(i);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | t | nitems=1000
! (1 row)
!
! TRUNCATE mcv_list;
! -- check explain (expect bitmap index scan, not plain index scan)
! INSERT INTO mcv_list
! SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i);
! CREATE INDEX mcv_idx ON mcv_list (a, b);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | t | nitems=100
! (1 row)
!
! EXPLAIN (COSTS off)
! SELECT * FROM mcv_list WHERE a = '10' AND b = '5';
! QUERY PLAN
! ------------------------------------------------------------
! Bitmap Heap Scan on mcv_list
! Recheck Cond: ((a = '10'::text) AND (b = '5'::text))
! -> Bitmap Index Scan on mcv_idx
! Index Cond: ((a = '10'::text) AND (b = '5'::text))
! (4 rows)
!
! TRUNCATE mcv_list;
! -- check explain (expect bitmap index scan, not plain index scan) with NULLs
! INSERT INTO mcv_list
! SELECT
! (CASE WHEN i/10000 = 0 THEN NULL ELSE i/10000 END),
! (CASE WHEN i/20000 = 0 THEN NULL ELSE i/20000 END),
! (CASE WHEN i/40000 = 0 THEN NULL ELSE i/40000 END)
! FROM generate_series(1,1000000) s(i);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | t | nitems=100
! (1 row)
!
! EXPLAIN (COSTS off)
! SELECT * FROM mcv_list WHERE a IS NULL AND b IS NULL;
! QUERY PLAN
! ---------------------------------------------------
! Bitmap Heap Scan on mcv_list
! Recheck Cond: ((a IS NULL) AND (b IS NULL))
! -> Bitmap Index Scan on mcv_idx
! Index Cond: ((a IS NULL) AND (b IS NULL))
! (4 rows)
!
! DROP TABLE mcv_list;
! -- NULL values (mix of int and text columns)
! CREATE TABLE mcv_list (
! a INT,
! b TEXT,
! c INT,
! d TEXT
! );
! CREATE STATISTICS s3 ON mcv_list (a, b, c, d) WITH (mcv);
! INSERT INTO mcv_list
! SELECT
! mod(i, 100),
! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END),
! mod(i, 400),
! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END)
! FROM generate_series(1,10000) s(i);
! ANALYZE mcv_list;
! SELECT mcv_enabled, mcv_built, pg_mv_stats_mcvlist_info(stamcv)
! FROM pg_mv_statistic WHERE starelid = 'mcv_list'::regclass;
! mcv_enabled | mcv_built | pg_mv_stats_mcvlist_info
! -------------+-----------+--------------------------
! t | t | nitems=1200
! (1 row)
!
! DROP TABLE mcv_list;
--- 80,86 ----
EXPLAIN (COSTS off)
SELECT * FROM mcv_list WHERE a = 10 AND b = 5;
! server closed the connection unexpectedly
! This probably means the server terminated abnormally
! before or while processing the request.
! connection to server was lost
======================================================================
*** /home/jjanes/pgsql/git/src/test/regress/expected/mv_histogram.out 2016-03-08 18:08:45.373327236 -0800
--- /home/jjanes/pgsql/git/src/test/regress/results/mv_histogram.out 2016-03-08 18:17:34.920706983 -0800
***************
*** 30,35 ****
--- 30,37 ----
ERROR: maximum number of buckets is 16384
-- correct command
CREATE STATISTICS s1 ON mv_histogram (a, b, c) WITH (histogram);
+ ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index"
+ DETAIL: Key (staname, stanamespace)=(s1, 2200) already exists.
-- random data (no functional dependencies)
INSERT INTO mv_histogram
SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i);
***************
*** 38,45 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- a => b, a => c, b => c
--- 40,46 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- a => b, a => c, b => c
***************
*** 50,57 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- a => b, a => c
--- 51,57 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- a => b, a => c
***************
*** 62,69 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- check explain (expect bitmap index scan, not plain index scan)
--- 62,68 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- check explain (expect bitmap index scan, not plain index scan)
***************
*** 75,92 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
EXPLAIN (COSTS off)
SELECT * FROM mv_histogram WHERE a = 10 AND b = 5;
! QUERY PLAN
! --------------------------------------------
! Bitmap Heap Scan on mv_histogram
! Recheck Cond: ((a = 10) AND (b = 5))
! -> Bitmap Index Scan on hist_idx
! Index Cond: ((a = 10) AND (b = 5))
! (4 rows)
DROP TABLE mv_histogram;
-- varlena type (text)
--- 74,88 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
EXPLAIN (COSTS off)
SELECT * FROM mv_histogram WHERE a = 10 AND b = 5;
! QUERY PLAN
! -------------------------------------------
! Index Scan using hist_idx on mv_histogram
! Index Cond: ((a = 10) AND (b = 5))
! (2 rows)
DROP TABLE mv_histogram;
-- varlena type (text)
***************
*** 96,101 ****
--- 92,99 ----
c TEXT
);
CREATE STATISTICS s2 ON mv_histogram (a, b, c) WITH (histogram);
+ ERROR: duplicate key value violates unique constraint "pg_mv_statistic_name_index"
+ DETAIL: Key (staname, stanamespace)=(s2, 2200) already exists.
-- random data (no functional dependencies)
INSERT INTO mv_histogram
SELECT mod(i, 111), mod(i, 123), mod(i, 23) FROM generate_series(1,10000) s(i);
***************
*** 104,111 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- a => b, a => c, b => c
--- 102,108 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- a => b, a => c, b => c
***************
*** 116,123 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- a => b, a => c
--- 113,119 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- a => b, a => c
***************
*** 128,207 ****
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! t | t
! (1 row)
TRUNCATE mv_histogram;
-- check explain (expect bitmap index scan, not plain index scan)
INSERT INTO mv_histogram
SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i);
! CREATE INDEX hist_idx ON mv_histogram (a, b);
! ANALYZE mv_histogram;
! SELECT hist_enabled, hist_built
! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
! hist_enabled | hist_built
! --------------+------------
! t | t
! (1 row)
!
! EXPLAIN (COSTS off)
! SELECT * FROM mv_histogram WHERE a = '10' AND b = '5';
! QUERY PLAN
! ------------------------------------------------------------
! Bitmap Heap Scan on mv_histogram
! Recheck Cond: ((a = '10'::text) AND (b = '5'::text))
! -> Bitmap Index Scan on hist_idx
! Index Cond: ((a = '10'::text) AND (b = '5'::text))
! (4 rows)
!
! TRUNCATE mv_histogram;
! -- check explain (expect bitmap index scan, not plain index scan) with NULLs
! INSERT INTO mv_histogram
! SELECT
! (CASE WHEN i/10000 = 0 THEN NULL ELSE i/10000 END),
! (CASE WHEN i/20000 = 0 THEN NULL ELSE i/20000 END),
! (CASE WHEN i/40000 = 0 THEN NULL ELSE i/40000 END)
! FROM generate_series(1,1000000) s(i);
! ANALYZE mv_histogram;
! SELECT hist_enabled, hist_built
! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
! hist_enabled | hist_built
! --------------+------------
! t | t
! (1 row)
!
! EXPLAIN (COSTS off)
! SELECT * FROM mv_histogram WHERE a IS NULL AND b IS NULL;
! QUERY PLAN
! ---------------------------------------------------
! Bitmap Heap Scan on mv_histogram
! Recheck Cond: ((a IS NULL) AND (b IS NULL))
! -> Bitmap Index Scan on hist_idx
! Index Cond: ((a IS NULL) AND (b IS NULL))
! (4 rows)
!
! DROP TABLE mv_histogram;
! -- NULL values (mix of int and text columns)
! CREATE TABLE mv_histogram (
! a INT,
! b TEXT,
! c INT,
! d TEXT
! );
! CREATE STATISTICS s3 ON mv_histogram (a, b, c, d) WITH (histogram);
! INSERT INTO mv_histogram
! SELECT
! mod(i, 100),
! (CASE WHEN mod(i, 200) = 0 THEN NULL ELSE mod(i,200) END),
! mod(i, 400),
! (CASE WHEN mod(i, 300) = 0 THEN NULL ELSE mod(i,600) END)
! FROM generate_series(1,10000) s(i);
! ANALYZE mv_histogram;
! SELECT hist_enabled, hist_built
! FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
! hist_enabled | hist_built
! --------------+------------
! t | t
! (1 row)
!
! DROP TABLE mv_histogram;
--- 124,139 ----
FROM pg_mv_statistic WHERE starelid = 'mv_histogram'::regclass;
hist_enabled | hist_built
--------------+------------
! (0 rows)
TRUNCATE mv_histogram;
-- check explain (expect bitmap index scan, not plain index scan)
INSERT INTO mv_histogram
SELECT i/10000, i/20000, i/40000 FROM generate_series(1,1000000) s(i);
! WARNING: terminating connection because of crash of another server process
! DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
! HINT: In a moment you should be able to reconnect to the database and repeat your command.
! server closed the connection unexpectedly
! This probably means the server terminated abnormally
! before or while processing the request.
! connection to server was lost
======================================================================