summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/cluster.out107
-rw-r--r--src/test/regress/sql/cluster.sql37
2 files changed, 144 insertions, 0 deletions
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 2bb62212ea..bdae8fe00c 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -466,10 +466,117 @@ where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
reset enable_indexscan;
reset maintenance_work_mem;
+-- test CLUSTER on expression index
+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)));
+-- verify indexes work before cluster
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_upper_b on clstr_expression
+ Index Cond: (upper(b) = 'PREFIX3'::text)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ id | a | b
+----+---+---------
+ 3 | 3 | prefix3
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_minus_a on clstr_expression
+ Index Cond: ((- a) = '-3'::integer)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ id | a | b
+-----+---+-----------
+ 129 | 3 | prefix129
+ 3 | 3 | prefix3
+ 45 | 3 | prefix45
+ 87 | 3 | prefix87
+(4 rows)
+
+COMMIT;
+-- and after clustering on clstr_expression_minus_a
+CLUSTER clstr_expression USING clstr_expression_minus_a;
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_upper_b on clstr_expression
+ Index Cond: (upper(b) = 'PREFIX3'::text)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ id | a | b
+----+---+---------
+ 3 | 3 | prefix3
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_minus_a on clstr_expression
+ Index Cond: ((- a) = '-3'::integer)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ id | a | b
+-----+---+-----------
+ 129 | 3 | prefix129
+ 3 | 3 | prefix3
+ 45 | 3 | prefix45
+ 87 | 3 | prefix87
+(4 rows)
+
+COMMIT;
+-- and after clustering on clstr_expression_upper_b
+CLUSTER clstr_expression USING clstr_expression_upper_b;
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_upper_b on clstr_expression
+ Index Cond: (upper(b) = 'PREFIX3'::text)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+ id | a | b
+----+---+---------
+ 3 | 3 | prefix3
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Scan using clstr_expression_minus_a on clstr_expression
+ Index Cond: ((- a) = '-3'::integer)
+(2 rows)
+
+SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+ id | a | b
+-----+---+-----------
+ 129 | 3 | prefix129
+ 3 | 3 | prefix3
+ 45 | 3 | prefix45
+ 87 | 3 | prefix87
+(4 rows)
+
+COMMIT;
-- clean up
DROP TABLE clustertest;
DROP TABLE clstr_1;
DROP TABLE clstr_2;
DROP TABLE clstr_3;
DROP TABLE clstr_4;
+DROP TABLE clstr_expression;
DROP USER regress_clstr_user;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index 522bfeead4..188183647c 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -222,10 +222,47 @@ where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
reset enable_indexscan;
reset maintenance_work_mem;
+-- test CLUSTER on expression index
+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)));
+
+-- verify indexes work before cluster
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+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;
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+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;
+BEGIN;
+SET LOCAL enable_seqscan = false;
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
+EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
+COMMIT;
+
-- clean up
DROP TABLE clustertest;
DROP TABLE clstr_1;
DROP TABLE clstr_2;
DROP TABLE clstr_3;
DROP TABLE clstr_4;
+DROP TABLE clstr_expression;
+
DROP USER regress_clstr_user;
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-24 23:23:48 +0000