From 580de16e30506eace4cdd962c1ab31a471da5dff Mon Sep 17 00:00:00 2001 From: Thomas Munro 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