Re: SLOPE - Planner optimizations on monotonic expressions.

- if (enable_slope && index->rel->reloptkind == RELOPT_BASEREL)
+ if (enable_slope)
{
...
- if (cpathkey)
- {
- /*
...

Isn't that cpathkey if/else needed in the !enable_slope case?

CREATE TABLE t (a int);
INSERT INTO t SELECT g FROM generate_series(1, 10000) g;
CREATE INDEX ON t (a);
ANALYZE t;

CREATE TABLE u (a int);
INSERT INTO u SELECT g FROM generate_series(1, 10000) g;
CREATE INDEX ON u (a);
ANALYZE u;

SET enable_seqscan = off;
SET enable_bitmapscan = off;

SET enable_slope = off;
EXPLAIN (COSTS OFF) SELECT a FROM t ORDER BY a LIMIT 5;

SET enable_hashjoin = off;
SET enable_nestloop = off;
EXPLAIN (COSTS OFF) SELECT * FROM t JOIN u USING (a);

I also realized one more timestamp issue: timestamptz -> timestamp /
date is not monotonic at a DST change:

SET TimeZone = 'America/New_York';
CREATE TABLE tsdst (x timestamptz);
-- in UTC these are ascending, in EDT->ESC descending
INSERT INTO tsdst VALUES ('2023-11-05 05:59:00+00'), ('2023-11-05 06:00:00+00');
CREATE INDEX ON tsdst (x);
ANALYZE tsdst;

SET enable_seqscan = off;
SET enable_bitmapscan = off;

SELECT x, x::timestamp AS local_ts FROM tsdst ORDER BY x::timestamp;
SET enable_slope = off;
SELECT x, x::timestamp AS local_ts FROM tsdst ORDER BY x::timestamp;

> Additionally,
> 0003 lets the planner ignore NULLS FIRST or NULLS LAST in a pathkey
> if the target expression is known to be NOT NULL. This change is is
> independent of SLOPE implementation.

This patch has an issue in the null handling:

CREATE TABLE l (a int); -- one join column can be NULL
CREATE TABLE r (b int NOT NULL); -- other join column NOT NULL
CREATE INDEX ON l (a);
INSERT INTO l VALUES (1), (2), (NULL);
INSERT INTO r VALUES (1), (2);

SELECT l.a FROM l LEFT JOIN r ON l.a = r.b ORDER BY l.a NULLS FIRST;

+ <para>
+ <varname>MONOTONICFUNC_INCREASING</varname>: <literal>x1 > x2</literal>
+ implies <literal>f(x1) >= f(x2)</literal>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>MONOTONICFUNC_DECREASING</varname>: <literal>x1 > x2</literal>
+ implies <literal>f(x1) >= f(x2)</literal>
+ </para>

Decreasing should be <= ?

+ <listitem>
+ <para>
+ <varname>MONOTONICFUNC_BOTH</varname>: both
+ <varname>MONOTONICFUNC_DECREASING</varname> and
+ <varname>MONOTONICFUNC_DECREASING</varname>, so
+ <literal>f(x1) = f(x2)</literal> for any valid
+ <literal>x1</literal> and <literal>x1</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>MONOTONICFUNC_BOTH</varname>: the order of
<literal>f(x)</literal>
+ cannot be infeered from the order of <literal>x</literal>
+ </para>
+ </listitem>

DECREASING and BOTH duplicated, both seem to be mistakes.

+CREATE TABLE (created_at timestamp, ...);
+CREATE INDEX ON orders(created_at);

CREATE TABLE orders?