diff options
author | Tomas Vondra | 2017-07-30 13:25:29 +0000 |
---|---|---|
committer | Tomas Vondra | 2017-07-31 01:20:45 +0000 |
commit | cfb055553687c257dd1d1ed123356c892f48a804 (patch) | |
tree | c25bf7fe66013d25d34f52e24c1b1c37fe7ba8fd | |
parent | d0fdaba0d704f76ec3a4f098abec6b4551539342 (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.out | 31 | ||||
-rw-r--r-- | src/test/regress/sql/inet.sql | 5 |
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; |