summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/cluster.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/cluster.out')
-rw-r--r--src/test/regress/expected/cluster.out667
1 files changed, 667 insertions, 0 deletions
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
new file mode 100644
index 0000000..542c2e0
--- /dev/null
+++ b/src/test/regress/expected/cluster.out
@@ -0,0 +1,667 @@
+--
+-- CLUSTER
+--
+CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
+ b INT);
+CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
+ b INT,
+ c TEXT,
+ d TEXT,
+ CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
+CREATE INDEX clstr_tst_b ON clstr_tst (b);
+CREATE INDEX clstr_tst_c ON clstr_tst (c);
+CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
+CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c);
+INSERT INTO clstr_tst_s (b) VALUES (0);
+INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
+INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
+INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
+INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
+INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
+CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
+INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
+INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
+INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
+INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
+INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
+INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
+INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
+INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
+INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
+INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
+INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
+INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
+INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
+INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
+INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
+INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
+INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
+INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
+INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
+INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
+INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
+INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
+INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
+INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
+INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
+INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
+INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
+INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
+INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
+INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
+INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
+-- This entry is needed to test that TOASTED values are copied correctly.
+INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
+CLUSTER clstr_tst_c ON clstr_tst;
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
+ a | b | c | substring | length
+----+----+---------------+--------------------------------+--------
+ 10 | 14 | catorce | |
+ 18 | 5 | cinco | |
+ 9 | 4 | cuatro | |
+ 26 | 19 | diecinueve | |
+ 12 | 18 | dieciocho | |
+ 30 | 16 | dieciseis | |
+ 24 | 17 | diecisiete | |
+ 2 | 10 | diez | |
+ 23 | 12 | doce | |
+ 11 | 2 | dos | |
+ 25 | 9 | nueve | |
+ 31 | 8 | ocho | |
+ 1 | 11 | once | |
+ 28 | 15 | quince | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+ 29 | 7 | siete | |
+ 15 | 13 | trece | |
+ 22 | 30 | treinta | |
+ 17 | 32 | treinta y dos | |
+ 3 | 31 | treinta y uno | |
+ 5 | 3 | tres | |
+ 20 | 1 | uno | |
+ 6 | 20 | veinte | |
+ 14 | 25 | veinticinco | |
+ 21 | 24 | veinticuatro | |
+ 4 | 22 | veintidos | |
+ 19 | 29 | veintinueve | |
+ 16 | 28 | veintiocho | |
+ 27 | 26 | veintiseis | |
+ 13 | 27 | veintisiete | |
+ 7 | 23 | veintitres | |
+ 8 | 21 | veintiuno | |
+(32 rows)
+
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
+ a | b | c | substring | length
+----+----+---------------+--------------------------------+--------
+ 1 | 11 | once | |
+ 2 | 10 | diez | |
+ 3 | 31 | treinta y uno | |
+ 4 | 22 | veintidos | |
+ 5 | 3 | tres | |
+ 6 | 20 | veinte | |
+ 7 | 23 | veintitres | |
+ 8 | 21 | veintiuno | |
+ 9 | 4 | cuatro | |
+ 10 | 14 | catorce | |
+ 11 | 2 | dos | |
+ 12 | 18 | dieciocho | |
+ 13 | 27 | veintisiete | |
+ 14 | 25 | veinticinco | |
+ 15 | 13 | trece | |
+ 16 | 28 | veintiocho | |
+ 17 | 32 | treinta y dos | |
+ 18 | 5 | cinco | |
+ 19 | 29 | veintinueve | |
+ 20 | 1 | uno | |
+ 21 | 24 | veinticuatro | |
+ 22 | 30 | treinta | |
+ 23 | 12 | doce | |
+ 24 | 17 | diecisiete | |
+ 25 | 9 | nueve | |
+ 26 | 19 | diecinueve | |
+ 27 | 26 | veintiseis | |
+ 28 | 15 | quince | |
+ 29 | 7 | siete | |
+ 30 | 16 | dieciseis | |
+ 31 | 8 | ocho | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+(32 rows)
+
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
+ a | b | c | substring | length
+----+----+---------------+--------------------------------+--------
+ 20 | 1 | uno | |
+ 11 | 2 | dos | |
+ 5 | 3 | tres | |
+ 9 | 4 | cuatro | |
+ 18 | 5 | cinco | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+ 29 | 7 | siete | |
+ 31 | 8 | ocho | |
+ 25 | 9 | nueve | |
+ 2 | 10 | diez | |
+ 1 | 11 | once | |
+ 23 | 12 | doce | |
+ 15 | 13 | trece | |
+ 10 | 14 | catorce | |
+ 28 | 15 | quince | |
+ 30 | 16 | dieciseis | |
+ 24 | 17 | diecisiete | |
+ 12 | 18 | dieciocho | |
+ 26 | 19 | diecinueve | |
+ 6 | 20 | veinte | |
+ 8 | 21 | veintiuno | |
+ 4 | 22 | veintidos | |
+ 7 | 23 | veintitres | |
+ 21 | 24 | veinticuatro | |
+ 14 | 25 | veinticinco | |
+ 27 | 26 | veintiseis | |
+ 13 | 27 | veintisiete | |
+ 16 | 28 | veintiocho | |
+ 19 | 29 | veintinueve | |
+ 22 | 30 | treinta | |
+ 3 | 31 | treinta y uno | |
+ 17 | 32 | treinta y dos | |
+(32 rows)
+
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
+ a | b | c | substring | length
+----+----+---------------+--------------------------------+--------
+ 10 | 14 | catorce | |
+ 18 | 5 | cinco | |
+ 9 | 4 | cuatro | |
+ 26 | 19 | diecinueve | |
+ 12 | 18 | dieciocho | |
+ 30 | 16 | dieciseis | |
+ 24 | 17 | diecisiete | |
+ 2 | 10 | diez | |
+ 23 | 12 | doce | |
+ 11 | 2 | dos | |
+ 25 | 9 | nueve | |
+ 31 | 8 | ocho | |
+ 1 | 11 | once | |
+ 28 | 15 | quince | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+ 29 | 7 | siete | |
+ 15 | 13 | trece | |
+ 22 | 30 | treinta | |
+ 17 | 32 | treinta y dos | |
+ 3 | 31 | treinta y uno | |
+ 5 | 3 | tres | |
+ 20 | 1 | uno | |
+ 6 | 20 | veinte | |
+ 14 | 25 | veinticinco | |
+ 21 | 24 | veinticuatro | |
+ 4 | 22 | veintidos | |
+ 19 | 29 | veintinueve | |
+ 16 | 28 | veintiocho | |
+ 27 | 26 | veintiseis | |
+ 13 | 27 | veintisiete | |
+ 7 | 23 | veintitres | |
+ 8 | 21 | veintiuno | |
+(32 rows)
+
+-- Verify that inheritance link still works
+INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
+SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
+ a | b | c | substring | length
+----+-----+----------------+--------------------------------+--------
+ 10 | 14 | catorce | |
+ 18 | 5 | cinco | |
+ 9 | 4 | cuatro | |
+ 26 | 19 | diecinueve | |
+ 12 | 18 | dieciocho | |
+ 30 | 16 | dieciseis | |
+ 24 | 17 | diecisiete | |
+ 2 | 10 | diez | |
+ 23 | 12 | doce | |
+ 11 | 2 | dos | |
+ 25 | 9 | nueve | |
+ 31 | 8 | ocho | |
+ 1 | 11 | once | |
+ 28 | 15 | quince | |
+ 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
+ 29 | 7 | siete | |
+ 15 | 13 | trece | |
+ 22 | 30 | treinta | |
+ 17 | 32 | treinta y dos | |
+ 3 | 31 | treinta y uno | |
+ 5 | 3 | tres | |
+ 20 | 1 | uno | |
+ 6 | 20 | veinte | |
+ 14 | 25 | veinticinco | |
+ 21 | 24 | veinticuatro | |
+ 4 | 22 | veintidos | |
+ 19 | 29 | veintinueve | |
+ 16 | 28 | veintiocho | |
+ 27 | 26 | veintiseis | |
+ 13 | 27 | veintisiete | |
+ 7 | 23 | veintitres | |
+ 8 | 21 | veintiuno | |
+ 0 | 100 | in child table | |
+(33 rows)
+
+-- Verify that foreign key link still works
+INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
+ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con"
+DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s".
+SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
+ORDER BY 1;
+ conname
+----------------
+ clstr_tst_con
+ clstr_tst_pkey
+(2 rows)
+
+SELECT relname, relkind,
+ EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
+FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
+ relname | relkind | hastoast
+----------------------+---------+----------
+ clstr_tst | r | t
+ clstr_tst_a_seq | S | f
+ clstr_tst_b | i | f
+ clstr_tst_b_c | i | f
+ clstr_tst_c | i | f
+ clstr_tst_c_b | i | f
+ clstr_tst_inh | r | t
+ clstr_tst_pkey | i | f
+ clstr_tst_s | r | f
+ clstr_tst_s_pkey | i | f
+ clstr_tst_s_rf_a_seq | S | f
+(11 rows)
+
+-- Verify that indisclustered is correctly set
+SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
+WHERE pg_class.oid=indexrelid
+ AND indrelid=pg_class_2.oid
+ AND pg_class_2.relname = 'clstr_tst'
+ AND indisclustered;
+ relname
+-------------
+ clstr_tst_c
+(1 row)
+
+-- Try changing indisclustered
+ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
+SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
+WHERE pg_class.oid=indexrelid
+ AND indrelid=pg_class_2.oid
+ AND pg_class_2.relname = 'clstr_tst'
+ AND indisclustered;
+ relname
+---------------
+ clstr_tst_b_c
+(1 row)
+
+-- Try turning off all clustering
+ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
+SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
+WHERE pg_class.oid=indexrelid
+ AND indrelid=pg_class_2.oid
+ AND pg_class_2.relname = 'clstr_tst'
+ AND indisclustered;
+ relname
+---------
+(0 rows)
+
+-- Verify that toast tables are clusterable
+CLUSTER pg_toast.pg_toast_826 USING pg_toast_826_index;
+-- Verify that clustering all tables does in fact cluster the right ones
+CREATE USER regress_clstr_user;
+CREATE TABLE clstr_1 (a INT PRIMARY KEY);
+CREATE TABLE clstr_2 (a INT PRIMARY KEY);
+CREATE TABLE clstr_3 (a INT PRIMARY KEY);
+ALTER TABLE clstr_1 OWNER TO regress_clstr_user;
+ALTER TABLE clstr_3 OWNER TO regress_clstr_user;
+GRANT SELECT ON clstr_2 TO regress_clstr_user;
+INSERT INTO clstr_1 VALUES (2);
+INSERT INTO clstr_1 VALUES (1);
+INSERT INTO clstr_2 VALUES (2);
+INSERT INTO clstr_2 VALUES (1);
+INSERT INTO clstr_3 VALUES (2);
+INSERT INTO clstr_3 VALUES (1);
+-- "CLUSTER <tablename>" on a table that hasn't been clustered
+CLUSTER clstr_2;
+ERROR: there is no previously clustered index for table "clstr_2"
+CLUSTER clstr_1_pkey ON clstr_1;
+CLUSTER clstr_2 USING clstr_2_pkey;
+SELECT * FROM clstr_1 UNION ALL
+ SELECT * FROM clstr_2 UNION ALL
+ SELECT * FROM clstr_3;
+ a
+---
+ 1
+ 2
+ 1
+ 2
+ 2
+ 1
+(6 rows)
+
+-- revert to the original state
+DELETE FROM clstr_1;
+DELETE FROM clstr_2;
+DELETE FROM clstr_3;
+INSERT INTO clstr_1 VALUES (2);
+INSERT INTO clstr_1 VALUES (1);
+INSERT INTO clstr_2 VALUES (2);
+INSERT INTO clstr_2 VALUES (1);
+INSERT INTO clstr_3 VALUES (2);
+INSERT INTO clstr_3 VALUES (1);
+-- this user can only cluster clstr_1 and clstr_3, but the latter
+-- has not been clustered
+SET SESSION AUTHORIZATION regress_clstr_user;
+CLUSTER;
+SELECT * FROM clstr_1 UNION ALL
+ SELECT * FROM clstr_2 UNION ALL
+ SELECT * FROM clstr_3;
+ a
+---
+ 1
+ 2
+ 2
+ 1
+ 2
+ 1
+(6 rows)
+
+-- cluster a single table using the indisclustered bit previously set
+DELETE FROM clstr_1;
+INSERT INTO clstr_1 VALUES (2);
+INSERT INTO clstr_1 VALUES (1);
+CLUSTER clstr_1;
+SELECT * FROM clstr_1;
+ a
+---
+ 1
+ 2
+(2 rows)
+
+-- Test MVCC-safety of cluster. There isn't much we can do to verify the
+-- results with a single backend...
+CREATE TABLE clustertest (key int PRIMARY KEY);
+INSERT INTO clustertest VALUES (10);
+INSERT INTO clustertest VALUES (20);
+INSERT INTO clustertest VALUES (30);
+INSERT INTO clustertest VALUES (40);
+INSERT INTO clustertest VALUES (50);
+-- Use a transaction so that updates are not committed when CLUSTER sees 'em
+BEGIN;
+-- Test update where the old row version is found first in the scan
+UPDATE clustertest SET key = 100 WHERE key = 10;
+-- Test update where the new row version is found first in the scan
+UPDATE clustertest SET key = 35 WHERE key = 40;
+-- Test longer update chain
+UPDATE clustertest SET key = 60 WHERE key = 50;
+UPDATE clustertest SET key = 70 WHERE key = 60;
+UPDATE clustertest SET key = 80 WHERE key = 70;
+SELECT * FROM clustertest;
+ key
+-----
+ 20
+ 30
+ 100
+ 35
+ 80
+(5 rows)
+
+CLUSTER clustertest_pkey ON clustertest;
+SELECT * FROM clustertest;
+ key
+-----
+ 20
+ 30
+ 35
+ 80
+ 100
+(5 rows)
+
+COMMIT;
+SELECT * FROM clustertest;
+ key
+-----
+ 20
+ 30
+ 35
+ 80
+ 100
+(5 rows)
+
+-- check that temp tables can be clustered
+create temp table clstr_temp (col1 int primary key, col2 text);
+insert into clstr_temp values (2, 'two'), (1, 'one');
+cluster clstr_temp using clstr_temp_pkey;
+select * from clstr_temp;
+ col1 | col2
+------+------
+ 1 | one
+ 2 | two
+(2 rows)
+
+drop table clstr_temp;
+RESET SESSION AUTHORIZATION;
+-- check clustering an empty table
+DROP TABLE clustertest;
+CREATE TABLE clustertest (f1 int PRIMARY KEY);
+CLUSTER clustertest USING clustertest_pkey;
+CLUSTER clustertest;
+-- Check that partitioned tables can be clustered
+CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
+CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1) TO (10) PARTITION BY RANGE (a);
+CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (5) TO (10) PARTITION BY RANGE (a);
+CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (10) TO (20);
+CREATE TABLE clstrpart3 PARTITION OF clstrpart DEFAULT PARTITION BY RANGE (a);
+CREATE TABLE clstrpart33 PARTITION OF clstrpart3 DEFAULT;
+CREATE INDEX clstrpart_only_idx ON ONLY clstrpart (a);
+CLUSTER clstrpart USING clstrpart_only_idx; -- fails
+ERROR: cannot cluster on invalid index "clstrpart_only_idx"
+DROP INDEX clstrpart_only_idx;
+CREATE INDEX clstrpart_idx ON clstrpart (a);
+-- Check that clustering sets new relfilenodes:
+CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+CLUSTER clstrpart USING clstrpart_idx;
+CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ;
+SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C";
+ relname | level | relkind | ?column?
+-------------+-------+---------+----------
+ clstrpart | 0 | p | t
+ clstrpart1 | 1 | p | t
+ clstrpart11 | 2 | r | f
+ clstrpart12 | 2 | p | t
+ clstrpart2 | 1 | r | f
+ clstrpart3 | 1 | p | t
+ clstrpart33 | 2 | r | f
+(7 rows)
+
+-- Partitioned indexes aren't and can't be marked un/clustered:
+\d clstrpart
+ Partitioned table "public.clstrpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: RANGE (a)
+Indexes:
+ "clstrpart_idx" btree (a)
+Number of partitions: 3 (Use \d+ to list them.)
+
+CLUSTER clstrpart;
+ERROR: there is no previously clustered index for table "clstrpart"
+ALTER TABLE clstrpart SET WITHOUT CLUSTER;
+ERROR: cannot mark index clustered in partitioned table
+ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
+ERROR: cannot mark index clustered in partitioned table
+DROP TABLE clstrpart;
+-- Ownership of partitions is checked
+CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
+CREATE INDEX ptnowner_i_idx ON ptnowner(i);
+CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);
+CREATE ROLE regress_ptnowner;
+CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);
+ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
+ALTER TABLE ptnowner OWNER TO regress_ptnowner;
+CREATE TEMP TABLE ptnowner_oldnodes AS
+ SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
+ JOIN pg_class AS c ON c.oid=tree.relid;
+SET SESSION AUTHORIZATION regress_ptnowner;
+CLUSTER ptnowner USING ptnowner_i_idx;
+RESET SESSION AUTHORIZATION;
+SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
+ JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";
+ relname | ?column?
+-----------+----------
+ ptnowner | t
+ ptnowner1 | f
+ ptnowner2 | t
+(3 rows)
+
+DROP TABLE ptnowner;
+DROP ROLE regress_ptnowner;
+-- Test CLUSTER with external tuplesorting
+create table clstr_4 as select * from tenk1;
+create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
+-- ensure we don't use the index in CLUSTER nor the checking SELECTs
+set enable_indexscan = off;
+-- Use external sort:
+set maintenance_work_mem = '1MB';
+cluster clstr_4 using cluster_sort;
+select * from
+(select hundred, lag(hundred) over () as lhundred,
+ thousand, lag(thousand) over () as lthousand,
+ tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss
+where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
+ hundred | lhundred | thousand | lthousand | tenthous | ltenthous
+---------+----------+----------+-----------+----------+-----------
+(0 rows)
+
+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;
+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';
+ 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;
+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';
+ 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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-10 01:00:10 +0000