From cfb055553687c257dd1d1ed123356c892f48a804 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sun, 30 Jul 2017 15:25:29 +0200 Subject: [PATCH] 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. --- src/test/regress/expected/inet.out | 31 ++++++++++++++++-------------- 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; -- 2.39.5