From 7f608536adeaecc0817edfb81b85c91e09e543a9 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Tue, 17 Oct 2023 19:15:40 +0200 Subject: [PATCH 4/8] Add tests for inifite date/timestamp values Make sure that infinite values in date/timestamp columns are treated as if in infinite distance. This means the values should not be merged with any other values, leaving them as outliers. The test queries a value in the "gap" and checks the range was eliminated by the BRIN index. --- src/test/regress/expected/brin_multi.out | 72 ++++++++++++++++++++++++ src/test/regress/sql/brin_multi.sql | 54 ++++++++++++++++++ 2 files changed, 126 insertions(+) diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index d5bd600f8fd..fad0c536b9a 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -881,6 +881,78 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Index Cond: (a = '2023-01-01'::date) (4 rows) +DROP TABLE brin_date_test; +RESET enable_seqscan; +-- test handling of infinite timestamp values +CREATE TABLE brin_timestamp_test(a TIMESTAMP); +INSERT INTO brin_timestamp_test VALUES + ('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'), +('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'), +('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'), +('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'), +('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'), +('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'), +('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'), +('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'), +('2000-02-08'), ('2000-02-09'); +CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); +SET enable_seqscan = off; +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; + QUERY PLAN +------------------------------------------------------------------------------ + Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1) + Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) + -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1) + Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) +(4 rows) + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; + QUERY PLAN +------------------------------------------------------------------------------ + Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1) + Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone) + -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1) + Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone) +(4 rows) + +DROP TABLE brin_timestamp_test; +RESET enable_seqscan; +-- test handling of infinite date values +CREATE TABLE brin_date_test(a DATE); +INSERT INTO brin_date_test VALUES +('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'), +('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'), +('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'), +('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'), +('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'), +('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'), +('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'), +('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'), +('2000-02-08'), ('2000-02-09'); +CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); +SET enable_seqscan = off; +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; + QUERY PLAN +------------------------------------------------------------------------- + Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1) + Recheck Cond: (a = '2023-01-01'::date) + -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1) + Index Cond: (a = '2023-01-01'::date) +(4 rows) + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; + QUERY PLAN +------------------------------------------------------------------------- + Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1) + Recheck Cond: (a = '1900-01-01'::date) + -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1) + Index Cond: (a = '1900-01-01'::date) +(4 rows) + DROP TABLE brin_date_test; RESET enable_seqscan; RESET datestyle; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 63d35eacd2d..25f939325f9 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -648,6 +648,60 @@ SET enable_seqscan = off; EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; +DROP TABLE brin_date_test; +RESET enable_seqscan; + +-- test handling of infinite timestamp values +CREATE TABLE brin_timestamp_test(a TIMESTAMP); + +INSERT INTO brin_timestamp_test VALUES + ('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'), +('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'), +('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'), +('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'), +('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'), +('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'), +('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'), +('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'), +('2000-02-08'), ('2000-02-09'); + +CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); + +SET enable_seqscan = off; + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; + +DROP TABLE brin_timestamp_test; +RESET enable_seqscan; + +-- test handling of infinite date values +CREATE TABLE brin_date_test(a DATE); + +INSERT INTO brin_date_test VALUES +('-infinity'), ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'), +('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'), +('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'), +('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'), +('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'), +('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'), +('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'), +('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'), +('2000-02-08'), ('2000-02-09'); + +CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); + +SET enable_seqscan = off; + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; + +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; + DROP TABLE brin_date_test; RESET enable_seqscan; RESET datestyle; -- 2.41.0