v2 updates prosupport function OIDs, as the ones I initially used were taken while this waits for review.
This patch adds paths where 'order by f(x)' is satisfied by an index on x
if f(x) is monotonic.
You can find examples of how this feature can be useful in the test file,
including
- Ordered outputs without a sorting node.
- GroupAggregate used directly on the index scan
- MinMaxAggregate replaced by Index scan + limit.
My favourite use case is
+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)
Where we can take advantage of an index on timestamp src(ts) to fulfill a
query aggregating monthly. With this feature the same timestamp column can
be used to either sort the rows, or aggregate by different intervals, e.g.
days, weeks, months or years. Great for reports or dashboards on busy
tables, as we only need one index.
On Wed, Feb 11, 2026 at 9:02 PM Alexandre Felipe <
[email protected]> wrote:
> Motivation:
> Consider a table data with an indexed timestamp column ts,
> the query `SELECT ts::date, count(1) GROUP BY 1;` requires
> ts::date to be sorted, but the planner is not aware of the fact
> that ts::date will ordered whenever ts is ordered.
>
> This includes slope information to several builtin functions.
> type casting, addition, subtraction, common mathematical functions,
> e.g. atan, sinh, log, exp, erf, etc. And some date manipulation function.
> This may not be complete, but already covers obvious cases.
>
> e.g. SELECT created_at::date, count(1) FROM tasks GROUP BY 1;
>
> can use an index on created_at, to count tasks per day.
>
> You can find examples of howthis feature can be useful in the test file,
> including
> - Ordered outputs without a sorting node.
> - GroupAggregate used directly on the index scan
> - MinMaxAggregate replaced by Index scan + limit.
>
>
>
v2-0003-SLOPE-Tests.patch
Description: Binary data
v2-0001-SLOPE-Analysis-Machinery.patch
Description: Binary data
v2-0002-SLOPE-Builtin-support.patch
Description: Binary data
