0003-SLOPE-Tests.patch

From 1b9f644f2feaa4011469599a7aef4263ad4d7508 Mon Sep 17 00:00:00 2001
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Wed, 11 Feb 2026 20:58:09 +0000
Subject: [PATCH 3/3] SLOPE Tests

This verifies/illustrates ways how this feature can be useful.
 - Ordered outputs without a sorting node.
- GroupAggregate used directly on the index scan
- MinMaxAggregate replaced by Index scan + limit.
---
 src/test/regress/expected/slope.out | 443 ++++++++++++++++++++++++++++
 src/test/regress/parallel_schedule  |   1 +
 src/test/regress/sql/slope.sql      | 259 ++++++++++++++++
 3 files changed, 703 insertions(+)
 create mode 100644 src/test/regress/expected/slope.out
 create mode 100644 src/test/regress/sql/slope.sql

diff --git a/src/test/regress/expected/slope.out b/src/test/regress/expected/slope.out
new file mode 100644
index 00000000000..e49690c8c55
--- /dev/null
+++ b/src/test/regress/expected/slope.out
@@ -0,0 +1,443 @@
+--
+-- Tests for monotonic function sort optimization.
+--
+-- When a function is declared monotonic via prosupport, the planner can
+-- use an index on 'x' to satisfy ORDER BY / GROUP BY on 'f(x)' without
+-- an extra Sort node.
+--
+create table src (
+    id serial primary key,
+    ts timestamp not null,
+    tstz timestamptz not null,
+    v_int2 int2 not null,
+    v_int4 int4 not null,
+    v_int8 int8 not null,
+    v_float4 float4 not null,
+    v_float8 float8 not null,
+    v_numeric numeric not null
+);
+create index on src (ts);
+create index on src (tstz);
+create index on src (v_int4);
+create index on src (v_float8);
+create index on src (v_numeric);
+-- Insert some data so the planner has statistics.
+insert into src (ts, tstz, v_int2, v_int4, v_int8, v_float4, v_float8, v_numeric)
+select
+    '2020-01-01'::timestamp + (i || ' hours')::interval,
+    '2020-01-01'::timestamptz + (i || ' hours')::interval,
+    (i % 32000)::int2,
+    i,
+    i::int8,
+    i::float4,
+    i::float8,
+    i::numeric
+from generate_series(1, 1000) i;
+analyze src;
+-- Disable hashagg to show GroupAggregate using index ordering.
+set enable_hashagg = off;
+set enable_seqscan = off;
+set enable_bitmapscan = off;
+--
+-- date_trunc: should use index on ts without a Sort node.
+--
+explain (costs off, verbose)
+select date_trunc('month', ts), count(*)
+from src
+group by 1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ GroupAggregate
+   Output: (date_trunc('month'::text, ts)), count(*)
+   Group Key: date_trunc('month'::text, src.ts)
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: date_trunc('month'::text, ts)
+(5 rows)
+
+explain (costs off, verbose)
+select date_trunc('day', ts)
+from src
+order by 1;
+                   QUERY PLAN                   
+------------------------------------------------
+ Index Only Scan using src_ts_idx on public.src
+   Output: date_trunc('day'::text, ts)
+(2 rows)
+
+--
+-- date_trunc with timestamptz
+--
+explain (costs off, verbose)
+select date_trunc('month', tstz), count(*)
+from src
+group by 1;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ GroupAggregate
+   Output: (date_trunc('month'::text, tstz)), count(*)
+   Group Key: date_trunc('month'::text, src.tstz)
+   ->  Index Only Scan using src_tstz_idx on public.src
+         Output: date_trunc('month'::text, tstz)
+(5 rows)
+
+--
+-- date_bin: should also use index on ts without a Sort.
+--
+explain (costs off, verbose)
+select date_bin('1 hour'::interval, ts, '2020-01-01'::timestamp), count(*)
+from src
+group by 1;
+                                                    QUERY PLAN                                                     
+-------------------------------------------------------------------------------------------------------------------
+ GroupAggregate
+   Output: (date_bin('@ 1 hour'::interval, ts, 'Wed Jan 01 00:00:00 2020'::timestamp without time zone)), count(*)
+   Group Key: date_bin('@ 1 hour'::interval, src.ts, 'Wed Jan 01 00:00:00 2020'::timestamp without time zone)
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: date_bin('@ 1 hour'::interval, ts, 'Wed Jan 01 00:00:00 2020'::timestamp without time zone)
+(5 rows)
+
+reset enable_hashagg;
+--
+-- Type conversion: int4 -> int8 is monotonic, should use index on v_int4.
+--
+explain (costs off, verbose)
+select v_int4::int8
+from src
+order by 1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Only Scan using src_v_int4_idx on public.src
+   Output: (v_int4)::bigint
+(2 rows)
+
+--
+-- Type conversion: int4 -> float8 is monotonic.
+--
+explain (costs off, verbose)
+select v_int4::float8
+from src
+order by 1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Only Scan using src_v_int4_idx on public.src
+   Output: (v_int4)::double precision
+(2 rows)
+
+--
+-- Type conversion: int4 -> numeric is monotonic.
+--
+explain (costs off, verbose)
+select v_int4::numeric
+from src
+order by 1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Only Scan using src_v_int4_idx on public.src
+   Output: (v_int4)::numeric
+(2 rows)
+
+--
+-- floor/ceil/round/trunc: monotonic increasing
+--
+explain (costs off, verbose)
+select floor(v_float8)
+from src
+order by 1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Index Only Scan using src_v_float8_idx on public.src
+   Output: floor(v_float8)
+(2 rows)
+
+explain (costs off, verbose)
+select ceil(v_float8)
+from src
+order by 1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Index Only Scan using src_v_float8_idx on public.src
+   Output: ceil(v_float8)
+(2 rows)
+
+explain (costs off, verbose)
+select round(v_numeric, 0)
+from src
+order by 1;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Index Only Scan using src_v_numeric_idx on public.src
+   Output: round(v_numeric, 0)
+(2 rows)
+
+explain (costs off, verbose)
+select trunc(v_numeric, 2)
+from src
+order by 1;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Index Only Scan using src_v_numeric_idx on public.src
+   Output: trunc(v_numeric, 2)
+(2 rows)
+
+-- Composed functions
+explain (costs off, verbose)
+select round(v_int4::numeric, 4)
+from src
+order by 1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Only Scan using src_v_int4_idx on public.src
+   Output: round((v_int4)::numeric, 4)
+(2 rows)
+
+explain (costs off, verbose)
+select round(v_int4::numeric, 4)
+from src
+order by 1 desc;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Index Only Scan Backward using src_v_int4_idx on public.src
+   Output: round((v_int4)::numeric, 4)
+(2 rows)
+
+explain (costs off, verbose)
+select round(-v_int4::numeric, 4)
+from src
+order by 1 desc;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Index Only Scan using src_v_int4_idx on public.src
+   Output: round((- (v_int4)::numeric), 4)
+(2 rows)
+
+--
+-- timestamp -> date: monotonic increasing, should use index on ts.
+--
+explain (costs off, verbose)
+select ts::date
+from src
+order by 1;
+                   QUERY PLAN                   
+------------------------------------------------
+ Index Only Scan using src_ts_idx on public.src
+   Output: (ts)::date
+(2 rows)
+
+--
+-- Negative test: date_trunc with non-const first arg should NOT eliminate sort.
+--
+explain (costs off, verbose)
+select date_trunc(case when id > 500 then 'month' else 'day' end, ts)
+from src
+order by 1;
+                                             QUERY PLAN                                             
+----------------------------------------------------------------------------------------------------
+ Sort
+   Output: (date_trunc(CASE WHEN (id > 500) THEN 'month'::text ELSE 'day'::text END, ts))
+   Sort Key: (date_trunc(CASE WHEN (src.id > 500) THEN 'month'::text ELSE 'day'::text END, src.ts))
+   ->  Seq Scan on public.src
+         Disabled: true
+         Output: date_trunc(CASE WHEN (id > 500) THEN 'month'::text ELSE 'day'::text END, ts)
+(6 rows)
+
+--
+-- Negative test: a non-monotonic function should NOT eliminate sort.
+--
+explain (costs off, verbose )
+select extract(month from ts)
+from src
+order by 1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Sort
+   Output: (EXTRACT(month FROM ts))
+   Sort Key: (EXTRACT(month FROM src.ts))
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: EXTRACT(month FROM ts)
+(5 rows)
+
+-- Verify correctness: results should be sorted.
+select date_trunc('month', ts), count(*)
+from src
+group by 1
+order by 1
+limit 5;
+        date_trunc        | count 
+--------------------------+-------
+ Wed Jan 01 00:00:00 2020 |   743
+ Sat Feb 01 00:00:00 2020 |   257
+(2 rows)
+
+select ts::date, count(*)
+from src
+group by 1
+order by 1
+limit 5;
+     ts     | count 
+------------+-------
+ 01-01-2020 |    23
+ 01-02-2020 |    24
+ 01-03-2020 |    24
+ 01-04-2020 |    24
+ 01-05-2020 |    24
+(5 rows)
+
+--
+-- MIN/MAX aggregate optimization: uses index scan when expression is monotonic
+--
+-- MIN of monotonic increasing function uses forward index scan
+explain (costs off)
+select min(atan(v_int4))
+from src;
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Result
+   Replaces: MinMaxAggregate
+   InitPlan minmax_1
+     ->  Limit
+           ->  Index Only Scan using src_v_int4_idx on src
+                 Filter: (atan((v_int4)::double precision) IS NOT NULL)
+(6 rows)
+
+-- MAX of monotonic increasing function uses backward index scan
+explain (costs off)
+select max(atan(v_int4))
+from src;
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Result
+   Replaces: MinMaxAggregate
+   InitPlan minmax_1
+     ->  Limit
+           ->  Index Only Scan Backward using src_v_int4_idx on src
+                 Filter: (atan((v_int4)::double precision) IS NOT NULL)
+(6 rows)
+
+-- MIN of monotonic decreasing expression uses backward index scan
+-- (atan(1) - atan(x) is decreasing in x
+explain (costs off)
+select min(atan(1) - atan(v_int4))
+from src;
+                                                    QUERY PLAN                                                     
+-------------------------------------------------------------------------------------------------------------------
+ Result
+   Replaces: MinMaxAggregate
+   InitPlan minmax_1
+     ->  Limit
+           ->  Index Only Scan Backward using src_v_int4_idx on src
+                 Filter: (('0.7853981633974483'::double precision - atan((v_int4)::double precision)) IS NOT NULL)
+(6 rows)
+
+-- MAX of monotonic decreasing expression uses forward index scan
+explain (costs off)
+select round(max(atan(1) - atan(v_int4))::numeric, 2)
+from src;
+                                                    QUERY PLAN                                                     
+-------------------------------------------------------------------------------------------------------------------
+ Result
+   Replaces: MinMaxAggregate
+   InitPlan minmax_1
+     ->  Limit
+           ->  Index Only Scan using src_v_int4_idx on src
+                 Filter: (('0.7853981633974483'::double precision - atan((v_int4)::double precision)) IS NOT NULL)
+(6 rows)
+
+-- Composed expression: MIN of (constant + decreasing) = decreasing
+explain (costs off)
+select min(1 + (2 - v_int4::float4))
+from src;
+                                                QUERY PLAN                                                
+----------------------------------------------------------------------------------------------------------
+ Result
+   Replaces: MinMaxAggregate
+   InitPlan minmax_1
+     ->  Limit
+           ->  Index Only Scan Backward using src_v_int4_idx on src
+                 Filter: (('1'::double precision + ('2'::double precision - (v_int4)::real)) IS NOT NULL)
+(6 rows)
+
+--
+-- Multi-column index: monotonic unwrapping only on the LAST sort key
+--
+-- Non-injective functions (like floor, round) on earlier keys could cause
+-- incorrect ordering of subsequent keys within groups of equal values.
+--
+create index on src (v_int4, v_int8);
+-- First column is plain var, second has monotonic function - CAN use index
+explain (costs off)
+select *
+from src
+order by v_int4, -v_int8 desc;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Index Scan using src_v_int4_v_int8_idx on src
+(1 row)
+
+-- Second column in opposite order - needs incremental sort
+explain (costs off)
+select *
+from src
+order by v_int4, -v_int8;
+                  QUERY PLAN                  
+----------------------------------------------
+ Incremental Sort
+   Sort Key: v_int4, v_int8 DESC
+   Presorted Key: v_int4
+   ->  Index Scan using src_v_int4_idx on src
+(4 rows)
+
+--
+-- Join tests: optimization works with joins on single-table expressions
+--
+create table src2 (id int primary key, val int);
+create index on src2 (val);
+insert into src2 select i, i from generate_series(1, 100) i;
+analyze src2;
+-- Inner join: ORDER BY on monotonic expression of one table works
+explain (costs off)
+select *
+from src s1 join src2 s2 on s1.v_int4 = s2.id
+order by atan(s1.v_int4);
+                   QUERY PLAN                    
+-------------------------------------------------
+ Merge Join
+   Merge Cond: (s1.v_int4 = s2.id)
+   ->  Index Scan using src_v_int4_idx on src s1
+   ->  Index Scan using src2_pkey on src2 s2
+(4 rows)
+
+-- Inner join: ORDER BY on expression with constant works
+explain (costs off)
+select *
+from src s1 join src2 s2 on s1.v_int4 = s2.id
+order by s1.v_int4 + 100;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Merge Join
+   Merge Cond: (s1.v_int4 = s2.id)
+   ->  Index Scan using src_v_int4_idx on src s1
+   ->  Index Scan using src2_pkey on src2 s2
+(4 rows)
+
+-- Cross join: ORDER BY on expression involving both tables cannot use index
+-- (this should NOT use the monotonicity optimization)
+explain (costs off)
+select *
+from src s1, src2 s2
+where s1.v_int4 < 10 and s2.val < 10
+order by s1.v_int4 + s2.val;
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Sort
+   Sort Key: ((s1.v_int4 + s2.val))
+   ->  Nested Loop
+         ->  Index Scan using src_v_int4_idx on src s1
+               Index Cond: (v_int4 < 10)
+         ->  Materialize
+               ->  Index Scan using src2_val_idx on src2 s2
+                     Index Cond: (val < 10)
+(8 rows)
+
+drop table src2;
+-- Clean up.
+drop table src;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 549e9b2d7be..30f3aea4d98 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -77,6 +77,7 @@ test: brin_bloom brin_multi
 # Another group of parallel tests
 # ----------
 test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions nls sysviews tsrf tid tidscan tidrangescan collate.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps generated_virtual
+test: slope
 
 # collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with each other
 # psql depends on create_am
diff --git a/src/test/regress/sql/slope.sql b/src/test/regress/sql/slope.sql
new file mode 100644
index 00000000000..0e1db79707a
--- /dev/null
+++ b/src/test/regress/sql/slope.sql
@@ -0,0 +1,259 @@
+--
+-- Tests for monotonic function sort optimization.
+--
+-- When a function is declared monotonic via prosupport, the planner can
+-- use an index on 'x' to satisfy ORDER BY / GROUP BY on 'f(x)' without
+-- an extra Sort node.
+--
+
+create table src (
+    id serial primary key,
+    ts timestamp not null,
+    tstz timestamptz not null,
+    v_int2 int2 not null,
+    v_int4 int4 not null,
+    v_int8 int8 not null,
+    v_float4 float4 not null,
+    v_float8 float8 not null,
+    v_numeric numeric not null
+);
+
+create index on src (ts);
+create index on src (tstz);
+create index on src (v_int4);
+create index on src (v_float8);
+create index on src (v_numeric);
+
+-- Insert some data so the planner has statistics.
+insert into src (ts, tstz, v_int2, v_int4, v_int8, v_float4, v_float8, v_numeric)
+select
+    '2020-01-01'::timestamp + (i || ' hours')::interval,
+    '2020-01-01'::timestamptz + (i || ' hours')::interval,
+    (i % 32000)::int2,
+    i,
+    i::int8,
+    i::float4,
+    i::float8,
+    i::numeric
+from generate_series(1, 1000) i;
+
+analyze src;
+
+-- Disable hashagg to show GroupAggregate using index ordering.
+set enable_hashagg = off;
+set enable_seqscan = off;
+set enable_bitmapscan = off;
+--
+-- date_trunc: should use index on ts without a Sort node.
+--
+explain (costs off, verbose)
+select date_trunc('month', ts), count(*)
+from src
+group by 1;
+
+explain (costs off, verbose)
+select date_trunc('day', ts)
+from src
+order by 1;
+
+--
+-- date_trunc with timestamptz
+--
+explain (costs off, verbose)
+select date_trunc('month', tstz), count(*)
+from src
+group by 1;
+
+--
+-- date_bin: should also use index on ts without a Sort.
+--
+explain (costs off, verbose)
+select date_bin('1 hour'::interval, ts, '2020-01-01'::timestamp), count(*)
+from src
+group by 1;
+
+reset enable_hashagg;
+
+--
+-- Type conversion: int4 -> int8 is monotonic, should use index on v_int4.
+--
+explain (costs off, verbose)
+select v_int4::int8
+from src
+order by 1;
+
+--
+-- Type conversion: int4 -> float8 is monotonic.
+--
+explain (costs off, verbose)
+select v_int4::float8
+from src
+order by 1;
+
+--
+-- Type conversion: int4 -> numeric is monotonic.
+--
+explain (costs off, verbose)
+select v_int4::numeric
+from src
+order by 1;
+
+--
+-- floor/ceil/round/trunc: monotonic increasing
+--
+explain (costs off, verbose)
+select floor(v_float8)
+from src
+order by 1;
+
+explain (costs off, verbose)
+select ceil(v_float8)
+from src
+order by 1;
+
+explain (costs off, verbose)
+select round(v_numeric, 0)
+from src
+order by 1;
+
+explain (costs off, verbose)
+select trunc(v_numeric, 2)
+from src
+order by 1;
+
+-- Composed functions
+explain (costs off, verbose)
+select round(v_int4::numeric, 4)
+from src
+order by 1;
+
+explain (costs off, verbose)
+select round(v_int4::numeric, 4)
+from src
+order by 1 desc;
+
+
+explain (costs off, verbose)
+select round(-v_int4::numeric, 4)
+from src
+order by 1 desc;
+
+--
+-- timestamp -> date: monotonic increasing, should use index on ts.
+--
+explain (costs off, verbose)
+select ts::date
+from src
+order by 1;
+
+--
+-- Negative test: date_trunc with non-const first arg should NOT eliminate sort.
+--
+explain (costs off, verbose)
+select date_trunc(case when id > 500 then 'month' else 'day' end, ts)
+from src
+order by 1;
+
+--
+-- Negative test: a non-monotonic function should NOT eliminate sort.
+--
+explain (costs off, verbose )
+select extract(month from ts)
+from src
+order by 1;
+
+-- Verify correctness: results should be sorted.
+select date_trunc('month', ts), count(*)
+from src
+group by 1
+order by 1
+limit 5;
+
+select ts::date, count(*)
+from src
+group by 1
+order by 1
+limit 5;
+
+--
+-- MIN/MAX aggregate optimization: uses index scan when expression is monotonic
+--
+
+-- MIN of monotonic increasing function uses forward index scan
+explain (costs off)
+select min(atan(v_int4))
+from src;
+
+-- MAX of monotonic increasing function uses backward index scan
+explain (costs off)
+select max(atan(v_int4))
+from src;
+
+-- MIN of monotonic decreasing expression uses backward index scan
+-- (atan(1) - atan(x) is decreasing in x
+explain (costs off)
+select min(atan(1) - atan(v_int4))
+from src;
+
+-- MAX of monotonic decreasing expression uses forward index scan
+explain (costs off)
+select round(max(atan(1) - atan(v_int4))::numeric, 2)
+from src;
+
+-- Composed expression: MIN of (constant + decreasing) = decreasing
+explain (costs off)
+select min(1 + (2 - v_int4::float4))
+from src;
+
+--
+-- Multi-column index: monotonic unwrapping only on the LAST sort key
+--
+-- Non-injective functions (like floor, round) on earlier keys could cause
+-- incorrect ordering of subsequent keys within groups of equal values.
+--
+create index on src (v_int4, v_int8);
+
+-- First column is plain var, second has monotonic function - CAN use index
+explain (costs off)
+select *
+from src
+order by v_int4, -v_int8 desc;
+
+-- Second column in opposite order - needs incremental sort
+explain (costs off)
+select *
+from src
+order by v_int4, -v_int8;
+
+--
+-- Join tests: optimization works with joins on single-table expressions
+--
+create table src2 (id int primary key, val int);
+create index on src2 (val);
+insert into src2 select i, i from generate_series(1, 100) i;
+analyze src2;
+
+-- Inner join: ORDER BY on monotonic expression of one table works
+explain (costs off)
+select *
+from src s1 join src2 s2 on s1.v_int4 = s2.id
+order by atan(s1.v_int4);
+
+-- Inner join: ORDER BY on expression with constant works
+explain (costs off)
+select *
+from src s1 join src2 s2 on s1.v_int4 = s2.id
+order by s1.v_int4 + 100;
+
+-- Cross join: ORDER BY on expression involving both tables cannot use index
+-- (this should NOT use the monotonicity optimization)
+explain (costs off)
+select *
+from src s1, src2 s2
+where s1.v_int4 < 10 and s2.val < 10
+order by s1.v_int4 + s2.val;
+
+drop table src2;
+
+-- Clean up.
+drop table src;
-- 
2.40.0