- 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?
