Re: SLOPE - Planner optimizations on monotonic expressions.
Alexandre Felipe <o.alexandre.felipe@gmail.com>
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
To: Zsolt Parragi <zsolt.parragi@percona.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>,
Corey Huinker <corey.huinker@gmail.com>, pgsql-hackers@postgresql.org
Date: 2026-04-13T06:22:22Z
Lists: pgsql-hackers
Attachments
- v7-0004-SLOPE-catalog-changes.patch (application/octet-stream) patch v7-0004
- v7-0001-benchmark.patch (application/octet-stream) patch v7-0001
- v7-0003-SLOPE-prosupport-definitions.patch (application/octet-stream) patch v7-0003
- v7-0005-SLOPE-Planner-support.patch (application/octet-stream) patch v7-0005
- v7-0002-Optimized-reverse-pathkeys.patch (application/octet-stream) patch v7-0002
- v7-0006-SLOPE-redundancy-checks.patch (application/octet-stream) patch v7-0006
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