| -- |
| -- CLUSTER |
| -- |
| CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY, |
| b INT) DISTRIBUTED BY (rf_a); |
| 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) |
| DISTRIBUTED BY (a); |
| WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Apache Cloudberry, will not be enforced |
| 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); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| 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'); |
| 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, distkey int) DISTRIBUTED BY (distkey); |
| CREATE INDEX clustertest_pkey ON clustertest (key); |
| INSERT INTO clustertest VALUES (10, 1); |
| INSERT INTO clustertest VALUES (20, 2); |
| INSERT INTO clustertest VALUES (30, 1); |
| INSERT INTO clustertest VALUES (40, 2); |
| INSERT INTO clustertest VALUES (50, 3); |
| -- 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 key FROM clustertest; |
| key |
| ----- |
| 20 |
| 30 |
| 100 |
| 35 |
| 80 |
| (5 rows) |
| |
| CLUSTER clustertest_pkey ON clustertest; |
| SELECT key FROM clustertest; |
| key |
| ----- |
| 20 |
| 30 |
| 35 |
| 80 |
| 100 |
| (5 rows) |
| |
| COMMIT; |
| SELECT key 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 | f |
| (3 rows) |
| |
| DROP TABLE ptnowner; |
| DROP ROLE regress_ptnowner; |
| -- Test CLUSTER with external tuplesorting |
| -- The tests assume that the rows come out in the physical order, as |
| -- sorted by CLUSTER. In GPDB, add a dummy column to force all the rows to go |
| -- to the same segment, otherwise the rows come out in random order from the |
| -- segments. |
| create table clstr_4 as select 1 as dummy, * from tenk1 distributed by (dummy); |
| 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))); |
| -- enable to keep same plan with pg |
| SET gp_enable_relsize_collection= on; |
| -- verify indexes work before cluster |
| BEGIN; |
| SET LOCAL enable_seqscan = false; |
| EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3'; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using clstr_expression_upper_b on clstr_expression |
| Index Cond: (upper(b) = 'PREFIX3'::text) |
| Optimizer: Postgres query optimizer |
| (4 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 |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: b |
| -> Index Scan using clstr_expression_minus_a on clstr_expression |
| Index Cond: ((- a) = '-3'::integer) |
| Optimizer: Postgres query optimizer |
| (5 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 (PARTITION BY gp_segment_id 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 |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using clstr_expression_upper_b on clstr_expression |
| Index Cond: (upper(b) = 'PREFIX3'::text) |
| Optimizer: Postgres query optimizer |
| (4 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 |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: b |
| -> Index Scan using clstr_expression_minus_a on clstr_expression |
| Index Cond: ((- a) = '-3'::integer) |
| Optimizer: Postgres query optimizer |
| (5 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 (PARTITION BY gp_segment_id 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 |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Scan using clstr_expression_upper_b on clstr_expression |
| Index Cond: (upper(b) = 'PREFIX3'::text) |
| Optimizer: Postgres query optimizer |
| (4 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 |
| --------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: b |
| -> Index Scan using clstr_expression_minus_a on clstr_expression |
| Index Cond: ((- a) = '-3'::integer) |
| Optimizer: Postgres query optimizer |
| (5 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 |
| SET gp_enable_relsize_collection= off; |
| 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; |
| -- Test transactional safety of CLUSTER against heap |
| CREATE TABLE foo (a int, b varchar, c int) DISTRIBUTED BY (a); |
| INSERT INTO foo SELECT i, 'initial insert' || i, i FROM generate_series(1,10000)i; |
| CREATE index ifoo on foo using btree (b); |
| -- execute cluster in a transaction but don't commit the transaction |
| BEGIN; |
| CLUSTER foo USING ifoo; |
| ABORT; |
| -- try cluster again |
| CLUSTER foo USING ifoo; |
| DROP TABLE foo; |
