summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTomas Vondra2017-07-30 13:25:29 +0000
committerTomas Vondra2017-07-31 01:20:45 +0000
commitcfb055553687c257dd1d1ed123356c892f48a804 (patch)
treec25bf7fe66013d25d34f52e24c1b1c37fe7ba8fd
parentd0fdaba0d704f76ec3a4f098abec6b4551539342 (diff)
Add explicit VACUUM to inet test to actually do IOS
Some of the queries in inet test are meant to exercise Index Only Scans. Postgres-XL was not however picking those plans due to stale stats on the coordinator (reltuples and relpages in pg_class). On plain PostgreSQL the tests work fine, as CREATE INDEX also updates statistics stored in the pg_class catalog. For example this CREATE TABLE t (a INT); INSERT INTO t SELECT i FROM generate_series(1,1000) s(i); SELECT relpages, reltuples FROM pg_class WHERE relname = 't'; CREATE INDEX ON t(a); SELECT relpages, reltuples FROM pg_class WHERE relname = 't'; will show zeroes before the CREATE INDEX command, and accurate values after it completes. On Postgres-XL that is not the case, and we will return zeroes even after the CREATE INDEX command. To actually update the statistics we need to fetch information from the datanodes the way VACUUM does it. Fixed by adding an explicit VACUUM call right after the CREATE INDEX, to fetch the stats from the datanodes and update the coordinator catalogs.
-rw-r--r--src/test/regress/expected/inet.out31
-rw-r--r--src/test/regress/sql/inet.sql5
2 files changed, 22 insertions, 14 deletions
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index 71a0126ebe..d4707f4ff7 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -65,6 +65,7 @@ SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL ORDER BY cidr, inet;
| 10:23::8000/113 | 10:23::ffff
(17 rows)
+VACUUM INET_TBL;
-- now test some support functions
SELECT '' AS ten, i AS inet, host(i), text(i), family(i) FROM INET_TBL ORDER BY i;
ten | inet | host | text | family
@@ -241,6 +242,7 @@ SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL ORDER BY 1, 2;
-- check that btree index works correctly
CREATE INDEX inet_idx1 ON inet_tbl(i);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr ORDER BY i, c;
c | i
@@ -265,6 +267,7 @@ SET enable_seqscan TO on;
DROP INDEX inet_idx1;
-- check that gist index works correctly
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
c | i
@@ -393,16 +396,14 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i, c;
-- test index-only scans
EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Remote Subquery Scan on all (datanode_1,datanode_2)
-> Sort
Sort Key: i
- -> Bitmap Heap Scan on inet_tbl
- Recheck Cond: (i << '192.168.1.0/24'::inet)
- -> Bitmap Index Scan on inet_idx2
- Index Cond: (i << '192.168.1.0/24'::inet)
-(7 rows)
+ -> Index Only Scan using inet_idx2 on inet_tbl
+ Index Cond: (i << '192.168.1.0/24'::inet)
+(5 rows)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
i
@@ -416,6 +417,7 @@ SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
c | i
@@ -544,13 +546,14 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i, c;
-- test index-only scans
EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
- QUERY PLAN
----------------------------------------------------
- Sort
- Sort Key: i
- -> Index Only Scan using inet_idx3 on inet_tbl
- Index Cond: (i << '192.168.1.0/24'::inet)
-(4 rows)
+ QUERY PLAN
+---------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+ -> Sort
+ Sort Key: i
+ -> Index Only Scan using inet_idx3 on inet_tbl
+ Index Cond: (i << '192.168.1.0/24'::inet)
+(5 rows)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
i
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 1f629ec7ab..b4d4f72371 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -31,6 +31,8 @@ INSERT INTO INET_TBL (c, i) VALUES (cidr('192.168.1.2/30'), '192.168.1.226');
INSERT INTO INET_TBL (c, i) VALUES (cidr('ffff:ffff:ffff:ffff::/24'), '::192.168.1.226');
SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL ORDER BY cidr, inet;
+VACUUM INET_TBL;
+
-- now test some support functions
SELECT '' AS ten, i AS inet, host(i), text(i), family(i) FROM INET_TBL ORDER BY i;
@@ -64,6 +66,7 @@ SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL ORDER BY 1, 2;
-- check that btree index works correctly
CREATE INDEX inet_idx1 ON inet_tbl(i);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr ORDER BY i, c;
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr ORDER BY i;
@@ -72,6 +75,7 @@ DROP INDEX inet_idx1;
-- check that gist index works correctly
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
@@ -95,6 +99,7 @@ DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
+VACUUM INET_TBL;
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;