Re: SLOPE - Planner optimizations on monotonic expressions.

Hello

> Removed prosupport for timestamptz <-> timestamp.

This is not fixed, same testcase still fails. Maybe because casts are
skipped unconditionally?

+ if ((fexpr->funcformat == COERCE_IMPLICIT_CAST ||
+ fexpr->funcformat == COERCE_EXPLICIT_CAST) &&
+ list_length(fexpr->args) == 1)
+ {
+ expr = (Expr *) linitial(fexpr->args);
+ continue;
+ }

And it seems like we have more issues with casts, there are some more
cases: timestamptz -> date (I mentioned this in my previous email),
timestamp -> time, int4 -> bool (-1 -> true, 0 -> false, 1 -> true)

I rechecked another old issue, and realized that this was dropped
somewhere around v7, was that intentional?
> v5 aims to prevent the elimination of the sort node if the index has a
> custom sort operator family.

CREATE SCHEMA s;
CREATE FUNCTION s.revcmp(int4,int4) RETURNS int4 IMMUTABLE STRICT LANGUAGE sql
AS $$ SELECT btint4cmp($2, $1) $$;
CREATE OPERATOR CLASS s.int4_reverse_ops FOR TYPE int4 USING btree AS
OPERATOR 1 >, OPERATOR 2 >=, OPERATOR 3 =, OPERATOR 4 <=, OPERATOR 5 <,
FUNCTION 1 s.revcmp(int4,int4);

CREATE TABLE t (x int4);
INSERT INTO t VALUES (1),(2),(3),(4),(5),(6);
CREATE INDEX t_rev ON t (x s.int4_reverse_ops);
ANALYZE t;

SET enable_sort = off;
SELECT x + 1 FROM t ORDER BY x + 1;
SET enable_sort = on; -- or enable_slope = off
SELECT x + 1 FROM t ORDER BY x + 1;

I found one more problem with binary coercible types, the skip there
is too generic, it should be more restrictive (btree opfamilies
maybe?):

+ /* Skip RelabelType (no-op coercion) */
+ if (IsA(expr, RelabelType))
+ {
+ expr = (Expr *) ((RelabelType *) expr)->arg;
+ continue;
+ }

CREATE TABLE t5(x int4);
INSERT INTO t5 VALUES (-1),(1),(-2),(2);
CREATE INDEX ON t5(x);
ANALYZE t5;
SET enable_seqscan=off; SET enable_bitmapscan=off;
SET enable_sort=off; SET enable_slope=on;
SELECT x::oid FROM t5 ORDER BY x::oid; -- oid is unsigned
SET enable_slope=off; SET enable_sort=on;
SELECT x::oid FROM t5 ORDER BY x::oid;

+ /*
+ * Case 2: f(x) after x — ascending chain. x is already
+ * in retval, so within each group of equal x values, f(x)
+ * is constant (for any deterministic f). The pathkey is
+ * redundant as a tiebreaker regardless of monotonicity.
+ */
+ if (!pathkey_is_redundant(qpk, retval))
+ retval = lappend(retval, qpk);
+ continue;

"within each group of equal x values, f(x) is constant (for any
deterministic f)" -- doesn't this also require equalimage?

DROP TABLE IF EXISTS t6;
CREATE TABLE t6(x numeric);
INSERT INTO t6 VALUES (1.00),(1.0),(2);
CREATE INDEX ON t6(x);
ANALYZE t6;
SET enable_seqscan=off; SET enable_bitmapscan=off;
SET enable_sort=off; SET enable_slope=on;
SELECT x, scale(x) FROM t6 ORDER BY x, scale(x);
SET enable_slope=off; SET enable_sort=on;
SELECT x, scale(x) FROM t6 ORDER BY x, scale(x);

There are also a few typos:

+ Enables or disable query planner's slope analysis. The slope analysis
+ gives the query executor the ability to use an index when the data
+ order is implied but not direct. Consider the following example</para>
+

disables

+ The table has an index on <varname>created_at</varname> of
+ type <type>timestamp</type>. Grouping take advantage of the order on
+ timestamp, as a data series ordered by timestamp will cluster

takes

+ <varname>MONOTONICFUNC_NONE</varname>: the order of
<literal>f(x)</literal>
+ cannot be infeered from the order of <literal>x</literal>

inferred

+ <literal>f(x1) = f(x2)</literal> for any valid
+ <literal>x1</literal> and <literal>x1</literal>.

x1 and x2

+ * 'nslopes' points to a MonotonicFunction array (one per argument up to
+ * nslopes). Arguments beyond nslopes are treated as MONOTONICFUNC_NONE.

slopes points to

+
+typedef enum NUMERIC_SIGN
+{

Also nitpick, but numeric.c has a define with the same name. There's
no conflict as it's in a different file and that's a macro, but could
make searching more difficult.