8000 Fix minmax-multi on infinite date/timestamp values · postgres/postgres@8da86d6 · GitHub
[go: up one dir, main page]
Skip to content

Commit 8da86d6

Browse files
tvondratvondra
committed
Fix minmax-multi on infinite date/timestamp values
Make sure that infinite values in date/timestamp columns are treated as if in infinite distance. Infinite values should not be merged with other values, leaving them as outliers. The code however returned distance 0 in this case, so that infinite values were merged first. While this does not break the index (i.e. it still produces correct query results), it may make it much less efficient. We don't need explicit handling of infinite date/timestamp values when calculating distances, because those values are represented as extreme but regular values (e.g. INT64_MIN/MAX for the timestamp type). We don't need an exact distance, just a value that is much larger than distanced between regular values. With the added cast to double values, we can simply subtract the values. The regression test queries a value in the "gap" and checks the range was properly eliminated by the BRIN index. This only affects minmax-multi indexes on timestamp/date columns with infinite values, which is not very common in practice. The affected indexes may need to be rebuilt. Backpatch to 14, where minmax-multi indexes were introduced. Reported-by: Ashutosh Bapat Reviewed-by: Ashutosh Bapat, Dean Rasheed Backpatch-through: 14 Discussion: https://postgr.es/m/eef0ea8c-4aaa-8d0d-027f-58b1f35dd170@enterprisedb.com
1 parent 394d517 commit 8da86d6

File tree

3 files changed

+96
-6
lines changed

3 files changed

+96
-6
lines changed

‎src/backend/access/brin/brin_minmax_multi.c

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2085,9 +2085,6 @@ brin_minmax_multi_distance_date(PG_FUNCTION_ARGS)
20852085
DateADT dateVal1 = PG_GETARG_DATEADT(0);
20862086
DateADT dateVal2 = PG_GETARG_DATEADT(1);
20872087

2088-
if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
2089-
PG_RETURN_FLOAT8(0);
2090-
20912088
delta = (float8) dateVal2 - (float8) dateVal1;
20922089

20932090
Assert(delta >= 0);
@@ -2146,9 +2143,6 @@ brin_minmax_multi_distance_timestamp(PG_FUNCTION_ARGS)
21462143
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
21472144
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
21482145

2149-
if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
2150-
PG_RETURN_FLOAT8(0);
2151-
21522146
delta = (float8) dt2 - (float8) dt1;
21532147

21542148
Assert(delta >= 0);

‎src/test/regress/expected/brin_multi.out

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -855,6 +855,63 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
855855
Index Cond: (a = '2023-01-01'::date)
856856
(4 rows)
857857

858+
DROP TABLE brin_date_test;
859+
RESET enable_seqscan;
860+
-- test handling of infinite timestamp values
861+
CREATE TABLE brin_timestamp_test(a TIMESTAMP);
862+
INSERT INTO brin_timestamp_test VALUES ('-infinity'), ('infinity');
863+
INSERT INTO brin_timestamp_test
864+
SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i);
865+
CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
866+
SET enable_seqscan = off;
867+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
868+
SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
869+
QUERY PLAN
870+
------------------------------------------------------------------------------
871+
Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
872+
Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
873+
-> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
874+
Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
875+
(4 rows)
876+
877+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
878+
SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
879+
QUERY PLAN
880+
------------------------------------------------------------------------------
881+
Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
882+
Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
883+
-> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
884+
Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
885+
(4 rows)
886+
887+
DROP TABLE brin_timestamp_test;
888+
RESET enable_seqscan;
889+
-- test handling of infinite date values
890+
CREATE TABLE brin_date_test(a DATE);
891+
INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity');
892+
INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i);
893+
CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
894+
SET enable_seqscan = off;
895+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
896+
SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
897+
QUERY PLAN
898+
-------------------------------------------------------------------------
899+
Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
900+
Recheck Cond: (a = '2023-01-01'::date)
901+
-> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
902+
Index Cond: (a = '2023-01-01'::date)
903+
(4 rows)
904+
905+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
906+
SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
907+
QUERY PLAN
908+
-------------------------------------------------------------------------
909+
Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
910+
Recheck Cond: (a = '1900-01-01'::date)
911+
-> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
912+
Index Cond: (a = '1900-01-01'::date)
913+
(4 rows)
914+
858915
DROP TABLE brin_date_test;
859916
RESET enable_seqscan;
860917
RESET datestyle;

‎src/test/regress/sql/brin_multi.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -622,6 +622,45 @@ SET enable_seqscan = off;
622622
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
623623
SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
624624

625+
DROP TABLE brin_date_test;
626+
RESET enable_seqscan;
627+
628+
-- test handling of infinite timestamp values
629+
CREATE TABLE brin_timestamp_test(a TIMESTAMP);
630+
631+
INSERT INTO brin_timestamp_test VALUES ('-infinity'), ('infinity');
632+
INSERT INTO brin_timestamp_test
633+
SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i);
634+
635+
CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
636+
637+
SET enable_seqscan = off;
638+
639+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
640+
SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
641+
642+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
643+
SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
644+
645+
DROP TABLE brin_timestamp_test;
646+
RESET enable_seqscan;
647+
648+
-- test handling of infinite date values
649+
CREATE TABLE brin_date_test(a DATE);
650+
651+
INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity');
652+
INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i);
653+
654+
CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
655+
656+
SET enable_seqscan = off;
657+
658+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
659+
SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
660+
661+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
662+
SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
663+
625664
DROP TABLE brin_date_test;
626665
RESET enable_seqscan;
627666
RESET datestyle;

0 commit comments

Comments
 (0)
0