SLOPE - Planner optimizations on monotonic expressions.

First seen: 2026-02-11 21:02:53+00:00 · Messages: 25 · Participants: 4

Latest Update

2026-06-04 · claude-opus-4-6

SLOPE: Planner Optimizations on Monotonic Expressions — May 2026 Summary

Overview

The SLOPE patch enables PostgreSQL's planner to recognize that monotonic transformations of indexed columns preserve sort order, eliminating unnecessary Sort nodes and enabling streaming GroupAggregate and MinMax rewrites. May 2026 saw limited but high-impact activity: closure of one correctness issue (NULL handling) immediately followed by discovery of a deeper correctness problem (IEEE-754 floats), which forced a reconceptualization of the safety model for slope tagging.

Key Developments

NULL Handling Validated (May 8)

Zsolt Parragi confirmed that Alexandre's exhaustive 32-case NULLS FIRST/LAST test grid and the associated fix are correct, closing out the NULL-placement correctness pitfall from April.

IEEE-754 Infinity/NaN Breaks Float Monotonicity (May 8)

Zsolt demonstrated that x * 'Infinity'::float8 violates monotonicity because 0 * Inf = NaN, and PostgreSQL sorts NaN last regardless of sort direction. An index scan on x emits the NaN-producing row in the middle of the stream, while a Sort-based plan places it at the end — a plan-dependent result difference (correctness bug).

This generalizes: any float multiplication/division where one operand is ±Inf can manufacture NaN from a finite input, breaking the ordering guarantee.

Empirical Corner-Case Enumeration (May 10)

Alexandre responded with slope_corner_cases.sql, an exhaustive regression test mapping six sentinel float values (-inf, -1, 0, 1, +inf, nan) through all relevant expression/direction combinations. The violations collapse to two families:

  1. Arithmetic with an infinite constant — addition, multiplication, and division with ±Inf produce NaN from finite inputs. Exception: x - Inf is safe (NaN only arises from already-infinite input).

  2. Decreasing functions on NaN-bearing types — PostgreSQL fixes NaN placement as "last under ASC" regardless of sort direction. A descending slope would need NaN first to match reverse-index traversal, but the sort operator cannot be made to cooperate. This means the reverse_sort/nulls_first toggle logic is insufficient for float types even for mathematically monotonic functions like unary minus.

Emerging Design Principle

A pattern is now clear across three independent correctness failures (intervals, Inf/NaN, decreasing-on-NaN-types): slope tagging is unsafe wherever the type's comparison semantics are not a strict homomorphism of the operator's arithmetic. The "tag by pg_proc OID alone" model is demonstrably insufficient; value-level guards (inspecting constant operands at plan time) and type-level guards (detecting non-NULL sentinels with fixed placement) are prerequisites for correctness.

Proposed Resolution

Alexandre proposes surgical disablement: suppress the slope optimization when (a) a float arithmetic op has a non-finite constant operand, or (b) the composed slope is SLOPE_DESC on a NaN-bearing type. This preserves the common cases (ts * 1000, x + 1.0) while excluding the pathological ones.

Partial-Domain Functions

Alexandre raised whether domain-restricted functions like sqrt(x) (which raises an error for x < 0 rather than returning NaN) can be safely tagged monotonic. The reasoning parallels integer overflow: an error terminates the query rather than producing misordered results. This likely generalizes to ln, log, acos, etc., but requires case-by-case audit since some float wrappers historically return NaN rather than erroring.

Status

The patch targets PG20. No committer engaged in May. The correctness model is maturing — from naive per-function tagging toward a layered system with function-level, value-level, and type-level guards — but the implementation has not yet caught up to the conceptual framework. The value-dependent slope inspection pattern (needed for both left(str, n) and float Inf guards) is increasingly a prerequisite rather than a follow-up.

History (1 prior analysis)
2026-06-04 · claude-opus-4-6

New in this round (single message from Alexandre Felipe, 2026-06-01)

Alexandre posts patch v8 (or at minimum a new sub-patch 0007) titled "FIX NaN special cases" that implements the conservative-exclusion strategy he proposed on 2026-05-10, now concretized into code.

What the patch does

The new 0007 patch filters out index pathkeys that could produce NaN values out of order. It operationalizes the two violation families Alexandre identified empirically:

Four guarded cases:

  1. Decreasing functions on types with special values (NaN) — Case (1). This is the "family 2" from the prior analysis: since PostgreSQL fixes NaN-last regardless of sort direction, a SLOPE_DESC composition on a float-bearing type cannot be safely satisfied by reversing the index scan. The patch disables the optimization at pathkey-building time. Importantly, Alexandre notes this is applied recursively during slope composition — so an expression like 1 - (1 - x) (which is mathematically increasing but has an intermediate decreasing sub-expression) is correctly handled: the inner 1 - x triggers the NaN guard at its level, even though the overall composed slope is ASC.

  2. Adding or subtracting infinity to/from a variable of a special-value type — Case (2). Blocks x + Inf, x - (-Inf) etc. where the constant is non-finite.

  3. Multiplication by infinity — Case (3). Blocks because 0 * Inf = NaN breaks ordering for the finite input 0.

  4. Division by infinity — Case (4). Blocks x / Inf for variables on special-value types (since Inf / Inf = NaN).

Design significance

This confirms the direction predicted in prior analyses: value-dependent slope inspection is now a correctness prerequisite, not an optional follow-up. The prosupport function must inspect the constant operand at plan time and return SLOPE_ANY (unsafe) when it detects a non-finite value — or equivalently, the pathkey-matching code must reject the optimization when these conditions hold.

The recursive handling of case (1) is the most architecturally interesting piece. It means the slope-composition chain now carries not just a composed slope sign but also a "has passed through a decreasing sub-expression on a NaN-bearing type" flag (or equivalent logic). This is a richer invariant than the original 2-bit slope lattice could express, effectively adding a third dimension: {slope_sign} × {nan_safe: bool}.

What remains unaddressed

  • No committer has engaged since Dean Rasheed's comments in the earlier rounds.
  • The sqrt/ln/domain-restricted-function question raised on May 10 is not explicitly resolved in this message (though the error-on-invalid-domain reasoning likely still applies).
  • The planning-overhead question from v6 is not revisited.
  • Merge-join / equivalence-class integration remains unaddressed.

Process note

The gap from May 10 → June 1 (three weeks) suggests Alexandre was working through the implementation details of the NaN guards. The patch is now more defensively correct but also more complex — the "simple 2-bit slope" model has acquired type-aware runtime guards, which increases review surface for committers.