Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8da86d6

Browse files
committed
Fix minmax-multi on infinite date/timestamp values
Make sure that infinite values in date/timestamp columns are treated asif in infinite distance. Infinite values should not be merged with othervalues, leaving them as outliers. The code however returned distance 0in this case, so that infinite values were merged first. While this doesnot break the index (i.e. it still produces correct query results), itmay make it much less efficient.We don't need explicit handling of infinite date/timestamp values whencalculating distances, because those values are represented as extremebut 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 thandistanced 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 rangewas properly eliminated by the BRIN index.This only affects minmax-multi indexes on timestamp/date columns withinfinite values, which is not very common in practice. The affectedindexes may need to be rebuilt.Backpatch to 14, where minmax-multi indexes were introduced.Reported-by: Ashutosh BapatReviewed-by: Ashutosh Bapat, Dean RasheedBackpatch-through: 14Discussion:https://postgr.es/m/eef0ea8c-4aaa-8d0d-027f-58b1f35dd170@enterprisedb.com
1 parent394d517 commit8da86d6

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
DateADTdateVal1=PG_GETARG_DATEADT(0);
20862086
DateADTdateVal2=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
Timestampdt1=PG_GETARG_TIMESTAMP(0);
21472144
Timestampdt2=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_testWHERE a='2023-01-01'::date;
624624

625+
DROPTABLE brin_date_test;
626+
RESET enable_seqscan;
627+
628+
-- test handling of infinite timestamp values
629+
CREATETABLEbrin_timestamp_test(aTIMESTAMP);
630+
631+
INSERT INTO brin_timestamp_testVALUES ('-infinity'), ('infinity');
632+
INSERT INTO brin_timestamp_test
633+
SELECT iFROM generate_series('2000-01-01'::timestamp,'2000-02-09'::timestamp,'1 day'::interval) s(i);
634+
635+
CREATEINDEXON 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_testWHERE a='2023-01-01'::timestamp;
641+
642+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
643+
SELECT*FROM brin_timestamp_testWHERE a='1900-01-01'::timestamp;
644+
645+
DROPTABLE brin_timestamp_test;
646+
RESET enable_seqscan;
647+
648+
-- test handling of infinite date values
649+
CREATETABLEbrin_date_test(aDATE);
650+
651+
INSERT INTO brin_date_testVALUES ('-infinity'), ('infinity');
652+
INSERT INTO brin_date_testSELECT'2000-01-01'::date+ iFROM generate_series(1,40) s(i);
653+
654+
CREATEINDEXON 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_testWHERE a='2023-01-01'::date;
660+
661+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
662+
SELECT*FROM brin_date_testWHERE a='1900-01-01'::date;
663+
625664
DROPTABLE brin_date_test;
626665
RESET enable_seqscan;
627666
RESET datestyle;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp