Thread

1. SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-02-11T21:02:53Z

   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.
   

2. Re: SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-02-18T21:51:03Z

   Hi Hackers,
   
   Is there anything else I can do to move this forward?
   Anyone could review review? This one should be a quick review!
   
   Regards,
   Alexandre

3. Re: SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-20T06:53:32Z

   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 <
   o.alexandre.felipe@gmail.com> 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.
   >
   >
   >
   

4. Re: SLOPE - Planner optimizations on monotonic expressions.

   Corey Huinker <corey.huinker@gmail.com> — 2026-03-25T05:18:12Z

   >
   > +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)
   >
   
   That's a good one.
   
   +/* Slope type for representing monotonicity */
   +typedef int8 Slope;
   +#define SLOPE_ANY   0   /* 0b00 - unknown/either (safe default) */
   +#define SLOPE_ASC   1   /* 0b01 - ascending (descending blocked) */
   +#define SLOPE_DESC  2   /* 0b10 - descending (ascending blocked) */
   +#define SLOPE_CONST 3   /* 0b11 - constant (both blocked) */
   
   The MonotonicFunction enum seems like a good pattern to follow here.
   
   Nitpick: it's a slope sign (+/-) rather than a slope itself, which to me
   implies a scalar. I can't think of a good singular word for it, either.
   
   + * If the result is SLOPE_ASC or SLOPE_DESC, *underlying_expr is set to the
   + * by checking the slopes of the function arguments and the expression
   + * passed combined as follows:
   
   "is set to the by" - seems like you left out a word here.
   
   Patches 0002-0003 would have to get committed at the same time, but I see
   why you separated them for clarity.
   
   0002 is missing the catversion bump but that's fine at this early stage.
   
   
   So, how would this work with a function like left() with a positive 2nd
   param (assuming specified collation matches the index)? I'm also curious if
   NULLS FIRST/LAST will throw this off.
   

5. Re: SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-25T14:47:58Z

   Thank you for your review Corey,
   
   
   On Wed, Mar 25, 2026 at 5:18 AM Corey Huinker <corey.huinker@gmail.com>
   wrote:
   
   > +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)
   >>
   >
   > That's a good one.
   >
   
   I am glad you noted, I waited for a while for this moment :)
   
   
   > +/* Slope type for representing monotonicity */
   > +typedef int8 Slope;
   > +#define SLOPE_ANY   0   /* 0b00 - unknown/either (safe default) */
   > +#define SLOPE_ASC   1   /* 0b01 - ascending (descending blocked) */
   > +#define SLOPE_DESC  2   /* 0b10 - descending (ascending blocked) */
   > +#define SLOPE_CONST 3   /* 0b11 - constant (both blocked) */
   >
   > The MonotonicFunction enum seems like a good pattern to follow here.
   >
   
   Learning about the existence of that now
   
   typedef enum MonotonicFunction
   {
   MONOTONICFUNC_NONE = 0,
   MONOTONICFUNC_INCREASING = (1 << 0),
   MONOTONICFUNC_DECREASING = (1 << 1),
   MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
   } MonotonicFunction;
   
   So, BOTH means that the function is both increasing and decreasing, thus it
   is constant.
   Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does the
   job.
   But now using an enum we will have 4 bytes per argument.
   
   Nitpick: it's a slope sign (+/-) rather than a slope itself, which to me
   > implies a scalar. I can't think of a good singular word for it, either.
   >
   
   Solved if we use MonotonicFunction I guess. But the word slope isn't always
   used to refer to derivatives.
   And here we are mostly dealing with functions that are constant or
   discontinuous, i.e. they never have a non-zero real derivative.
   
   + * If the result is SLOPE_ASC or SLOPE_DESC, *underlying_expr is set to the
   > + * by checking the slopes of the function arguments and the expression
   > + * passed combined as follows:
   >
   > "is set to the by" - seems like you left out a word here.
   >
   
   rewording like this
   
    * The contribution of each argument to the final slope of the function
    * determined by the slope of the function with respect to an argument
    * and the slope of the underlying expression expression passed to it
    * as follows:
   
   
   > Patches 0002-0003 would have to get committed at the same time, but I see
   > why you separated them for clarity.
   >
   
   >
   0002 is missing the catversion bump but that's fine at this early stage.
   >
   changed catversion this time
   
   So, how would this work with a function like left() with a positive 2nd
   > param (assuming specified collation matches the index)?
   >
   That would require a separate prosupport function, it would have to do some
   extra work, checking if the second argument is a positive constant at
   planning time.
   I'm happy to do that if there is interest, but I would keep after this.
   
   
   > I'm also curious if NULLS FIRST/LAST will throw this off.
   >
   
   All that this will do is, when considering index scans, check if the
   requested order
   matches the index order. And whether we have to do a backwards or forwards
   scan.
   
   + if (mono_decreasing)
   + {
   + reverse_sort = !reverse_sort;
   + nulls_first = !nulls_first;
   + }
   
   I think this covers all the cases under consideration.
   
   Added more test cases at the end of 0003.
   
   Is there a specialized nulls first/last sort or does it use a generic sort?
   
   Regards,
   Alexandre
   

6. Re: SLOPE - Planner optimizations on monotonic expressions.

   Corey Huinker <corey.huinker@gmail.com> — 2026-03-25T17:26:54Z

   >
   > The MonotonicFunction enum seems like a good pattern to follow here.
   >>
   >
   > Learning about the existence of that now
   >
   > typedef enum MonotonicFunction
   > {
   > MONOTONICFUNC_NONE = 0,
   > MONOTONICFUNC_INCREASING = (1 << 0),
   > MONOTONICFUNC_DECREASING = (1 << 1),
   > MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
   > } MonotonicFunction;
   >
   > So, BOTH means that the function is both increasing and decreasing, thus
   > it is constant.
   > Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does the
   > job.
   > But now using an enum we will have 4 bytes per argument.
   >
   
   Sorry, I wasn't suggesting that you use MonotonicFunction outright, only
   that you follow it's pattern (make an enum, if you're needing specific bit
   patterns do it this way, etc. We often use enums as a way for switch
   statements to warn us when we're missing values.
   
   
   >
   > So, how would this work with a function like left() with a positive 2nd
   >> param (assuming specified collation matches the index)?
   >>
   > That would require a separate prosupport function, it would have to do
   > some extra work, checking if the second argument is a positive constant at
   > planning time.
   > I'm happy to do that if there is interest, but I would keep after this.
   >
   >
   >> I'm also curious if NULLS FIRST/LAST will throw this off.
   >>
   >
   > All that this will do is, when considering index scans, check if the
   > requested order
   > matches the index order. And whether we have to do a backwards or forwards
   > scan.
   >
   
   What I meant was will we need a custom function to inspect parameters such
   that we *do* try the index optimization on substr(foo,x,..) if and only if
   x = 1?
   
   It almost seems like what we need is "does f(x) preserve the leading sort
   bits of x, and reduce the trailing bits to ties?"
   
   Is there a specialized nulls first/last sort or does it use a generic sort?
   >
   
   No, it just fed into my wondering about situations where the ordering of
   f(x) would be modified away from the ordering of x.
   
   Oh, one other thing I am curious if any other reviewers like/dislike the
   
       static const Slope pattern[2] = {SLOPE_ASC, SLOPE_DESC};
   
   pattern. I haven't seen that one done before, and I'm wondering if we need
   some comments to explain how it works so that people don't start looking
   for the pfree that they'll never find.
   

7. Re: SLOPE - Planner optimizations on monotonic expressions.

   Zsolt Parragi <zsolt.parragi@percona.com> — 2026-03-25T20:57:23Z

   Hello
   
   -  proname => 'time_pl_interval', prorettype => 'time',
   -  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
   +  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
   +  prorettype => 'time', proargtypes => 'time interval',
   +  prosrc => 'time_pl_interval' },
   
   I think this is incorrect, time can wrap around and isn't monotonic.
   
   CREATE TABLE time_wrap_test (t time PRIMARY KEY);
   INSERT INTO time_wrap_test VALUES
     ('20:00'), ('21:00'), ('22:00'), ('23:00'), ('23:30'),
     ('00:00'), ('01:00'), ('02:00'), ('03:00'), ('04:00');
   SELECT t, t + interval '3 hours' AS t_plus_3h
   FROM time_wrap_test
   ORDER BY t + interval '3 hours';
   
   + /* Check each index on this relation */
   + foreach(lc, rel->indexlist)
   + {
   + IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
   
   Isn't a sortopfamily check missing from this?
   
   > Added one line to .gitignore file because I like to keep
   > data related to this in the project workarea, e.g.
   > ".install", ".dbdata", ".patches", and I don't want those
   
   +
   +# ignore hidden files
   +.*
   
   This doesn't seem related to the patch.
   
   
   
   

8. Re: SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-25T22:22:03Z

   On Wed, Mar 25, 2026 at 5:27 PM Corey Huinker <corey.huinker@gmail.com>
   wrote:
   
   > The MonotonicFunction enum seems like a good pattern to follow here.
   >>>
   >>
   >> Learning about the existence of that now
   >>
   >> typedef enum MonotonicFunction
   >> {
   >> MONOTONICFUNC_NONE = 0,
   >> MONOTONICFUNC_INCREASING = (1 << 0),
   >> MONOTONICFUNC_DECREASING = (1 << 1),
   >> MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
   >> } MonotonicFunction;
   >>
   >> So, BOTH means that the function is both increasing and decreasing, thus
   >> it is constant.
   >> Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does
   >> the job.
   >> But now using an enum we will have 4 bytes per argument.
   >>
   >
   > Sorry, I wasn't suggesting that you use MonotonicFunction outright, only
   > that you follow it's pattern (make an enum, if you're needing specific bit
   > patterns do it this way, etc. We often use enums as a way for switch
   > statements to warn us when we're missing values.
   >
   
   No worries, that was easy as the values coincided.
   
   
   >> So, how would this work with a function like left() with a positive 2nd
   >>> param (assuming specified collation matches the index)?
   >>>
   >> That would require a separate prosupport function, it would have to do
   >> some extra work, checking if the second argument is a positive constant at
   >> planning time.
   >> I'm happy to do that if there is interest, but I would keep after this.
   >>
   >>
   >>> I'm also curious if NULLS FIRST/LAST will throw this off.
   >>>
   >>
   >> All that this will do is, when considering index scans, check if the
   >> requested order
   >> matches the index order. And whether we have to do a backwards or
   >> forwards scan.
   >>
   >
   > What I meant was will we need a custom function to inspect parameters such
   > that we *do* try the index optimization on substr(foo,x,..) if and only if
   > x = 1?
   >
   What I would do in this case is
   if(x is constant and x > 0) {
     return {INCREASING, BOTH, NONE}
   }else{
     return {NONE, BOTH, NONE}
   }
   
   It almost seems like what we need is "does f(x) preserve the leading sort
   > bits of x, and reduce the trailing bits to ties?"
   >
   
   Yes, this is definitely the most common case, the few exceptions are there
   for completeness, but I don't believe people will want to order by e.g.
   atan(x) very often.
   
   Is there a specialized nulls first/last sort or does it use a generic sort?
   >>
   >
   > No, it just fed into my wondering about situations where the ordering of
   > f(x) would be modified away from the ordering of x.
   >
   There is one caveat, my current approach for nulls first/last is valid only
   if f(NULL) is NULL
   
   
   > Oh, one other thing I am curious if any other reviewers like/dislike the
   >
   >     static const Slope pattern[2] = {SLOPE_ASC, SLOPE_DESC};
   >
   > pattern. I haven't seen that one done before, and I'm wondering if we need
   > some comments to explain how it works so that people don't start looking
   > for the pfree that they'll never find.
   >
   
   OK, adding this
   + req.expr = (Node *) expr;
   + /* prosupport function sets req.slopes to a static
   + * pointer, no pfree is required.
   + */
   + req.slopes = NULL;
   

9. Re: SLOPE - Planner optimizations on monotonic expressions.

   Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-25T23:34:40Z

   On Wed, Mar 25, 2026 at 8:57 PM Zsolt Parragi <zsolt.parragi@percona.com>
   wrote:
   
   > Hello
   >
   > -  proname => 'time_pl_interval', prorettype => 'time',
   > -  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
   > +  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
   > +  prorettype => 'time', proargtypes => 'time interval',
   > +  prosrc => 'time_pl_interval' },
   >
   > I think this is incorrect, time can wrap around and isn't monotonic.
   >
   > CREATE TABLE time_wrap_test (t time PRIMARY KEY);
   > INSERT INTO time_wrap_test VALUES
   >   ('20:00'), ('21:00'), ('22:00'), ('23:00'), ('23:30'),
   >   ('00:00'), ('01:00'), ('02:00'), ('03:00'), ('04:00');
   > SELECT t, t + interval '3 hours' AS t_plus_3h
   > FROM time_wrap_test
   > ORDER BY t + interval '3 hours';
   >
   
   Good catch,
   Can you think of any other type that would wrap,
   int2, int4, int8, money, float4, float8, date, timestamp, timestamptz,
   interval
   all raise some error.
   time and timetz wrap around.
   
   
   @@ -4820,21 +4820,17 @@
      prosrc => 'width_bucket_numeric' },
   
    { oid => '1747',
   -  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
   -  prorettype => 'time', proargtypes => 'time interval',
   -  prosrc => 'time_pl_interval' },
   +  proname => 'time_pl_interval', prorettype => 'time',
   +  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
    { oid => '1748',
   -  proname => 'time_mi_interval', prosupport => 'diff_slope_support',
   -  prorettype => 'time', proargtypes => 'time interval',
   -  prosrc => 'time_mi_interval' },
   +  proname => 'time_mi_interval', prorettype => 'time',
   +  proargtypes => 'time interval', prosrc => 'time_mi_interval' },
    { oid => '1749',
   -  proname => 'timetz_pl_interval', prosupport => 'addition_slope_support',
   -  prorettype => 'timetz', proargtypes => 'timetz interval',
   -  prosrc => 'timetz_pl_interval' },
   +  proname => 'timetz_pl_interval', prorettype => 'timetz',
   +  proargtypes => 'timetz interval', prosrc => 'timetz_pl_interval' },
    { oid => '1750',
   -  proname => 'timetz_mi_interval', prosupport => 'diff_slope_support',
   -  prorettype => 'timetz', proargtypes => 'timetz interval',
   -  prosrc => 'timetz_mi_interval' },
   +  proname => 'timetz_mi_interval', prorettype => 'timetz',
   +  proargtypes => 'timetz interval', prosrc => 'timetz_mi_interval' },
   
    { oid => '1764', descr => 'increment by one',
      proname => 'numeric_inc', prorettype => 'numeric', proargtypes =>
   'numeric',
   
   
   
   
   > + /* Check each index on this relation */
   > + foreach(lc, rel->indexlist)
   > + {
   > + IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
   >
   > Isn't a sortopfamily check missing from this?
   >
   
   You are probably right but I am confused here.
   We could simply skip this analysis entirely if the index has a custom
   operator family.
   Do you think this would be enough?
   
   
   > Added one line to .gitignore file because I like to keep
   > > data related to this in the project workarea, e.g.
   > > ".install", ".dbdata", ".patches", and I don't want those
   >
   > +
   > +# ignore hidden files
   > +.*
   >
   > This doesn't seem related to the patch.
   >
   
   Yes, it is not specific for this patch, just annoying that it doesn't
   ignore hidden files.
   
   PS.
   The v3 patch failed on the CI checks, I was testing without assertions.
   The problem was that v2-0001 had a
   + sortkey = mono_var;
   modifying the sort key from the query, and this caused an assertion to fail.
   v4 keeps the sort key untouched but has checks
   ec_member_is_monotonic_in
   in both pathkey_is_monotonic_of to determine if the available order is
   useful,
   and again in build_index_pathkeys that creates a trivial equivalence class
   to be used in pathkey_is_monotonic_of with the index key if it doesn't
   belong to one already.
   Maybe this can be improved later, but it is likely better than a broken
   build.
   
   
   Regards,
   Alexandre
   

10. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-26T09:51:56Z

    Zsolt,
    
    v5 aims to prevent the elimination of the sort node if the index has a
    custom sort operator family.
    
    + /* Check each index on this relation */
    > + foreach(lc, rel->indexlist)
    > + {
    > + IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
    >
    >>
    > Isn't a sortopfamily check missing from this?
    > You are probably right but I am confused here.
    > We could simply skip this analysis entirely if the index has a custom
    > operator family.
    > Do you think this would be enough?
    >
    
    
    
    The benchmark table had these 24 indices:
    19 single-column: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p (all int),
    ts (timestamp), v_float8 (float8), v_numeric (numeric)
    4 multi-column: (a, b), (c, d, e), (ts, a), (v_float8, a)
    1 primary key: id (serial)
    
    Evaluating the effect of v5 on planning time (in microseconds)
    ORDER BY            Master  Patched  Delta  Overhead
    (nothing)            21.3    21.5    +0.2      ~1%
    a                    28.2    29.5    +1.3      ~5%
    a + 1                29.2    32.4    +3.2     ~11%
    a::float8            30.1    34.6    +4.5     ~15%
    date_trunc(ts)       30.0    31.4    +1.4      ~5%
    
    
    
    + /*
    + * The query pathkey's opfamily is the default for
    + * the sort expression's type.  If it matches the
    + * index opfamily, the index uses the standard
    + * ordering — no catalog lookup needed.  Otherwise
    + * fall back to GetDefaultOpClass to handle the
    + * cross-type case (e.g. int4 cast to float8).
    + */
    + if (qpk->pk_opfamily != index->sortopfamily[i])
    + {
    + default_opclass = GetDefaultOpClass(
    + index->opcintype[i], BTREE_AM_OID);
    + default_opfamily = OidIsValid(default_opclass)
    + ? get_opclass_family(default_opclass)
    + : InvalidOid;
    +
    + if (index->sortopfamily[i] != default_opfamily)
    + break;
    + }
    
    
    Regards,
    Alexandre
    

11. Re: SLOPE - Planner optimizations on monotonic expressions.

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-03-26T18:17:11Z

    On Wed, 25 Mar 2026 at 23:35, Alexandre Felipe
    <o.alexandre.felipe@gmail.com> wrote:
    >
    > Good catch,
    > Can you think of any other type that would wrap,
    
    Arithmetic with intervals isn't monotonic in general because of the
    way interval time units vary.
    
    For example, '1 month - 29 days' is "positive" (in the sense that it
    compares as greater than zero, and it compares equal to an interval of
    '1 day'), but adding it to a date or timestamp may go forwards or
    backwards, or not move at all, depending on the number of days in the
    month.
    
    Another example, is the interval '361 days', which compares as greater
    than the interval '1 year' (because that's taken to be 12 months of 30
    days each). But adding '361 days' to a date or timestamp advances it
    by less than a year.
    
    So ordering by an interval column can produce different results than
    ordering by a fixed date/timestamp plus the interval.
    
    Regards,
    Dean
    
    
    
    

12. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-26T20:49:57Z

    I know it is less than a week from the end of the commitfest,
    does it make sense to reduce the scope of this to get it in an
    acceptable condition?
    
    #1 GROUP BY
    What motivated me to get this was queries with
    GROUP BY date_trunc(x, timestamp)
    GROUP BY timestamp::date
    I tried to cover as many functions as possible, as I don't know how
    other people might be creatively writing their queries.
    
    #2 JOIN
    There might be some joins that can be made a merge join
    but that is not a big deal, and makes it much more complicated
    happy to drop.
    
    #3 ORDER BY
    This case is a bit pointless, if we are ordering by f(x) we could
    simply order by x and still use f(x) in the query. But is there
    because I use the pathkey to get the grouping correct.
    
    #3 min/max
    This is a nice case where min(f(x)) is computed by an index scan
    with limit 1, but again, one can simply use f(min(x)) to get the same
    plan.
    
    #4 Recursive slope analysis
    While the recursive slope analysis is very elegant I know that
    a lot of eyebrows will raise with a 15% planning time increase.
    So I am happy to restrict this to the queries with GROUP BY
    clause.
    
    #5 Alternative explicit monotonicity
    If you think that this is better, we could add two functions
    increasing(x, f(x)) returns f(x)
    decreasing(x, f(x)) returns f(x)
    at planning time we accept the index paths, and at
    run time we could check that the result does not violate the order
    this could be even more general, e.g.
    
    SELECT increasing(x, abs(x)) WHERE x >= 0
    GROUP BY 1;
    
    SELECT increasing(x, x * y) WHERE y >= 0
    GROUP BY 1;
    
    Do any of these give a good chance to have this in this commitfest
    or should I just chill out?
    
    
    On Thu, Mar 26, 2026 at 6:17 PM Dean Rasheed <dean.a.rasheed@gmail.com>
    wrote:
    
    > On Wed, 25 Mar 2026 at 23:35, Alexandre Felipe
    > <o.alexandre.felipe@gmail.com> wrote:
    > >
    > > Good catch,
    > > Can you think of any other type that would wrap,
    >
    > Arithmetic with intervals isn't monotonic in general because of the
    > way interval time units vary.
    
    
    
    For example, '1 month - 29 days' is "positive" (in the sense that it
    > compares as greater than zero, and it compares equal to an interval of
    > '1 day'), but adding it to a date or timestamp may go forwards or
    > backwards, or not move at all, depending on the number of days in the
    > month.
    
    
    Now that you say it makes sense.
    
    So ordering by an interval column can produce different results than
    > ordering by a fixed date/timestamp plus the interval.
    >
    
    Removing interval arithmetic
    
    Regards,
    Alexandre
    

13. Re: SLOPE - Planner optimizations on monotonic expressions.

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-03-27T08:46:55Z

    On Thu, 26 Mar 2026 at 20:50, Alexandre Felipe
    <o.alexandre.felipe@gmail.com> wrote:
    >
    > I know it is less than a week from the end of the commitfest,
    > does it make sense to reduce the scope of this to get it in an
    > acceptable condition?
    >
    > [snip various options]
    >
    > Do any of these give a good chance to have this in this commitfest
    > or should I just chill out?
    
    I don't know. I haven't looked at the patches themselves in any
    detail. I was merely replying to a specific question about
    monotonicity with respect to a particular data type.
    
    However, my impression from reading this thread is that the patches
    haven't had a great deal of in-depth review yet, there are still a
    number of design ideas that people might wish to explore in more
    detail, it probably needs more careful analysis to verify correctness,
    and more testing. That makes me think that it's not feasible to get
    anything committable in less than a week, and it would be better to
    defer this.
    
    Regards,
    Dean
    
    
    
    

14. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-03-27T09:29:03Z

    On Fri, Mar 27, 2026 at 8:47 AM Dean Rasheed <dean.a.rasheed@gmail.com>
    wrote:
    
    > I don't know. I haven't looked at the patches themselves in any
    > detail. I was merely replying to a specific question about
    > monotonicity with respect to a particular data type.
    >
    > However, my impression from reading this thread is that the patches
    > haven't had a great deal of in-depth review yet, there are still a
    > number of design ideas that people might wish to explore in more
    > detail, it probably needs more careful analysis to verify correctness,
    > and more testing. That makes me think that it's not feasible to get
    > anything committable in less than a week, and it would be better to
    > defer this.
    
    
    Thank you for your feedback, either way I am happy that at least
    now it received some attention.
    
    I am testing an iterative single variable/expression and to find the
    simplest expression x that is the cause of all the variation in the
    pathkey expression, saving this in the plan info, and thus, reduce
    the per index work.
    
    Regards,
    Alexandre
    

15. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-05T21:28:35Z

    Hi All,
    
    I hope I am early enough for PG20, so v6 maintains the full scope.
    
    0001 as the name suggests is just a benchmark, to get a baseline.
    0002 is just a refactoring to ensure build_index_pathkeys is called once
    per index.
    master is calling once to produce forward pathkeys and once to produce
    backward pathkeys.
    
    Other questions are: Should we maybe do this in a way to support
    monotonicity
    for user defined functions too? Maybe use some per argument flags that can
    retrieved without the call by oid?
    
    v6 splits the pathkey monotonicity analysis in two parts.
    
    If the pathkey expression depends on a single table, the innermost
    sub expression that contains all variable terms is extracted during the
    index
    creation, and stored in slope_info.
    When considering indices for pathkeys, check if the index key matches
    the slope info source of variation, if it does, check the monotonicity.
    
    Limitations:
    
    Not supporting pathkeys [f(x), x], maybe useful for
    queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    I have an implementation on a 0006 patch but I think it would hurt the
    overall
    patchset quality.
    
    Not working with joins where I expected a MergeJoin to be used.
    Any hints here? Because I am not properly using equivalence classes? or
    something else?
    
    I see that `make_canonical_pathkey` does a list search for every index key.
    expressions, the complexity is roughly quadratic with the number of indices.
    I suspect that pointer chasing having a preallocated array
    would already be better, as it would probably improve the memory locality.
    Would it be worth investigating other data structures here, like hash or a
    tree?
    (I guess the answer will be no as that could hurt the very simple plans
    with
    a handful of indices).
    
    
    Regards,
    Alexandre
    
    
    
    On Fri, Mar 27, 2026 at 9:29 AM Alexandre Felipe <
    o.alexandre.felipe@gmail.com> wrote:
    
    >
    >
    > On Fri, Mar 27, 2026 at 8:47 AM Dean Rasheed <dean.a.rasheed@gmail.com>
    > wrote:
    >
    >> I don't know. I haven't looked at the patches themselves in any
    >> detail. I was merely replying to a specific question about
    >> monotonicity with respect to a particular data type.
    >>
    >> However, my impression from reading this thread is that the patches
    >> haven't had a great deal of in-depth review yet, there are still a
    >> number of design ideas that people might wish to explore in more
    >> detail, it probably needs more careful analysis to verify correctness,
    >> and more testing. That makes me think that it's not feasible to get
    >> anything committable in less than a week, and it would be better to
    >> defer this.
    >
    >
    > Thank you for your feedback, either way I am happy that at least
    > now it received some attention.
    >
    > I am testing an iterative single variable/expression and to find the
    > simplest expression x that is the cause of all the variation in the
    > pathkey expression, saving this in the plan info, and thus, reduce
    > the per index work.
    >
    > Regards,
    > Alexandre
    >
    >
    >
    

16. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-06T08:29:34Z

    Hi All,
    
    I hope I am early enough for PG20, so v6 maintains the full scope.
    
    0001 as the name suggests is just a benchmark, to get a baseline.
    0002 is just a refactoring to ensure build_index_pathkeys is called once
    per index.
    master is calling once to produce forward pathkeys and once to produce
    backward pathkeys.
    
    Other questions are: Should we maybe do this in a way to support
    monotonicity
    for user defined functions too? Maybe use some per argument flags that can
    retrieved without the call by oid?
    
    v6 splits the pathkey monotonicity analysis in two parts.
    
    If the pathkey expression depends on a single table, the innermost
    sub expression that contains all variable terms is extracted during the
    index
    creation, and stored in slope_info.
    When considering indices for pathkeys, check if the index key matches
    the slope info source of variation, if it does, check the monotonicity.
    
    Limitations:
    
    Not supporting pathkeys [f(x), x], maybe useful for
    queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    I have an implementation on a 0006 patch but I think it would hurt the
    overall
    patchset quality.
    
    Not working with joins where I expected a MergeJoin to be used.
    Any hints here? Because I am not properly using equivalence classes? or
    something else?
    
    I see that `make_canonical_pathkey` does a list search for every index key.
    expressions, the complexity is roughly quadratic with the number of indices.
    I suspect that pointer chasing having a preallocated array
    would already be better, as it would probably improve the memory locality.
    Would it be worth investigating other data structures here, like hash or a
    tree?
    (I guess the answer will be no as that could hurt the very simple plans
    with
    a handful of indices).
    
    
    PS. Only rebasing the previous patch set
    
    Regards,
    Alexandre
    
    On Sun, Apr 5, 2026 at 10:28 PM Alexandre Felipe <
    o.alexandre.felipe@gmail.com> wrote:
    
    > Hi All,
    >
    > I hope I am early enough for PG20, so v6 maintains the full scope.
    >
    > 0001 as the name suggests is just a benchmark, to get a baseline.
    > 0002 is just a refactoring to ensure build_index_pathkeys is called once
    > per index.
    > master is calling once to produce forward pathkeys and once to produce
    > backward pathkeys.
    >
    > Other questions are: Should we maybe do this in a way to support
    > monotonicity
    > for user defined functions too? Maybe use some per argument flags that can
    > retrieved without the call by oid?
    >
    > v6 splits the pathkey monotonicity analysis in two parts.
    >
    > If the pathkey expression depends on a single table, the innermost
    > sub expression that contains all variable terms is extracted during the
    > index
    > creation, and stored in slope_info.
    > When considering indices for pathkeys, check if the index key matches
    > the slope info source of variation, if it does, check the monotonicity.
    >
    > Limitations:
    >
    > Not supporting pathkeys [f(x), x], maybe useful for
    > queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    > I have an implementation on a 0006 patch but I think it would hurt the
    > overall
    > patchset quality.
    >
    > Not working with joins where I expected a MergeJoin to be used.
    > Any hints here? Because I am not properly using equivalence classes? or
    > something else?
    >
    > I see that `make_canonical_pathkey` does a list search for every index key.
    > expressions, the complexity is roughly quadratic with the number of
    > indices.
    > I suspect that pointer chasing having a preallocated array
    > would already be better, as it would probably improve the memory locality.
    > Would it be worth investigating other data structures here, like hash or a
    > tree?
    > (I guess the answer will be no as that could hurt the very simple plans
    > with
    > a handful of indices).
    >
    >
    > Regards,
    > Alexandre
    >
    >
    >
    > On Fri, Mar 27, 2026 at 9:29 AM Alexandre Felipe <
    > o.alexandre.felipe@gmail.com> wrote:
    >
    >>
    >>
    >> On Fri, Mar 27, 2026 at 8:47 AM Dean Rasheed <dean.a.rasheed@gmail.com>
    >> wrote:
    >>
    >>> I don't know. I haven't looked at the patches themselves in any
    >>> detail. I was merely replying to a specific question about
    >>> monotonicity with respect to a particular data type.
    >>>
    >>> However, my impression from reading this thread is that the patches
    >>> haven't had a great deal of in-depth review yet, there are still a
    >>> number of design ideas that people might wish to explore in more
    >>> detail, it probably needs more careful analysis to verify correctness,
    >>> and more testing. That makes me think that it's not feasible to get
    >>> anything committable in less than a week, and it would be better to
    >>> defer this.
    >>
    >>
    >> Thank you for your feedback, either way I am happy that at least
    >> now it received some attention.
    >>
    >> I am testing an iterative single variable/expression and to find the
    >> simplest expression x that is the cause of all the variation in the
    >> pathkey expression, saving this in the plan info, and thus, reduce
    >> the per index work.
    >>
    >> Regards,
    >> Alexandre
    >>
    >>
    >>
    

17. Re: SLOPE - Planner optimizations on monotonic expressions.

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-04-06T23:09:33Z

    I think there's a bug with NULL handling:
    
    CREATE TABLE slope_nulls_test (v int4);
    INSERT INTO slope_nulls_test SELECT generate_series(1, 10);
    INSERT INTO slope_nulls_test VALUES (NULL), (NULL);
    CREATE INDEX slope_nulls_idx ON slope_nulls_test (v ASC NULLS FIRST);
    ANALYZE slope_nulls_test;
    
    SET enable_seqscan = off;
    
    SELECT floor(v::float8), v FROM slope_nulls_test ORDER BY 1;
    
    RESET enable_seqscan;
    SET enable_indexscan = off;
    SELECT floor(v::float8), v FROM slope_nulls_test ORDER BY 1;
    
    
     { oid => '2308', descr => 'nearest integer >= value',
    -  proname => 'ceil', prorettype => 'float8', proargtypes => 'float8',
    -  prosrc => 'dceil' },
    +  proname => 'ceil', prosupport => 'arg0_asc_slope_support',
    +  prorettype => 'float8', proargtypes => 'float8', prosrc => 'dceil' },
     { oid => '2320', descr => 'nearest integer >= value',
       proname => 'ceiling', prorettype => 'float8', proargtypes => 'float8',
       prosrc => 'dceil' },
    
    Shouldn't 2320 also have the same change? Same for 1711/2167
    
     { oid => '183',
    -  proname => 'int42mi', prorettype => 'int4', proargtypes => 'int4 int2',
    -  prosrc => 'int42mi' },
    +  proname => 'int42mi', prosupport => 'diff_slope_support',
    +  prorettype => 'int4', proargtypes => 'int4 int2', prosrc => 'int42mi' },
    
    Similarly shouldn't int24/int42 mul and div also be included?
    
    
    
    

18. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-07T07:50:23Z

    Thank you,
    Valuable feedback
    
    On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    
    I think there's a bug with NULL handling
    
    I was genuinely surprised that this was not caught by any test in the
    regression.
    
    There are 32 cases, next patch will verify both the plan and the output
    FOR r IN
            SELECT idx_dir, idx_nf, qry_dir, qry_nf, sign
            FROM unnest(ARRAY['+','-']) AS sign,
                 unnest(ARRAY['ASC','DESC']) AS idx_dir,
                 unnest(ARRAY['FIRST','LAST']) AS idx_nf,
                 unnest(ARRAY['ASC','DESC']) AS qry_dir,
                 unnest(ARRAY['FIRST','LAST']) AS qry_nf
    
    
     { oid => '2308', descr => 'nearest integer >= value',
    > -  proname => 'ceil', prorettype => 'float8', proargtypes => 'float8',
    > -  prosrc => 'dceil' },
    > +  proname => 'ceil', prosupport => 'arg0_asc_slope_support',
    > +  prorettype => 'float8', proargtypes => 'float8', prosrc => 'dceil' },
    >  { oid => '2320', descr => 'nearest integer >= value',
    >    proname => 'ceiling', prorettype => 'float8', proargtypes => 'float8',
    >    prosrc => 'dceil' },
    >
    > Shouldn't 2320 also have the same change? Same for 1711/2167
    >
    >  { oid => '183',
    > -  proname => 'int42mi', prorettype => 'int4', proargtypes => 'int4 int2',
    > -  prosrc => 'int42mi' },
    > +  proname => 'int42mi', prosupport => 'diff_slope_support',
    > +  prorettype => 'int4', proargtypes => 'int4 int2', prosrc => 'int42mi' },
    >
    > Similarly shouldn't int24/int42 mul and div also be included?
    >
    
    Here I am genuinely NOT surprised :) I will take another look there before
    submitting the next patch.
    
    Regards,
    Alexandre
    

19. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-12T15:01:52Z

    Now including a 0006 patch supporting redundant pathkeys such as
    [f(x), x], or [x, f(x)], motivated by queries like
    queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    
    
    On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    > I think there's a bug with NULL handling
    
    There were too many possibilities for my brain to enumerate, so I scripted
    the
    extraction and presentation of that in regress/sql/slope.sql in commit 0005.
    
     sign |   index_order    |   query_order    | scan_method |  example
    ------+------------------+------------------+-------------+------------
     +    | ASC NULLS FIRST  | ASC NULLS FIRST  | Forward     | NULL,1,2
     +    | ASC NULLS LAST   | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS FIRST | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS LAST  | ASC NULLS FIRST  | Backward    | NULL,1,2
     -    | ASC NULLS FIRST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     -    | ASC NULLS LAST   | ASC NULLS FIRST  | Backward    | NULL,-2,-1
     -    | DESC NULLS FIRST | ASC NULLS FIRST  | Forward     | NULL,-2,-1
     -    | DESC NULLS LAST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     +    | ASC NULLS FIRST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     +    | ASC NULLS LAST   | ASC NULLS LAST   | Forward     | 1,2,NULL
     +    | DESC NULLS FIRST | ASC NULLS LAST   | Backward    | 1,2,NULL
     +    | DESC NULLS LAST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     -    | ASC NULLS FIRST  | ASC NULLS LAST   | Backward    | -2,-1,NULL
     -    | ASC NULLS LAST   | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS FIRST | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS LAST  | ASC NULLS LAST   | Forward     | -2,-1,NULL
     +    | ASC NULLS FIRST  | DESC NULLS FIRST | Sort        | NULL,2,1
     +    | ASC NULLS LAST   | DESC NULLS FIRST | Backward    | NULL,2,1
     +    | DESC NULLS FIRST | DESC NULLS FIRST | Forward     | NULL,2,1
     +    | DESC NULLS LAST  | DESC NULLS FIRST | Sort        | NULL,2,1
     -    | ASC NULLS FIRST  | DESC NULLS FIRST | Forward     | NULL,-1,-2
     -    | ASC NULLS LAST   | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS FIRST | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS LAST  | DESC NULLS FIRST | Backward    | NULL,-1,-2
     +    | ASC NULLS FIRST  | DESC NULLS LAST  | Backward    | 2,1,NULL
     +    | ASC NULLS LAST   | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS FIRST | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS LAST  | DESC NULLS LAST  | Forward     | 2,1,NULL
     -    | ASC NULLS FIRST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
     -    | ASC NULLS LAST   | DESC NULLS LAST  | Forward     | -1,-2,NULL
     -    | DESC NULLS FIRST | DESC NULLS LAST  | Backward    | -1,-2,NULL
     -    | DESC NULLS LAST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
    
    I hope this covers everything (I hope nobody minds the lines starting with
    " +" and " -")
    
    >  Shouldn't 2320 also have the same change? Same for 1711/2167
    Fixed that, and probably a bunch of others, included the math functions
    that were in v5
    and added regress/sql/slope_catalog.sql to format the results, this serves
    a double purpose
    (a) check that what we wrote had de desired effect (b) facilitate the
    review by providing
    a more readable format.
    
    There you will find 4 tables
    -- Operators with slope prosupport
     oid  | operator | left_type | right_type | prosupport
    ...
    
    -- Functions (non-operator) with slope prosupport
     oid  |   function   |   arguments | returns | prosupport
    
    -- Operators whose name has slope support for some types but not others
    e.g.
    
     oid  | operator |          left_type          |         right_type
    
    ------+----------+-----------------------------+-----------------------------
     4394 | *        | anymultirange               | anymultirange
     3900 | *        | anyrange                    | anyrange
    
    
    -- Functions whose name has slope support for some signatures but not others
      oid  |   function   |              arguments               |
    returns
    ------+--------------+--------------------------------------+-----------------------------
     1218 | date_trunc   | text, interval                       | interval
     1736 | log          | numeric, numeric                     | numeric
     1961 | timestamp    | timestamp without time zone, integer | timestamp
    without time zone
     1967 | timestamptz  | timestamp with time zone, integer    | timestamp
    with time zone
     1778 | to_timestamp | text, text                           | timestamp
    with time zone
      753 | trunc        | macaddr                              | macaddr
     4112 | trunc        | macaddr8                             | macaddr8
    
    
    Regards,
    Alexandre
    

20. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-13T06:22:22Z

    Hi,
    
    Fixing Meta ordering issue :), the table reporting orders was ordered in
    different ways on
    on different systems, specifying collation on the results table.
    
    On Sun, Apr 12, 2026 at 4:01 PM Alexandre Felipe <
    o.alexandre.felipe@gmail.com> wrote:
    
    Now including a 0006 patch supporting redundant pathkeys such as
    [f(x), x], or [x, f(x)], motivated by queries like
    queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    
    
    On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    > I think there's a bug with NULL handling
    
    There were too many possibilities for my brain to enumerate, so I scripted
    the
    extraction and presentation of that in regress/sql/slope.sql in commit 0005.
    
     sign |   index_order    |   query_order    | scan_method |  example
    ------+------------------+------------------+-------------+------------
     +    | ASC NULLS FIRST  | ASC NULLS FIRST  | Forward     | NULL,1,2
     +    | ASC NULLS LAST   | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS FIRST | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS LAST  | ASC NULLS FIRST  | Backward    | NULL,1,2
     -    | ASC NULLS FIRST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     -    | ASC NULLS LAST   | ASC NULLS FIRST  | Backward    | NULL,-2,-1
     -    | DESC NULLS FIRST | ASC NULLS FIRST  | Forward     | NULL,-2,-1
     -    | DESC NULLS LAST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     +    | ASC NULLS FIRST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     +    | ASC NULLS LAST   | ASC NULLS LAST   | Forward     | 1,2,NULL
     +    | DESC NULLS FIRST | ASC NULLS LAST   | Backward    | 1,2,NULL
     +    | DESC NULLS LAST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     -    | ASC NULLS FIRST  | ASC NULLS LAST   | Backward    | -2,-1,NULL
     -    | ASC NULLS LAST   | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS FIRST | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS LAST  | ASC NULLS LAST   | Forward     | -2,-1,NULL
     +    | ASC NULLS FIRST  | DESC NULLS FIRST | Sort        | NULL,2,1
     +    | ASC NULLS LAST   | DESC NULLS FIRST | Backward    | NULL,2,1
     +    | DESC NULLS FIRST | DESC NULLS FIRST | Forward     | NULL,2,1
     +    | DESC NULLS LAST  | DESC NULLS FIRST | Sort        | NULL,2,1
     -    | ASC NULLS FIRST  | DESC NULLS FIRST | Forward     | NULL,-1,-2
     -    | ASC NULLS LAST   | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS FIRST | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS LAST  | DESC NULLS FIRST | Backward    | NULL,-1,-2
     +    | ASC NULLS FIRST  | DESC NULLS LAST  | Backward    | 2,1,NULL
     +    | ASC NULLS LAST   | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS FIRST | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS LAST  | DESC NULLS LAST  | Forward     | 2,1,NULL
     -    | ASC NULLS FIRST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
     -    | ASC NULLS LAST   | DESC NULLS LAST  | Forward     | -1,-2,NULL
     -    | DESC NULLS FIRST | DESC NULLS LAST  | Backward    | -1,-2,NULL
     -    | DESC NULLS LAST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
    
    I hope this covers everything (I hope nobody minds the lines starting with
    " +" and " -")
    
    >  Shouldn't 2320 also have the same change? Same for 1711/2167
    Fixed that, and probably a bunch of others, included the math functions
    that were in v5
    and added regress/sql/slope_catalog.sql to format the results, this serves
    a double purpose
    (a) check that what we wrote had de desired effect (b) facilitate the
    review by providing
    a more readable format.
    
    There you will find 4 tables
    -- Operators with slope prosupport
     oid  | operator | left_type | right_type | prosupport
    ...
    
    -- Functions (non-operator) with slope prosupport
     oid  |   function   |   arguments | returns | prosupport
    
    -- Operators whose name has slope support for some types but not others
    e.g.
    
     oid  | operator |          left_type          |         right_type
    
    ------+----------+-----------------------------+------------
    -----------------
     4394 | *        | anymultirange               | anymultirange
     3900 | *        | anyrange                    | anyrange
    
    
    -- Functions whose name has slope support for some signatures but not others
      oid  |   function   |              arguments               |
    returns
    ------+--------------+--------------------------------------
    +-----------------------------
     1218 | date_trunc   | text, interval                       | interval
     1736 | log          | numeric, numeric                     | numeric
     1961 | timestamp    | timestamp without time zone, integer | timestamp
    without time zone
     1967 | timestamptz  | timestamp with time zone, integer    | timestamp
    with time zone
     1778 | to_timestamp | text, text                           | timestamp
    with time zone
      753 | trunc        | macaddr                              | macaddr
     4112 | trunc        | macaddr8                             | macaddr8
    
    
    Regards,
    Alexandre
    

21. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-04-13T16:38:50Z

    Fixing Meta ordering issue :), the table reporting orders was ordered in
    different ways on
    on different systems, specifying collation on the results table.
    
    On Sun, Apr 12, 2026 at 4:01 PM Alexandre Felipe <
    o.alexandre.felipe@gmail.com> wrote:
    
    Now including a 0006 patch supporting redundant pathkeys such as
    [f(x), x], or [x, f(x)], motivated by queries like
    queries SELECT OVER (PARTITION  f(x) ORDER BY x)
    
    
    On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    > I think there's a bug with NULL handling
    
    There were too many possibilities for my brain to enumerate, so I scripted
    the
    extraction and presentation of that in regress/sql/slope.sql in commit 0005.
    
     sign |   index_order    |   query_order    | scan_method |  example
    ------+------------------+------------------+-------------+------------
     +    | ASC NULLS FIRST  | ASC NULLS FIRST  | Forward     | NULL,1,2
     +    | ASC NULLS LAST   | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS FIRST | ASC NULLS FIRST  | Sort        | NULL,1,2
     +    | DESC NULLS LAST  | ASC NULLS FIRST  | Backward    | NULL,1,2
     -    | ASC NULLS FIRST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     -    | ASC NULLS LAST   | ASC NULLS FIRST  | Backward    | NULL,-2,-1
     -    | DESC NULLS FIRST | ASC NULLS FIRST  | Forward     | NULL,-2,-1
     -    | DESC NULLS LAST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
     +    | ASC NULLS FIRST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     +    | ASC NULLS LAST   | ASC NULLS LAST   | Forward     | 1,2,NULL
     +    | DESC NULLS FIRST | ASC NULLS LAST   | Backward    | 1,2,NULL
     +    | DESC NULLS LAST  | ASC NULLS LAST   | Sort        | 1,2,NULL
     -    | ASC NULLS FIRST  | ASC NULLS LAST   | Backward    | -2,-1,NULL
     -    | ASC NULLS LAST   | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS FIRST | ASC NULLS LAST   | Sort        | -2,-1,NULL
     -    | DESC NULLS LAST  | ASC NULLS LAST   | Forward     | -2,-1,NULL
     +    | ASC NULLS FIRST  | DESC NULLS FIRST | Sort        | NULL,2,1
     +    | ASC NULLS LAST   | DESC NULLS FIRST | Backward    | NULL,2,1
     +    | DESC NULLS FIRST | DESC NULLS FIRST | Forward     | NULL,2,1
     +    | DESC NULLS LAST  | DESC NULLS FIRST | Sort        | NULL,2,1
     -    | ASC NULLS FIRST  | DESC NULLS FIRST | Forward     | NULL,-1,-2
     -    | ASC NULLS LAST   | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS FIRST | DESC NULLS FIRST | Sort        | NULL,-1,-2
     -    | DESC NULLS LAST  | DESC NULLS FIRST | Backward    | NULL,-1,-2
     +    | ASC NULLS FIRST  | DESC NULLS LAST  | Backward    | 2,1,NULL
     +    | ASC NULLS LAST   | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS FIRST | DESC NULLS LAST  | Sort        | 2,1,NULL
     +    | DESC NULLS LAST  | DESC NULLS LAST  | Forward     | 2,1,NULL
     -    | ASC NULLS FIRST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
     -    | ASC NULLS LAST   | DESC NULLS LAST  | Forward     | -1,-2,NULL
     -    | DESC NULLS FIRST | DESC NULLS LAST  | Backward    | -1,-2,NULL
     -    | DESC NULLS LAST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
    
    I hope this covers everything (I hope nobody minds the lines starting with
    " +" and " -")
    
    >  Shouldn't 2320 also have the same change? Same for 1711/2167
    Fixed that, and probably a bunch of others, included the math functions
    that were in v5
    and added regress/sql/slope_catalog.sql to format the results, this serves
    a double purpose
    (a) check that what we wrote had de desired effect (b) facilitate the
    review by providing
    a more readable format.
    
    There you will find 4 tables
    -- Operators with slope prosupport
     oid  | operator | left_type | right_type | prosupport
    ...
    
    -- Functions (non-operator) with slope prosupport
     oid  |   function   |   arguments | returns | prosupport
    
    -- Operators whose name has slope support for some types but not others
    e.g.
    
     oid  | operator |          left_type          |         right_type
    
    ------+----------+-----------------------------+------------
    -----------------
     4394 | *        | anymultirange               | anymultirange
     3900 | *        | anyrange                    | anyrange
    
    
    -- Functions whose name has slope support for some signatures but not others
      oid  |   function   |              arguments               |
    returns
    ------+--------------+--------------------------------------
    +-----------------------------
     1218 | date_trunc   | text, interval                       | interval
     1736 | log          | numeric, numeric                     | numeric
     1961 | timestamp    | timestamp without time zone, integer | timestamp
    without time zone
     1967 | timestamptz  | timestamp with time zone, integer    | timestamp
    with time zone
     1778 | to_timestamp | text, text                           | timestamp
    with time zone
      753 | trunc        | macaddr                              | macaddr
     4112 | trunc        | macaddr8                             | macaddr8
    
    
    Regards,
    Alexandre
    

22. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-05-07T19:12:25Z

    On Wed, Mar 25, 2026 at 5:18 AM Corey Huinker <corey.huinker@gmail.com>
    wrote:
    > 0002 is missing the catversion bump but that's fine at this early stage.
    
    postponing catversion bump for a time when we get closer to commit,
    v8 doesn't include catversion.h changes, hoping to require less frequent
    rebases.
    

23. Re: SLOPE - Planner optimizations on monotonic expressions.

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-05-08T22:18:55Z

    > There were too many possibilities for my brain to enumerate, so I scripted
    > the
    > extraction and presentation of that in regress/sql/slope.sql in commit 0005.
    
    Apologies, I forgot to reply to this earlier, the new NULL handling
    looks good. Nice test!
    
    I found one more corner case with infinities (same applies also with
    negative infinity):
    
    CREATE TABLE t8 (x float8);
    INSERT INTO t8 VALUES (-2), (-1), (0), (1), (2);
    CREATE INDEX t8_x_idx ON t8 (x);
    ANALYZE t8;
    
    SET enable_seqscan = off;
    SELECT x, x * 'Infinity'::float8 AS f FROM t8 ORDER BY x * 'Infinity'::float8;
    
    SET enable_indexscan = off;
    SET enable_indexonlyscan = off;
    SET enable_seqscan = on;
    SELECT x, x * 'Infinity'::float8 AS f FROM t8 ORDER BY x * 'Infinity'::float8;
    
    
    
    

24. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-05-10T15:53:49Z

    On Fri, May 8, 2026 at 11:19 PM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    
    > I found one more corner case with infinities (same applies also with
    > negative infinity):
    >
    
    This will restrict a lot of cases.
    
    
    slope_corner_cases.sql enumerate experssions and orders producing
    permutations of (-inf, -1, 0, 1, +inf, nan) mapped to (1,2,3,4,5,6)
    to visualize other similar corner cases.
    
    Apparently the violations boil down to two cases
    * All basic arithmetic operations with infinity constant (with a lucky
    exception x - inf)
    * Every decreasing function where the index key.
    e.g. `-x desc` would have NaNs first
    
    Should I simply detect and disable the above cases?
    
    sqrt(x < 0) already raise an exception, is it safe to assume that for all
    the limited domain functions?
    

25. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-06-01T13:07:47Z

    This patch set adds 0007 "FIX NaN special cases"
    filter the index pathkeys that could produce NaN values out of order
    
    By special values I mean the non-real numeric values +Infinity, -Infinity,
    NaN.
    
    Cases considered.
    (1) Input type supports special values, and functions are decreasing.
    (2) Adding or subtracting infinity to a variable of a type that supports
    special values.
    (3) Multiplication by infinity, as the 0 * Infinity is out of order.
    (4) Dividing by infinity a variable that supports special values.
    
    The case (1) is treated when building the pathkey to handle properly cases
    like (1 - (1 - x)) where the expression is increased but depends on some
    decreasing sub expression.
    
    
    Regards,
    Alexandre
    
    
    
    On Sun, May 10, 2026 at 4:53 PM Alexandre Felipe <
    o.alexandre.felipe@gmail.com> wrote:
    
    >
    > On Fri, May 8, 2026 at 11:19 PM Zsolt Parragi <zsolt.parragi@percona.com>
    > wrote:
    >
    >> I found one more corner case with infinities (same applies also with
    >> negative infinity):
    >>
    >
    > This will restrict a lot of cases.
    >
    >
    > slope_corner_cases.sql enumerate experssions and orders producing
    > permutations of (-inf, -1, 0, 1, +inf, nan) mapped to (1,2,3,4,5,6)
    > to visualize other similar corner cases.
    >
    > Apparently the violations boil down to two cases
    > * All basic arithmetic operations with infinity constant (with a lucky
    > exception x - inf)
    > * Every decreasing function where the index key.
    > e.g. `-x desc` would have NaNs first
    >
    > Should I simply detect and disable the above cases?
    >
    > sqrt(x < 0) already raise an exception, is it safe to assume that for all
    > the limited domain functions?
    >
    >
    >
    >
    

26. Re: SLOPE - Planner optimizations on monotonic expressions.

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-07-01T22:00:48Z

    Apologies for the late response, somehow I missed this update.
    
    The changes generally look good and seem to work, except one remaining
    issue with domains:
    
    CREATE DOMAIN df AS float8;
    CREATE TABLE t (id int, x df);
    INSERT INTO t VALUES (1,'-inf'),(2,'-1'),(3,'0'),(4,'3'),(5,'inf'),(6,'nan');
    CREATE INDEX ON t (x);
    SET enable_seqscan=off; SET enable_sort=off; SET enable_bitmapscan=off;
    SELECT id, -x AS f FROM t  ORDER BY (-x) ASC NULLS FIRST;
    
    
    
     /*
      * get_const_sign
      *		Helper to determine the sign of a numeric constant.
      *		Returns 1 for positive, -1 for negative, 0 for zero or unknown.
      */
    -static int
    +static inline enum NUMERIC_SIGN
    
    This comment is now outdated. The default value is unreachable
    currently, but is 0 still a safe value there?
    
    
    +enum NUMERIC_SIGN {
    +	NUMERIC_SIGN_NINF=-2,
    +	NUMERIC_SIGN_NEG=-1,
    +	NUMERIC_SIGN_ZERO=0,
    +	NUMERIC_SIGN_POS=1,
    +	NUMERIC_SIGN_PINF=3,
    +	NUMERIC_SIGN_NAN=4,
    +	NUMERIC_SIGN_NULL=5,
    +};
    
    this is missing a typedef and maybe it should be defined at the
    beginning of the file or in a header? Is skipping positive 2
    intentional?
    
    The patchset also has many small formatting issues/inconsistencies,
    maybe it would be worth to run pgindent on it?
    
    
    
    

27. Re: SLOPE - Planner optimizations on monotonic expressions.

    Alexandre Felipe <o.alexandre.felipe@gmail.com> — 2026-07-03T10:02:30Z

    On Wed, Jul 1, 2026 at 11:00 PM Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    
    > Apologies for the late response, somehow I missed this update.
    >
    
    Thank you for your late response :D
    
    
    > The changes generally look good and seem to work, except one remaining
    > issue with domains:
    
    
    I didn't know about domains before, tempting
    e.g. cos(pi * x) decreasing for x between 0 and 1.
    But I will avoid that rabbit hole.
    
    The solution I implemented was using getBaseType
    ./src/backend/utils/cache/lsyscache.c:2754:getBaseType(Oid typid)
    
    + if(var_type >= FirstNormalObjectId)
    + var_type = getBaseType(var_type);
    
     /*
    >   * get_const_sign
    >   *             Helper to determine the sign of a numeric constant.
    >   *             Returns 1 for positive, -1 for negative, 0 for zero or
    > unknown.
    >   */
    > -static int
    > +static inline enum NUMERIC_SIGN
    >
    > This comment is now outdated. The default value is unreachable
    > currently, but is 0 still a safe value there?
    >
    
    Updated, actually NUMERIC_SIGN_ZERO indicates that the value
    is strictly equals to 0, I don't see how it would be unsafe.
    
    
    > +enum NUMERIC_SIGN {
    > +       NUMERIC_SIGN_NINF=-2,
    > +       NUMERIC_SIGN_NEG=-1,
    > +       NUMERIC_SIGN_ZERO=0,
    > +       NUMERIC_SIGN_POS=1,
    > +       NUMERIC_SIGN_PINF=3,
    > +       NUMERIC_SIGN_NAN=4,
    > +       NUMERIC_SIGN_NULL=5,
    > +};
    >
    > this is missing a typedef and maybe it should be defined at the
    > beginning of the file or in a header? Is skipping positive 2
    > intentional?
    >
    
    OK, moved to the top with a typedef, the 2 gap was not intentional,
    I think when I wrote it I had NaN there before +Infinity, but later
    I realised that NaN goes after +Infinity.
    
    
    > The patchset also has many small formatting issues/inconsistencies,
    > maybe it would be worth to run pgindent on it?
    >
    
    I ran it on src/backend/utils/adt/misc.c
    
    It kept changing
    /*
     * arg0_asc_slope_support
     *      Prosupport: f(x, ...) is monotonically increasing in x.
     */
    to
    /*
     * arg0_asc_slope_support Prosupport: f(x, ...) is monotonically increasing
     * in x.
     */
    
    Is there a written standard/recommendation for these, maybe
    /*
     * arg0_asc_slope_support
     *
     * Prosupport: f(x, ...) is monotonically increasing in x.
     */
    
    The last part of the slope test is checking 480 corner cases, in my machine
    it takes 240ms while some other tests take 1000+ so I guess it is not a
    problem.
    
    
    Regards,
    Alexandre
    

28. Re: SLOPE - Planner optimizations on monotonic expressions.

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-07-03T20:21:37Z

    Thanks for the updated version, I didn't find more correctness issues
    with the patch in v9.
    
    > Is there a written standard/recommendation for these, maybe
    > /*
    > * arg0_asc_slope_support
    > *
    > * Prosupport: f(x, ...) is monotonically increasing in x.
    > */
    
    I think most of the function comment's don't mention the function name
    at all, but those that do follow this approach with a blank line.
    At some places I also see a
    
    /* functionname: some description
     *
     * more description
     */
    
    pattern.
    
    
     CREATE OR REPLACE FUNCTION log10(numeric)
      RETURNS numeric
      LANGUAGE sql
    + SUPPORT arg0_asc_slope_support
      IMMUTABLE PARALLEL SAFE STRICT COST 1
     RETURN log(10, $1);
    
    Shouldn't these additions, and generally the feature itself / how it
    works / what's the contract for functions / ... have proper
    documentation as it is user/extension facing?
    
    Another similar question is if the feature should have an enable_
    toggle similar to others, enable_slope?