Re: SLOPE - Planner optimizations on monotonic expressions.

Thank you again for your review Zsolt,

On Wed, Aug 26, 2026 at 11:30 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
wrote:

> > I had previously dropped the support to the timezone dependent functions.
> > This patchset has one additional patch where that is fixed.
>
> I focused on mostly this, and found a few issues in it:
>
> 1: session_timezone is read at plan time, so cached plans can go stale
> and return wrong results
>
> SET enable_seqscan = off;
> SET enable_bitmapscan = off;
> CREATE TABLE pc(t timestamptz);
> INSERT INTO pc VALUES ('2025-11-02 05:59:59.999999+00'), ('2025-11-02
> 06:00:00+00');
> CREATE INDEX ON pc(t);
> PREPARE q AS SELECT t AT LOCAL AS l FROM pc ORDER BY 1;
> EXECUTE q; -- generic plan, built under Etc/UTC
> SET TimeZone = 'America/New_York';
> EXECUTE q; -- 01:59:59.999999 then 01:00:00
> SELECT t AT LOCAL AS l FROM pc ORDER BY 1; -- same query, fresh plan:
> correct
>

0008 - "plan-cache guards" added a flag CURSOR_OPT_ONESHOT to cursorOptions
that is saved as a field to PlannerGlobal, and added a field PlannerInfo
*root to
SupportMonotonicRequest. This way monotonic request functions can see
information
from the plan being created, and that in turn allows distinguishing whether
the plan
is going to be cached or not (not sure if I covered all the cases here). If
!is_oneshot
declares no monotonicity for the function forcing the plan to emit a sort
node.

2: timezone(text, timestamp) isn't monotonic
>
> SET enable_seqscan = off;
> SET enable_bitmapscan = off;
> CREATE TABLE gap(ts timestamp);
> INSERT INTO gap VALUES ('2026-03-08 01:59'), ('2026-03-08 02:30'),
> ('2026-03-08 03:00');
> CREATE INDEX ON gap(ts);
> EXPLAIN (COSTS OFF) SELECT timezone('America/New_York', ts) FROM gap ORDER
> BY 1;
> SELECT timezone('America/New_York', ts) FROM gap ORDER BY 1;
> SET enable_slope = off;
> SELECT timezone('America/New_York', ts) FROM gap ORDER BY 1;
>

0006 - "Timezone" implemented two monotonicity masks, one for
UTC to TZ and one from TZ to UTC. Kept the plan-based report on
slope.sql, and I am attaching tzbreak.sql, an attempt to reveal monotonicity
violations with data.

sort required | has sort | n
---------------+----------+-------
maybe | f | 10129 <- monotonic, not disproven
maybe | t | 2483 <- non-monotonic, not proven
yes | f | 2 <- known issue [1]
yes | t | 2934 <- proven non-monotonic

The second row (maybe, t) indicates where the planner produced a sort node,
but no example of non-monotonicity was found on the queries.

3: unrecognized zones results in an error during planning
>
> CREATE TABLE e(t timestamptz);
> CREATE INDEX ON e(t);
> SELECT date_trunc('day', t, 'Bogus/Zone') FROM e ORDER BY 1; --
> ERROR: time zone "Bogus/Zone" not recognized
>

0007 - "timezone errsave" exposed an ErrorSaveContext that can be
passed to capture the errors instead of omitting, and updated the
call sites to pass an extra NULL.

> 4: inspect_monotonicity has a corner-case issue with date_trunc which
> returns a timestamptz

Submitted a separate patch for that [1], as in my understanding is a bug
in date_trunc.

> There's also an uninitialized variable warning in 0002:
> - index_pathkeys = build_index_pathkeys(root, index,
> -
> BackwardScanDirection);
>

That was not incorrect but was also not obviously correct.

The variable was initialized under
+ if (index_is_ordered && pathkeys_possibly_useful)
and used under
+ if (index_is_ordered && pathkeys_possibly_useful && index_pathkeys !=
NIL)

That initialisation was selectively initializing variables under the "if".
I simplified by initializing before the "if", it would be more elegant to
initialise at the top but, arguably, that would be a few unnecessary
assignments if the code returns before these variables are touched.

[1]
https://www.postgresql.org/message-id/flat/CAE8JnxMWMqMUfz0QruiCeHJifm55--LaBqW6-apeNJUMrJ7oig(at)mail(dot)gmail(dot)com

Regards,
Alexandre