From 02df65387eea6fe617460900da62b5c5491d04f1 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 17 Feb 2023 02:25:52 +0100 Subject: [PATCH 4/9] Add minmax-multi inequality tests Add tests exercising the other scan key strategies, to improve test coverage. --- src/test/regress/expected/brin_multi.out | 363 +++++++++++++++++++++++ src/test/regress/sql/brin_multi.sql | 136 +++++++++ 2 files changed, 499 insertions(+) diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index 861a06ef8ca..33c4566d7a1 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -466,3 +466,366 @@ EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1; Filter: (b = 1) (2 rows) +-- do some inequality tests +CREATE TABLE brin_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_test_multi_1 +SELECT i/5 + mod(911 * i + 483, 25), + i/10 + mod(751 * i + 221, 41) + FROM generate_series(1,1000) s(i); +CREATE INDEX brin_test_multi_1_idx_1 ON brin_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=5); +CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=5); +SET enable_seqscan=off; +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a < 37) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a < 37) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37; + count +------- + 124 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a < 113) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a < 113) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113; + count +------- + 504 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a <= 177) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a <= 177) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177; + count +------- + 829 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a <= 25) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a <= 25) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25; + count +------- + 69 +(1 row) + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a > 120) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a > 120) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120; + count +------- + 456 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a >= 180) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a >= 180) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180; + count +------- + 161 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a > 71) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a > 71) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71; + count +------- + 701 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (a >= 63) + -> Bitmap Index Scan on brin_test_multi_1_idx_1 + Index Cond: (a >= 63) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63; + count +------- + 746 +(1 row) + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b < 73) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b < 73) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73; + count +------- + 529 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b <= 47) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b <= 47) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47; + count +------- + 279 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b < 199) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b < 199) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199; + count +------- + 1000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b <= 150) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b <= 150) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150; + count +------- + 1000 +(1 row) + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b > 93) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b > 93) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93; + count +------- + 261 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b > 37) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b > 37) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37; + count +------- + 821 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b >= 215) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b >= 215) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215; + count +------- + 0 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_1 + Recheck Cond: (b > 201) + -> Bitmap Index Scan on brin_test_multi_1_idx_2 + Index Cond: (b > 201) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201; + count +------- + 0 +(1 row) + +DROP TABLE brin_test_multi_1; +RESET enable_seqscan; +-- do some inequality tests for varlena data types +CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10); +INSERT INTO brin_test_multi_2 +SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); +CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5); +SET enable_seqscan=off; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189'; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_test_multi_2 + Recheck Cond: (a < '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) + -> Bitmap Index Scan on brin_test_multi_2_idx + Index Cond: (a < '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189'; + count +------- + 195 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189'; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on brin_test_multi_2 + Recheck Cond: (a > '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) + -> Bitmap Index Scan on brin_test_multi_2_idx + Index Cond: (a > '33e75ff0-9dd6-01bb-e69f-351039152189'::uuid) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189'; + count +------- + 792 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_2 + Recheck Cond: (a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'::uuid) + -> Bitmap Index Scan on brin_test_multi_2_idx + Index Cond: (a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'::uuid) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'; + count +------- + 961 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on brin_test_multi_2 + Recheck Cond: (a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'::uuid) + -> Bitmap Index Scan on brin_test_multi_2_idx + Index Cond: (a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'::uuid) +(5 rows) + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'; + count +------- + 272 +(1 row) + +DROP TABLE brin_test_multi_2; +RESET enable_seqscan; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 070455257c0..68ba94121e3 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -421,3 +421,139 @@ VACUUM ANALYZE brin_test_multi; EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE a = 1; -- Ensure brin index is not used when values are not correlated EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1; + + +-- do some inequality tests +CREATE TABLE brin_test_multi_1 (a INT, b BIGINT) WITH (fillfactor=10); +INSERT INTO brin_test_multi_1 +SELECT i/5 + mod(911 * i + 483, 25), + i/10 + mod(751 * i + 221, 41) + FROM generate_series(1,1000) s(i); + +CREATE INDEX brin_test_multi_1_idx_1 ON brin_test_multi_1 USING brin (a int4_minmax_multi_ops) WITH (pages_per_range=5); +CREATE INDEX brin_test_multi_1_idx_2 ON brin_test_multi_1 USING brin (b int8_minmax_multi_ops) WITH (pages_per_range=5); + +SET enable_seqscan=off; + +-- int: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 37; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a < 113; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 177; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a <= 25; + +-- int: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 120; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 180; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a > 71; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE a >= 63; + +-- bigint: less than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 73; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 47; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b < 199; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b <= 150; + +-- bigint: greater than +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 93; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 37; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b >= 215; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201; + +SELECT COUNT(*) FROM brin_test_multi_1 WHERE b > 201; + +DROP TABLE brin_test_multi_1; +RESET enable_seqscan; + + +-- do some inequality tests for varlena data types +CREATE TABLE brin_test_multi_2 (a UUID) WITH (fillfactor=10); +INSERT INTO brin_test_multi_2 +SELECT v::uuid FROM (SELECT row_number() OVER (ORDER BY v) c, v FROM (SELECT md5((i/13)::text) AS v FROM generate_series(1,1000) s(i)) foo) bar ORDER BY c + 25 * random(); + +CREATE INDEX brin_test_multi_2_idx ON brin_test_multi_2 USING brin (a uuid_minmax_multi_ops) WITH (pages_per_range=5); + +SET enable_seqscan=off; + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189'; + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a < '33e75ff0-9dd6-01bb-e69f-351039152189'; + + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189'; + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a > '33e75ff0-9dd6-01bb-e69f-351039152189'; + + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'; + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a <= 'f457c545-a9de-d88f-18ec-ee47145a72c0'; + + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'; + +SELECT COUNT(*) FROM brin_test_multi_2 WHERE a >= 'c51ce410-c124-a10e-0db5-e4b97fc2af39'; + +DROP TABLE brin_test_multi_2; +RESET enable_seqscan; -- 2.40.1