From 79454c696bd3346a9f00f5e940398fb01a337fad Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 4 Jun 2015 14:39:52 -0400 Subject: [PATCH] Tighten the per-operator testing done in brin regression test. Verify that the number of matches is exactly what it should be, not just that it not be zero. This should help us detect any environment-dependent issues. Also, verify that we're getting the expected type of scan plan (either bitmap or seqscan as appropriate). Right now, this is failing on the cidrcol test cases, as shown in the output file. I'll look into that in a bit, but it seems good to commit this as-is temporarily to verify that it behaves as expected on the buildfarm. --- src/test/regress/expected/brin.out | 295 ++++++++++++++++++++++++----- src/test/regress/sql/brin.sql | 267 +++++++++++++++++++++----- 2 files changed, 458 insertions(+), 104 deletions(-) diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out index b45c77a064..272db274aa 100644 --- a/src/test/regress/expected/brin.out +++ b/src/test/regress/expected/brin.out @@ -92,56 +92,195 @@ CREATE INDEX brinidx ON brintest USING brin ( lsncol, boxcol ) with (pages_per_range = 1); -CREATE TABLE brinopers (colname name, typ text, op text[], value text[], - check (cardinality(op) = cardinality(value))); +CREATE TABLE brinopers (colname name, typ text, + op text[], value text[], matches int[], + check (cardinality(op) = cardinality(value)), + check (cardinality(op) = cardinality(matches))); INSERT INTO brinopers VALUES - ('byteacol', 'bytea', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}'), - ('charcol', '"char"', '{>, >=, =, <=, <}', '{A, A, M, Z, Z}'), - ('namecol', 'name', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}'), - ('int2col', 'int2', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 999}'), - ('int2col', 'int4', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 1999}'), - ('int2col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 1428427143}'), - ('int4col', 'int2', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1999}'), - ('int4col', 'int4', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1999}'), - ('int4col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1428427143}'), - ('int8col', 'int2', '{>, >=}', '{0, 0}'), - ('int8col', 'int4', '{>, >=}', '{0, 0}'), - ('int8col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 1257141600, 1428427143, 1428427143}'), - ('textcol', 'text', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}'), - ('oidcol', 'oid', '{>, >=, =, <=, <}', '{0, 0, 8800, 9999, 9999}'), - ('tidcol', 'tid', '{>, >=, =, <=, <}', '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}'), - ('float4col', 'float4', '{>, >=, =, <=, <}', '{0.0103093, 0.0103093, 1, 1, 1}'), - ('float4col', 'float8', '{>, >=, =, <=, <}', '{0.0103093, 0.0103093, 1, 1, 1}'), - ('float8col', 'float4', '{>, >=, =, <=, <}', '{0, 0, 0, 1.98, 1.98}'), - ('float8col', 'float8', '{>, >=, =, <=, <}', '{0, 0, 0, 1.98, 1.98}'), - ('macaddrcol', 'macaddr', '{>, >=, =, <=, <}', '{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}'), - ('inetcol', 'inet', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}'), - ('inetcol', 'inet', '{&&, >>=, <<=, =}', '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}'), - ('inetcol', 'cidr', '{&&, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}'), - ('inetcol', 'cidr', '{&&, >>=, <<=, =}', '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}'), - ('cidrcol', 'inet', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}'), - ('cidrcol', 'inet', '{&&, >>=, <<=, =}', '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}'), - ('cidrcol', 'cidr', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}'), - ('cidrcol', 'cidr', '{&&, >>=, <<=, =}', '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}'), - ('bpcharcol', 'bpchar', '{>, >=, =, <=, <}', '{A, A, W, Z, Z}'), - ('datecol', 'date', '{>, >=, =, <=, <}', '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}'), - ('timecol', 'time', '{>, >=, =, <=, <}', '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}'), - ('timestampcol', 'timestamp', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestampcol', 'timestamptz', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestampcol', 'timestamptz', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestamptzcol', 'timestamptz', '{>, >=, =, <=, <}', '{1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-19 09:00:00-07, 1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03}'), - ('intervalcol', 'interval', '{>, >=, =, <=, <}', '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}'), - ('timetzcol', 'timetz', '{>, >=, =, <=, <}', '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}'), - ('bitcol', 'bit(10)', '{>, >=, =, <=, <}', '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}'), - ('varbitcol', 'varbit(16)', '{>, >=, =, <=, <}', '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}'), - ('numericcol', 'numeric', '{>, >=, =, <=, <}', '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}'), - ('uuidcol', 'uuid', '{>, >=, =, <=, <}', '{00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 52225222-5222-5222-5222-522252225222, 99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998}'), - ('int4rangecol', 'int4range', '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}', '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}'), - ('int4rangecol', 'int4range', '{@>, <@, =, <=, >, >=}', '{empty, empty, empty, empty, empty, empty}'), - ('int4rangecol', 'int4', '{@>}', '{1500}'), - ('lsncol', 'pg_lsn', '{>, >=, =, <=, <, IS, IS NOT}', '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}'), - ('boxcol', 'point', '{@>}', '{"(500,43)"}'), - ('boxcol', 'box', '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}', '{"((1000,2000),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3,4))","((1000,2000),(3000,4000))","((1,2000),(3,4000))","((1000,2),(3000,4))","((1,2),(3,4))","((1,2),(300,400))","((1,2),(3000,4000))","((222,1222),(44,45))"}'); + ('byteacol', 'bytea', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}', + '{100, 100, 1, 100, 100}'), + ('charcol', '"char"', + '{>, >=, =, <=, <}', + '{A, A, M, Z, Z}', + '{97, 100, 6, 100, 98}'), + ('namecol', 'name', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}', + '{100, 100, 2, 100, 100}'), + ('int2col', 'int2', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 999}', + '{100, 100, 1, 100, 100}'), + ('int2col', 'int4', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int2col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int2', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int4', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('int8col', 'int2', + '{>, >=}', + '{0, 0}', + '{100, 100}'), + ('int8col', 'int4', + '{>, >=}', + '{0, 0}', + '{100, 100}'), + ('int8col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 1257141600, 1428427143, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('textcol', 'text', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}', + '{100, 100, 1, 100, 100}'), + ('oidcol', 'oid', + '{>, >=, =, <=, <}', + '{0, 0, 8800, 9999, 9999}', + '{100, 100, 1, 100, 100}'), + ('tidcol', 'tid', + '{>, >=, =, <=, <}', + '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}', + '{100, 100, 1, 100, 100}'), + ('float4col', 'float4', + '{>, >=, =, <=, <}', + '{0.0103093, 0.0103093, 1, 1, 1}', + '{100, 100, 4, 100, 96}'), + ('float4col', 'float8', + '{>, >=, =, <=, <}', + '{0.0103093, 0.0103093, 1, 1, 1}', + '{100, 100, 4, 100, 96}'), + ('float8col', 'float4', + '{>, >=, =, <=, <}', + '{0, 0, 0, 1.98, 1.98}', + '{99, 100, 1, 100, 100}'), + ('float8col', 'float8', + '{>, >=, =, <=, <}', + '{0, 0, 0, 1.98, 1.98}', + '{99, 100, 1, 100, 100}'), + ('macaddrcol', 'macaddr', + '{>, >=, =, <=, <}', + '{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}', + '{99, 100, 2, 100, 100}'), + ('inetcol', 'inet', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}', + '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('inetcol', 'inet', + '{&&, >>=, <<=, =}', + '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('inetcol', 'cidr', + '{&&, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}', + '{100, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('inetcol', 'cidr', + '{&&, >>=, <<=, =}', + '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('cidrcol', 'inet', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}', + '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('cidrcol', 'inet', + '{&&, >>=, <<=, =}', + '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('cidrcol', 'cidr', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}', + '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('cidrcol', 'cidr', + '{&&, >>=, <<=, =}', + '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('bpcharcol', 'bpchar', + '{>, >=, =, <=, <}', + '{A, A, W, Z, Z}', + '{97, 100, 6, 100, 98}'), + ('datecol', 'date', + '{>, >=, =, <=, <}', + '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}', + '{100, 100, 1, 100, 100}'), + ('timecol', 'time', + '{>, >=, =, <=, <}', + '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}', + '{100, 100, 1, 100, 100}'), + ('timestampcol', 'timestamp', + '{>, >=, =, <=, <}', + '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}', + '{100, 100, 1, 100, 100}'), + ('timestampcol', 'timestamptz', + '{>, >=, =, <=, <}', + '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}', + '{100, 100, 1, 100, 100}'), + ('timestamptzcol', 'timestamptz', + '{>, >=, =, <=, <}', + '{1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-19 09:00:00-07, 1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03}', + '{100, 100, 1, 100, 100}'), + ('intervalcol', 'interval', + '{>, >=, =, <=, <}', + '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}', + '{100, 100, 1, 100, 100}'), + ('timetzcol', 'timetz', + '{>, >=, =, <=, <}', + '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}', + '{99, 100, 2, 100, 100}'), + ('bitcol', 'bit(10)', + '{>, >=, =, <=, <}', + '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}', + '{100, 100, 1, 100, 100}'), + ('varbitcol', 'varbit(16)', + '{>, >=, =, <=, <}', + '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}', + '{100, 100, 1, 100, 100}'), + ('numericcol', 'numeric', + '{>, >=, =, <=, <}', + '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}', + '{100, 100, 1, 100, 100}'), + ('uuidcol', 'uuid', + '{>, >=, =, <=, <}', + '{00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 52225222-5222-5222-5222-522252225222, 99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998}', + '{100, 100, 1, 100, 100}'), + ('int4rangecol', 'int4range', + '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}', + '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}', + '{53, 53, 53, 53, 50, 22, 72, 1, 74, 75, 34, 21}'), + ('int4rangecol', 'int4range', + '{@>, <@, =, <=, >, >=}', + '{empty, empty, empty, empty, empty, empty}', + '{125, 72, 72, 72, 53, 125}'), + ('int4rangecol', 'int4', + '{@>}', + '{1500}', + '{22}'), + ('lsncol', 'pg_lsn', + '{>, >=, =, <=, <, IS, IS NOT}', + '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}', + '{100, 100, 1, 100, 100, 25, 100}'), + ('boxcol', 'point', + '{@>}', + '{"(500,43)"}', + '{11}'), + ('boxcol', 'box', + '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}', + '{"((1000,2000),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3,4))","((1000,2000),(3000,4000))","((1,2000),(3,4000))","((1000,2),(3000,4))","((1,2),(3,4))","((1,2),(300,400))","((1,2),(3000,4000))","((222,1222),(44,45))"}', + '{100, 100, 100, 99, 96, 100, 100, 99, 96, 1, 99, 1}'); DO $x$ DECLARE r record; @@ -149,8 +288,10 @@ DECLARE cond text; count int; mismatch bool; + plan_ok bool; + plan_line text; BEGIN - FOR r IN SELECT colname, oper, typ, value[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP + FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP mismatch := false; -- prepare the condition @@ -164,12 +305,34 @@ BEGIN CREATE TEMP TABLE brin_result (cid tid); SET enable_seqscan = 0; SET enable_bitmapscan = 1; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP + IF plan_line LIKE 'Bitmap Heap Scan on brintest%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get bitmap indexscan plan for %', r; + END IF; + EXECUTE format($y$INSERT INTO brin_result SELECT ctid FROM brintest WHERE %s $y$, cond); -- run the query using a seqscan CREATE TEMP TABLE brin_result_ss (cid tid); SET enable_seqscan = 1; SET enable_bitmapscan = 0; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP + IF plan_line LIKE 'Seq Scan on brintest%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get seqscan plan for %', r; + END IF; + EXECUTE format($y$INSERT INTO brin_result_ss SELECT ctid FROM brintest WHERE %s $y$, cond); -- make sure both return the same results @@ -200,9 +363,9 @@ BEGIN END LOOP; END IF; - -- make sure it was a sensible test case + -- make sure we found expected number of matches SELECT count(*) INTO count FROM brin_result; - IF count = 0 THEN RAISE WARNING 'no results for %', r; END IF; + IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; -- drop the temporary tables DROP TABLE brin_result; @@ -210,6 +373,34 @@ BEGIN END LOOP; END; $x$; +WARNING: did not get bitmap indexscan plan for (cidrcol,&&,inet,10/8,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,=,inet,10.2.14/24,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,<,inet,255.255.255.255,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,<=,inet,255.255.255.255,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,>,inet,0.0.0.0,125) +WARNING: did not get bitmap indexscan plan for (cidrcol,>=,inet,0.0.0.0,125) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>=,inet,10.2.14.231/24,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>,inet,10.2.14.231/25,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<=,inet,10.2.14.231/8,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<,inet,0/0,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,&&,inet,fe80::6e40:8ff:fea9:a673/32,25) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>=,inet,fe80::6e40:8ff:fea9:8c46,1) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<=,inet,fe80::6e40:8ff:fea9:a673/32,25) +WARNING: did not get bitmap indexscan plan for (cidrcol,=,inet,fe80::6e40:8ff:fea9:8c46,1) +WARNING: did not get bitmap indexscan plan for (cidrcol,&&,cidr,10/8,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,=,cidr,10.2.14/24,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,<,cidr,255.255.255.255,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,<=,cidr,255.255.255.255,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,>,cidr,0.0.0.0,125) +WARNING: did not get bitmap indexscan plan for (cidrcol,>=,cidr,0.0.0.0,125) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>=,cidr,10.2.14/24,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>,cidr,10.2.14/25,2) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<=,cidr,10/8,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<,cidr,0/0,100) +WARNING: did not get bitmap indexscan plan for (cidrcol,&&,cidr,fe80::/32,25) +WARNING: did not get bitmap indexscan plan for (cidrcol,>>=,cidr,fe80::6e40:8ff:fea9:8c46,1) +WARNING: did not get bitmap indexscan plan for (cidrcol,<<=,cidr,fe80::/32,25) +WARNING: did not get bitmap indexscan plan for (cidrcol,=,cidr,fe80::6e40:8ff:fea9:8c46,1) INSERT INTO brintest SELECT repeat(stringu1, 42)::bytea, substr(stringu1, 1, 1)::"char", diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql index 9a8baef812..a4b56b4044 100644 --- a/src/test/regress/sql/brin.sql +++ b/src/test/regress/sql/brin.sql @@ -96,57 +96,196 @@ CREATE INDEX brinidx ON brintest USING brin ( boxcol ) with (pages_per_range = 1); -CREATE TABLE brinopers (colname name, typ text, op text[], value text[], - check (cardinality(op) = cardinality(value))); +CREATE TABLE brinopers (colname name, typ text, + op text[], value text[], matches int[], + check (cardinality(op) = cardinality(value)), + check (cardinality(op) = cardinality(matches))); INSERT INTO brinopers VALUES - ('byteacol', 'bytea', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}'), - ('charcol', '"char"', '{>, >=, =, <=, <}', '{A, A, M, Z, Z}'), - ('namecol', 'name', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}'), - ('int2col', 'int2', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 999}'), - ('int2col', 'int4', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 1999}'), - ('int2col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 800, 999, 1428427143}'), - ('int4col', 'int2', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1999}'), - ('int4col', 'int4', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1999}'), - ('int4col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 800, 1999, 1428427143}'), - ('int8col', 'int2', '{>, >=}', '{0, 0}'), - ('int8col', 'int4', '{>, >=}', '{0, 0}'), - ('int8col', 'int8', '{>, >=, =, <=, <}', '{0, 0, 1257141600, 1428427143, 1428427143}'), - ('textcol', 'text', '{>, >=, =, <=, <}', '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}'), - ('oidcol', 'oid', '{>, >=, =, <=, <}', '{0, 0, 8800, 9999, 9999}'), - ('tidcol', 'tid', '{>, >=, =, <=, <}', '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}'), - ('float4col', 'float4', '{>, >=, =, <=, <}', '{0.0103093, 0.0103093, 1, 1, 1}'), - ('float4col', 'float8', '{>, >=, =, <=, <}', '{0.0103093, 0.0103093, 1, 1, 1}'), - ('float8col', 'float4', '{>, >=, =, <=, <}', '{0, 0, 0, 1.98, 1.98}'), - ('float8col', 'float8', '{>, >=, =, <=, <}', '{0, 0, 0, 1.98, 1.98}'), - ('macaddrcol', 'macaddr', '{>, >=, =, <=, <}', '{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}'), - ('inetcol', 'inet', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}'), - ('inetcol', 'inet', '{&&, >>=, <<=, =}', '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}'), - ('inetcol', 'cidr', '{&&, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}'), - ('inetcol', 'cidr', '{&&, >>=, <<=, =}', '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}'), - ('cidrcol', 'inet', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}'), - ('cidrcol', 'inet', '{&&, >>=, <<=, =}', '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}'), - ('cidrcol', 'cidr', '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}'), - ('cidrcol', 'cidr', '{&&, >>=, <<=, =}', '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}'), - ('bpcharcol', 'bpchar', '{>, >=, =, <=, <}', '{A, A, W, Z, Z}'), - ('datecol', 'date', '{>, >=, =, <=, <}', '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}'), - ('timecol', 'time', '{>, >=, =, <=, <}', '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}'), - ('timestampcol', 'timestamp', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestampcol', 'timestamptz', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestampcol', 'timestamptz', '{>, >=, =, <=, <}', '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}'), - ('timestamptzcol', 'timestamptz', '{>, >=, =, <=, <}', '{1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-19 09:00:00-07, 1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03}'), - ('intervalcol', 'interval', '{>, >=, =, <=, <}', '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}'), - ('timetzcol', 'timetz', '{>, >=, =, <=, <}', '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}'), - ('bitcol', 'bit(10)', '{>, >=, =, <=, <}', '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}'), - ('varbitcol', 'varbit(16)', '{>, >=, =, <=, <}', '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}'), - ('numericcol', 'numeric', '{>, >=, =, <=, <}', '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}'), - ('uuidcol', 'uuid', '{>, >=, =, <=, <}', '{00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 52225222-5222-5222-5222-522252225222, 99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998}'), - ('int4rangecol', 'int4range', '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}', '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}'), - ('int4rangecol', 'int4range', '{@>, <@, =, <=, >, >=}', '{empty, empty, empty, empty, empty, empty}'), - ('int4rangecol', 'int4', '{@>}', '{1500}'), - ('lsncol', 'pg_lsn', '{>, >=, =, <=, <, IS, IS NOT}', '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}'), - ('boxcol', 'point', '{@>}', '{"(500,43)"}'), - ('boxcol', 'box', '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}', '{"((1000,2000),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3,4))","((1000,2000),(3000,4000))","((1,2000),(3,4000))","((1000,2),(3000,4))","((1,2),(3,4))","((1,2),(300,400))","((1,2),(3000,4000))","((222,1222),(44,45))"}'); + ('byteacol', 'bytea', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}', + '{100, 100, 1, 100, 100}'), + ('charcol', '"char"', + '{>, >=, =, <=, <}', + '{A, A, M, Z, Z}', + '{97, 100, 6, 100, 98}'), + ('namecol', 'name', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}', + '{100, 100, 2, 100, 100}'), + ('int2col', 'int2', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 999}', + '{100, 100, 1, 100, 100}'), + ('int2col', 'int4', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int2col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 800, 999, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int2', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int4', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1999}', + '{100, 100, 1, 100, 100}'), + ('int4col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 800, 1999, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('int8col', 'int2', + '{>, >=}', + '{0, 0}', + '{100, 100}'), + ('int8col', 'int4', + '{>, >=}', + '{0, 0}', + '{100, 100}'), + ('int8col', 'int8', + '{>, >=, =, <=, <}', + '{0, 0, 1257141600, 1428427143, 1428427143}', + '{100, 100, 1, 100, 100}'), + ('textcol', 'text', + '{>, >=, =, <=, <}', + '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}', + '{100, 100, 1, 100, 100}'), + ('oidcol', 'oid', + '{>, >=, =, <=, <}', + '{0, 0, 8800, 9999, 9999}', + '{100, 100, 1, 100, 100}'), + ('tidcol', 'tid', + '{>, >=, =, <=, <}', + '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}', + '{100, 100, 1, 100, 100}'), + ('float4col', 'float4', + '{>, >=, =, <=, <}', + '{0.0103093, 0.0103093, 1, 1, 1}', + '{100, 100, 4, 100, 96}'), + ('float4col', 'float8', + '{>, >=, =, <=, <}', + '{0.0103093, 0.0103093, 1, 1, 1}', + '{100, 100, 4, 100, 96}'), + ('float8col', 'float4', + '{>, >=, =, <=, <}', + '{0, 0, 0, 1.98, 1.98}', + '{99, 100, 1, 100, 100}'), + ('float8col', 'float8', + '{>, >=, =, <=, <}', + '{0, 0, 0, 1.98, 1.98}', + '{99, 100, 1, 100, 100}'), + ('macaddrcol', 'macaddr', + '{>, >=, =, <=, <}', + '{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}', + '{99, 100, 2, 100, 100}'), + ('inetcol', 'inet', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}', + '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('inetcol', 'inet', + '{&&, >>=, <<=, =}', + '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('inetcol', 'cidr', + '{&&, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}', + '{100, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('inetcol', 'cidr', + '{&&, >>=, <<=, =}', + '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('cidrcol', 'inet', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}', + '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('cidrcol', 'inet', + '{&&, >>=, <<=, =}', + '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('cidrcol', 'cidr', + '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', + '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}', + '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), + ('cidrcol', 'cidr', + '{&&, >>=, <<=, =}', + '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', + '{25, 1, 25, 1}'), + ('bpcharcol', 'bpchar', + '{>, >=, =, <=, <}', + '{A, A, W, Z, Z}', + '{97, 100, 6, 100, 98}'), + ('datecol', 'date', + '{>, >=, =, <=, <}', + '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}', + '{100, 100, 1, 100, 100}'), + ('timecol', 'time', + '{>, >=, =, <=, <}', + '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}', + '{100, 100, 1, 100, 100}'), + ('timestampcol', 'timestamp', + '{>, >=, =, <=, <}', + '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}', + '{100, 100, 1, 100, 100}'), + ('timestampcol', 'timestamptz', + '{>, >=, =, <=, <}', + '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}', + '{100, 100, 1, 100, 100}'), + ('timestamptzcol', 'timestamptz', + '{>, >=, =, <=, <}', + '{1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-19 09:00:00-07, 1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03}', + '{100, 100, 1, 100, 100}'), + ('intervalcol', 'interval', + '{>, >=, =, <=, <}', + '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}', + '{100, 100, 1, 100, 100}'), + ('timetzcol', 'timetz', + '{>, >=, =, <=, <}', + '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}', + '{99, 100, 2, 100, 100}'), + ('bitcol', 'bit(10)', + '{>, >=, =, <=, <}', + '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}', + '{100, 100, 1, 100, 100}'), + ('varbitcol', 'varbit(16)', + '{>, >=, =, <=, <}', + '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}', + '{100, 100, 1, 100, 100}'), + ('numericcol', 'numeric', + '{>, >=, =, <=, <}', + '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}', + '{100, 100, 1, 100, 100}'), + ('uuidcol', 'uuid', + '{>, >=, =, <=, <}', + '{00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 52225222-5222-5222-5222-522252225222, 99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998}', + '{100, 100, 1, 100, 100}'), + ('int4rangecol', 'int4range', + '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}', + '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}', + '{53, 53, 53, 53, 50, 22, 72, 1, 74, 75, 34, 21}'), + ('int4rangecol', 'int4range', + '{@>, <@, =, <=, >, >=}', + '{empty, empty, empty, empty, empty, empty}', + '{125, 72, 72, 72, 53, 125}'), + ('int4rangecol', 'int4', + '{@>}', + '{1500}', + '{22}'), + ('lsncol', 'pg_lsn', + '{>, >=, =, <=, <, IS, IS NOT}', + '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}', + '{100, 100, 1, 100, 100, 25, 100}'), + ('boxcol', 'point', + '{@>}', + '{"(500,43)"}', + '{11}'), + ('boxcol', 'box', + '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}', + '{"((1000,2000),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3,4))","((1000,2000),(3000,4000))","((1,2000),(3,4000))","((1000,2),(3000,4))","((1,2),(3,4))","((1,2),(300,400))","((1,2),(3000,4000))","((222,1222),(44,45))"}', + '{100, 100, 100, 99, 96, 100, 100, 99, 96, 1, 99, 1}'); DO $x$ DECLARE @@ -155,8 +294,10 @@ DECLARE cond text; count int; mismatch bool; + plan_ok bool; + plan_line text; BEGIN - FOR r IN SELECT colname, oper, typ, value[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP + FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP mismatch := false; -- prepare the condition @@ -170,12 +311,34 @@ BEGIN CREATE TEMP TABLE brin_result (cid tid); SET enable_seqscan = 0; SET enable_bitmapscan = 1; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP + IF plan_line LIKE 'Bitmap Heap Scan on brintest%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get bitmap indexscan plan for %', r; + END IF; + EXECUTE format($y$INSERT INTO brin_result SELECT ctid FROM brintest WHERE %s $y$, cond); -- run the query using a seqscan CREATE TEMP TABLE brin_result_ss (cid tid); SET enable_seqscan = 1; SET enable_bitmapscan = 0; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP + IF plan_line LIKE 'Seq Scan on brintest%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get seqscan plan for %', r; + END IF; + EXECUTE format($y$INSERT INTO brin_result_ss SELECT ctid FROM brintest WHERE %s $y$, cond); -- make sure both return the same results @@ -206,9 +369,9 @@ BEGIN END LOOP; END IF; - -- make sure it was a sensible test case + -- make sure we found expected number of matches SELECT count(*) INTO count FROM brin_result; - IF count = 0 THEN RAISE WARNING 'no results for %', r; END IF; + IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; -- drop the temporary tables DROP TABLE brin_result; -- 2.40.0