Re: SLOPE - Planner optimizations on monotonic expressions.

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