Hi, Independently of a problem with a recent commit, it seems that $SUBJECT in all releases (well, I only tested as far back as 11). I attach an addition to the tests to show this, but here's a stand-alone repro:
DROP TABLE IF EXISTS clstr_expression; CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C"); INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i); CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b); CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b))); CLUSTER clstr_expression USING clstr_expression_minus_a; WITH rows AS (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression) SELECT * FROM rows WHERE la < a; All good, and now for the part that I think is misbehaving: CLUSTER clstr_expression USING clstr_expression_upper_b; WITH rows AS (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression) SELECT * FROM rows WHERE upper(lb) > upper(b); That should produce no rows. It works as expected if you SET enable_seqscan = off and re-run CLUSTER, revealing that it's the seq-scan-and-sort strategy that is broken. It also works as expected for non-yet-abbreviatable collations.
From 580de16e30506eace4cdd962c1ab31a471da5dff Mon Sep 17 00:00:00 2001 From: Thomas Munro <thomas.mu...@gmail.com> Date: Sun, 3 Apr 2022 14:13:00 +1200 Subject: [PATCH] Add simple test for CLUSTER on expression indexes. Assert that the CLUSTER command rewrites the heap in the expected order, when using an expression index. XXX CLUSTER on upper(b) for a COLLATE "C" column is currently broken, so this doesn't yet pass unless you disable seqscans --- src/test/regress/expected/cluster.out | 14 ++++++++++++++ src/test/regress/sql/cluster.sql | 6 ++++++ 2 files changed, 20 insertions(+) diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index e46a66952f..0da85faff8 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -511,6 +511,13 @@ SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b; COMMIT; -- and after clustering on clstr_expression_minus_a CLUSTER clstr_expression USING clstr_expression_minus_a; +WITH rows AS + (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression) +SELECT * FROM rows WHERE la < a; + ctid | la | a +------+----+--- +(0 rows) + BEGIN; SET LOCAL enable_seqscan = false; EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3'; @@ -545,6 +552,13 @@ SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b; COMMIT; -- and after clustering on clstr_expression_upper_b CLUSTER clstr_expression USING clstr_expression_upper_b; +WITH rows AS + (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression) +SELECT * FROM rows WHERE upper(lb) > upper(b); + ctid | lb | b +---------+-----------+----------- +(0 rows) + BEGIN; SET LOCAL enable_seqscan = false; EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3'; diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql index aee9cf83e0..99ee533c8d 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -245,6 +245,9 @@ COMMIT; -- and after clustering on clstr_expression_minus_a CLUSTER clstr_expression USING clstr_expression_minus_a; +WITH rows AS + (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression) +SELECT * FROM rows WHERE la < a; BEGIN; SET LOCAL enable_seqscan = false; EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3'; @@ -255,6 +258,9 @@ COMMIT; -- and after clustering on clstr_expression_upper_b CLUSTER clstr_expression USING clstr_expression_upper_b; +WITH rows AS + (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression) +SELECT * FROM rows WHERE upper(lb) > upper(b); BEGIN; SET LOCAL enable_seqscan = false; EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3'; -- 2.35.1
