Re: SLOPE - Planner optimizations on monotonic expressions. - Mailing list pgsql-hackers

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,