| -- Most of these test steps are modified such that the tables' tuples are |
| -- co-located on one QE. |
| -- Test scan correctness |
| CREATE TABLE brintest_aocs (id int, |
| byteacol bytea, |
| charcol "char", |
| namecol name, |
| int8col bigint, |
| int2col smallint, |
| int4col integer, |
| textcol text, |
| oidcol oid, |
| tidcol tid, |
| float4col real, |
| float8col double precision, |
| macaddrcol macaddr, |
| inetcol inet, |
| cidrcol cidr, |
| bpcharcol character, |
| datecol date, |
| timecol time without time zone, |
| timestampcol timestamp without time zone, |
| timestamptzcol timestamp with time zone, |
| intervalcol interval, |
| timetzcol time with time zone, |
| bitcol bit(10), |
| varbitcol bit varying(16), |
| numericcol numeric, |
| uuidcol uuid, |
| int4rangecol int4range, |
| lsncol pg_lsn, |
| boxcol box |
| ) WITH (appendonly = true, orientation=column); |
| INSERT INTO brintest_aocs SELECT |
| 1, |
| repeat(stringu1, 8)::bytea, |
| substr(stringu1, 1, 1)::"char", |
| stringu1::name, 142857 * tenthous, |
| thousand, |
| twothousand, |
| repeat(stringu1, 8), |
| unique1::oid, |
| format('(%s,%s)', tenthous, twenty)::tid, |
| (four + 1.0)/(hundred+1), |
| odd::float8 / (tenthous + 1), |
| format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, |
| inet '10.2.3.4/24' + tenthous, |
| cidr '10.2.3/24' + tenthous, |
| substr(stringu1, 1, 1)::bpchar, |
| date '1995-08-15' + tenthous, |
| time '01:20:30' + thousand * interval '18.5 second', |
| timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', |
| timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', |
| justify_days(justify_hours(tenthous * interval '12 minutes')), |
| timetz '01:30:20+02' + hundred * interval '15 seconds', |
| thousand::bit(10), |
| tenthous::bit(16)::varbit, |
| tenthous::numeric(36,30) * fivethous * even / (hundred + 1), |
| format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, |
| int4range(thousand, twothousand), |
| format('%s/%s%s', odd, even, tenthous)::pg_lsn, |
| box(point(odd, even), point(thousand, twothousand)) |
| FROM tenk1 ORDER BY unique2 LIMIT 100; |
| -- throw in some NULL's and different values |
| INSERT INTO brintest_aocs (id, inetcol, cidrcol, int4rangecol) SELECT |
| 1, |
| inet 'fe80::6e40:8ff:fea9:8c46' + tenthous, |
| cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous, |
| 'empty'::int4range |
| FROM tenk1 ORDER BY thousand, tenthous LIMIT 25; |
| CREATE INDEX brinaocsidx ON brintest_aocs USING brin ( |
| byteacol, |
| charcol, |
| namecol, |
| int8col, |
| int2col, |
| int4col, |
| textcol, |
| oidcol, |
| tidcol, |
| float4col, |
| float8col, |
| macaddrcol, |
| inetcol inet_inclusion_ops, |
| inetcol inet_minmax_ops, |
| cidrcol inet_inclusion_ops, |
| cidrcol inet_minmax_ops, |
| bpcharcol, |
| datecol, |
| timecol, |
| timestampcol, |
| timestamptzcol, |
| intervalcol, |
| timetzcol, |
| bitcol, |
| varbitcol, |
| numericcol, |
| uuidcol, |
| int4rangecol, |
| lsncol, |
| boxcol |
| ) with (pages_per_range = 1); |
| CREATE TABLE brinopers_aocs (colname name, typ text, |
| op text[], value text[], matches int[], |
| check (cardinality(op) = cardinality(value)), |
| check (cardinality(op) = cardinality(matches))); |
| INSERT INTO brinopers_aocs VALUES |
| ('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', |
| '{>, >=, =, <=, <}', |
| '{ABABAB, ABABAB, 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; |
| r2 record; |
| cond text; |
| count int; |
| mismatch bool; |
| is_orca bool; |
| plan_ok bool; |
| is_planner_plan bool; |
| plan_line text; |
| BEGIN |
| -- determine whether we are using ORCA or planner |
| is_orca := false; |
| FOR r IN EXECUTE 'show optimizer' LOOP |
| IF r.optimizer = 'on' THEN |
| is_orca := true; |
| END IF; |
| END LOOP; |
| |
| FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers_aocs, unnest(op) WITH ORDINALITY AS oper order by colname, typ, oper LOOP |
| mismatch := false; |
| |
| -- prepare the condition |
| IF r.value IS NULL THEN |
| cond := format('%I %s %L', r.colname, r.oper, r.value); |
| ELSE |
| cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ); |
| END IF; |
| |
| -- run the query using the brin index |
| CREATE TEMP TABLE brin_result (cid tid) distributed by (cid); |
| SET enable_seqscan = 0; |
| SET enable_bitmapscan = 1; |
| SET optimizer_enable_tablescan = 0; |
| SET optimizer_enable_bitmapscan = 1; |
| |
| plan_ok := false; |
| is_planner_plan := false; |
| FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest_aocs WHERE %s $y$, cond) LOOP |
| IF plan_line LIKE '%Bitmap Heap Scan on brintest_aocs%' THEN |
| plan_ok := true; |
| END IF; |
| IF plan_line LIKE '%Postgres-based planner%' THEN |
| is_planner_plan := true; |
| END IF; |
| END LOOP; |
| IF NOT plan_ok THEN |
| RAISE WARNING 'did not get bitmap indexscan plan for %', r; |
| END IF; |
| IF is_orca AND is_planner_plan THEN |
| RAISE WARNING 'ORCA did not produce a bitmap indexscan plan for %', r; |
| END IF; |
| |
| EXECUTE format($y$INSERT INTO brin_result SELECT ctid FROM brintest_aocs WHERE %s $y$, cond); |
| |
| -- run the query using a seqscan |
| CREATE TEMP TABLE brin_result_ss (cid tid) distributed by (cid); |
| SET enable_seqscan = 1; |
| SET enable_bitmapscan = 0; |
| SET optimizer_enable_tablescan = 1; |
| SET optimizer_enable_bitmapscan = 0; |
| |
| plan_ok := false; |
| FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest_aocs WHERE %s $y$, cond) LOOP |
| IF plan_line LIKE '%Seq Scan on brintest_aocs%' 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_aocs WHERE %s $y$, cond); |
| |
| -- make sure both return the same results |
| PERFORM * FROM brin_result EXCEPT ALL SELECT * FROM brin_result_ss; |
| GET DIAGNOSTICS count = ROW_COUNT; |
| IF count <> 0 THEN |
| mismatch = true; |
| END IF; |
| PERFORM * FROM brin_result_ss EXCEPT ALL SELECT * FROM brin_result; |
| GET DIAGNOSTICS count = ROW_COUNT; |
| IF count <> 0 THEN |
| mismatch = true; |
| END IF; |
| |
| -- report the results of each scan to make the differences obvious |
| IF mismatch THEN |
| RAISE WARNING 'something not right in %: count %', r, count; |
| SET enable_seqscan = 1; |
| SET enable_bitmapscan = 0; |
| SET optimizer_enable_tablescan = 1; |
| SET optimizer_enable_bitmapscan = 0; |
| FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_aocs WHERE ' || cond LOOP |
| RAISE NOTICE 'seqscan: %', r2; |
| END LOOP; |
| |
| SET enable_seqscan = 0; |
| SET enable_bitmapscan = 1; |
| SET optimizer_enable_tablescan = 0; |
| SET optimizer_enable_bitmapscan = 1; |
| FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_aocs WHERE ' || cond LOOP |
| RAISE NOTICE 'bitmapscan: %', r2; |
| END LOOP; |
| END IF; |
| |
| -- make sure we found expected number of matches |
| SELECT count(*) INTO count FROM brin_result; |
| IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; |
| |
| -- drop the temporary tables |
| DROP TABLE brin_result; |
| DROP TABLE brin_result_ss; |
| END LOOP; |
| END; |
| $x$; |
| -- Note: ORCA does not support all of the above operators: |
| -- - standard comparison operators on inet and cidr columns |
| -- because ORCA does not look at the second occurrence of a column in an index, |
| -- even if it uses a different operator class |
| -- - IS NULL and IS NOT NULL operators, because ORCA supports only binary operators |
| -- - namecol predicates, falls back because of the use of a non-default collation |
| RESET enable_seqscan; |
| RESET enable_bitmapscan; |
| RESET optimizer_enable_tablescan; |
| RESET optimizer_enable_bitmapscan; |
| INSERT INTO brintest_aocs SELECT |
| 1, |
| repeat(stringu1, 42)::bytea, |
| substr(stringu1, 1, 1)::"char", |
| stringu1::name, 142857 * tenthous, |
| thousand, |
| twothousand, |
| repeat(stringu1, 42), |
| unique1::oid, |
| format('(%s,%s)', tenthous, twenty)::tid, |
| (four + 1.0)/(hundred+1), |
| odd::float8 / (tenthous + 1), |
| format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, |
| inet '10.2.3.4' + tenthous, |
| cidr '10.2.3/24' + tenthous, |
| substr(stringu1, 1, 1)::bpchar, |
| date '1995-08-15' + tenthous, |
| time '01:20:30' + thousand * interval '18.5 second', |
| timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', |
| timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', |
| justify_days(justify_hours(tenthous * interval '12 minutes')), |
| timetz '01:30:20' + hundred * interval '15 seconds', |
| thousand::bit(10), |
| tenthous::bit(16)::varbit, |
| tenthous::numeric(36,30) * fivethous * even / (hundred + 1), |
| format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, |
| int4range(thousand, twothousand), |
| format('%s/%s%s', odd, even, tenthous)::pg_lsn, |
| box(point(odd, even), point(thousand, twothousand)) |
| FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5; |
