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

Commit497fc92

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 parente796522 commit497fc92

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
@@ -2079,9 +2079,6 @@ brin_minmax_multi_distance_date(PG_FUNCTION_ARGS)
20792079
DateADTdateVal1=PG_GETARG_DATEADT(0);
20802080
DateADTdateVal2=PG_GETARG_DATEADT(1);
20812081

2082-
if (DATE_NOT_FINITE(dateVal1)||DATE_NOT_FINITE(dateVal2))
2083-
PG_RETURN_FLOAT8(0);
2084-
20852082
delta= (float8)dateVal2- (float8)dateVal1;
20862083

20872084
Assert(delta >=0);
@@ -2140,9 +2137,6 @@ brin_minmax_multi_distance_timestamp(PG_FUNCTION_ARGS)
21402137
Timestampdt1=PG_GETARG_TIMESTAMP(0);
21412138
Timestampdt2=PG_GETARG_TIMESTAMP(1);
21422139

2143-
if (TIMESTAMP_NOT_FINITE(dt1)||TIMESTAMP_NOT_FINITE(dt2))
2144-
PG_RETURN_FLOAT8(0);
2145-
21462140
delta= (float8)dt2- (float8)dt1;
21472141

21482142
Assert(delta >=0);

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

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

501+
DROP TABLE brin_date_test;
502+
RESET enable_seqscan;
503+
-- test handling of infinite timestamp values
504+
CREATE TABLE brin_timestamp_test(a TIMESTAMP);
505+
INSERT INTO brin_timestamp_test VALUES ('-infinity'), ('infinity');
506+
INSERT INTO brin_timestamp_test
507+
SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i);
508+
CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
509+
SET enable_seqscan = off;
510+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
511+
SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
512+
QUERY PLAN
513+
------------------------------------------------------------------------------
514+
Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
515+
Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
516+
-> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
517+
Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
518+
(4 rows)
519+
520+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
521+
SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
522+
QUERY PLAN
523+
------------------------------------------------------------------------------
524+
Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
525+
Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
526+
-> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
527+
Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
528+
(4 rows)
529+
530+
DROP TABLE brin_timestamp_test;
531+
RESET enable_seqscan;
532+
-- test handling of infinite date values
533+
CREATE TABLE brin_date_test(a DATE);
534+
INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity');
535+
INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i);
536+
CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
537+
SET enable_seqscan = off;
538+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
539+
SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
540+
QUERY PLAN
541+
-------------------------------------------------------------------------
542+
Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
543+
Recheck Cond: (a = '2023-01-01'::date)
544+
-> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
545+
Index Cond: (a = '2023-01-01'::date)
546+
(4 rows)
547+
548+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
549+
SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
550+
QUERY PLAN
551+
-------------------------------------------------------------------------
552+
Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
553+
Recheck Cond: (a = '1900-01-01'::date)
554+
-> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
555+
Index Cond: (a = '1900-01-01'::date)
556+
(4 rows)
557+
501558
DROP TABLE brin_date_test;
502559
RESET enable_seqscan;
503560
RESET datestyle;

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -457,6 +457,45 @@ SET enable_seqscan = off;
457457
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
458458
SELECT*FROM brin_date_testWHERE a='2023-01-01'::date;
459459

460+
DROPTABLE brin_date_test;
461+
RESET enable_seqscan;
462+
463+
-- test handling of infinite timestamp values
464+
CREATETABLEbrin_timestamp_test(aTIMESTAMP);
465+
466+
INSERT INTO brin_timestamp_testVALUES ('-infinity'), ('infinity');
467+
INSERT INTO brin_timestamp_test
468+
SELECT iFROM generate_series('2000-01-01'::timestamp,'2000-02-09'::timestamp,'1 day'::interval) s(i);
469+
470+
CREATEINDEXON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
471+
472+
SET enable_seqscan= off;
473+
474+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
475+
SELECT*FROM brin_timestamp_testWHERE a='2023-01-01'::timestamp;
476+
477+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
478+
SELECT*FROM brin_timestamp_testWHERE a='1900-01-01'::timestamp;
479+
480+
DROPTABLE brin_timestamp_test;
481+
RESET enable_seqscan;
482+
483+
-- test handling of infinite date values
484+
CREATETABLEbrin_date_test(aDATE);
485+
486+
INSERT INTO brin_date_testVALUES ('-infinity'), ('infinity');
487+
INSERT INTO brin_date_testSELECT'2000-01-01'::date+ iFROM generate_series(1,40) s(i);
488+
489+
CREATEINDEXON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
490+
491+
SET enable_seqscan= off;
492+
493+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
494+
SELECT*FROM brin_date_testWHERE a='2023-01-01'::date;
495+
496+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
497+
SELECT*FROM brin_date_testWHERE a='1900-01-01'::date;
498+
460499
DROPTABLE brin_date_test;
461500
RESET enable_seqscan;
462501
RESET datestyle;

0 commit comments

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