BRIN minmax multi - incorrect distance for infinite timestamp/date

[Tomas Vondra]

Hi,

Ashutosh Bapat reported me off-list a possible issue in how BRIN
minmax-multi calculate distance for infinite timestamp/date values.

The current code does this:

    if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
        PG_RETURN_FLOAT8(0);

so means infinite values are "very close" to any other value, and thus
likely to be merged into a summary range. That's exactly the opposite of
what we want to do, possibly resulting in inefficient indexes.

Consider this example

    create table test (a timestamptz) with (fillfactor=50);

    insert into test
    select (now() + ((10000 * random())::int || ' seconds')::interval)
      from generate_series(1,1000000) s(i);

    update test set a = '-infinity'::timestamptz where random() < 0.01;
    update test set a = 'infinity'::timestamptz where random() < 0.01;

    explain (analyze, timing off, costs off)
     select * from test where a = '2024-01-01'::timestamptz;

                                  QUERY PLAN

------------------------------------------------------------------------------
 Bitmap Heap Scan on test (actual rows=0 loops=1)
   Recheck Cond: (a = '2024-01-01 00:00:00+01'::timestamp with time zone)
   Rows Removed by Index Recheck: 680662
   Heap Blocks: lossy=6024
   ->  Bitmap Index Scan on test_a_idx (actual rows=60240 loops=1)
         Index Cond: (a = '2024-01-01 00:00:00+01'::timestamp with time
zone)
 Planning Time: 0.075 ms
 Execution Time: 106.871 ms
(8 rows)

Clearly, large part of the table gets scanned - this happens because
when building the index, we end up with ranges like this:


    [-infinity,a,b,c,...,x,y,z,infinity]

and we conclude that distance for [-infinity,a] is 0, and we combine
these values into a range. And the same for [z,infinity]. But we should
do exactly the opposite thing - never merge those.

Attached is a patch fixing this, with which the plan looks like this:

                                  QUERY PLAN

------------------------------------------------------------------------------
 Bitmap Heap Scan on test (actual rows=0 loops=1)
   Recheck Cond: (a = '2024-01-01 00:00:00+01'::timestamp with time zone)
   ->  Bitmap Index Scan on test_a_idx (actual rows=0 loops=1)
         Index Cond: (a = '2024-01-01 00:00:00+01'::timestamp with time
zone)
 Planning Time: 0.289 ms
 Execution Time: 9.432 ms
(6 rows)

Which seems much better.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c
index f8b2a3f9bc..c8775c274e 100644
--- a/src/backend/access/brin/brin_minmax_multi.c
+++ b/src/backend/access/brin/brin_minmax_multi.c
@@ -2084,8 +2084,14 @@ brin_minmax_multi_distance_date(PG_FUNCTION_ARGS)
 	DateADT		dateVal1 = PG_GETARG_DATEADT(0);
 	DateADT		dateVal2 = PG_GETARG_DATEADT(1);
 
+	/*
+	 * If either value is infinite, we treat them as in infinite distance.
+	 * We deduplicate the values before calculating distances for them, so
+	 * either one value is finite, or the sign is different - so the
+	 * inifinite distance is appropriate for both cases.
+	 */
 	if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
-		PG_RETURN_FLOAT8(0);
+		PG_RETURN_FLOAT8(get_float8_infinity());
 
 	PG_RETURN_FLOAT8(dateVal1 - dateVal2);
 }
@@ -2141,8 +2147,14 @@ brin_minmax_multi_distance_timestamp(PG_FUNCTION_ARGS)
 	Timestamp	dt1 = PG_GETARG_TIMESTAMP(0);
 	Timestamp	dt2 = PG_GETARG_TIMESTAMP(1);
 
+	/*
+	 * If either value is infinite, we treat them as in infinite distance.
+	 * We deduplicate the values before calculating distances for them, so
+	 * either one value is finite, or the sign is different - so the
+	 * inifinite distance is appropriate for both cases.
+	 */
 	if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
-		PG_RETURN_FLOAT8(0);
+		PG_RETURN_FLOAT8(get_float8_infinity());
 
 	delta = dt2 - dt1;