src/test/regress/sql/cluster.sql

   1 --
   2 --  CLUSTER
   3 --
   4
   5 CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
   6         b INT);
   7
   8 CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
   9         b INT,
  10         c TEXT,
  11         d TEXT,
  12         CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
  13
  14 CREATE INDEX clstr_tst_b ON clstr_tst (b);
  15 CREATE INDEX clstr_tst_c ON clstr_tst (c);
  16 CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
  17 CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c);
  18
  19 INSERT INTO clstr_tst_s (b) VALUES (0);
  20 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
  21 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
  22 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
  23 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
  24 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
  25
  26 CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
  27
  28 INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
  29 INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
  30 INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
  31 INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
  32 INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
  33 INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
  34 INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
  35 INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
  36 INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
  37 INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
  38 INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
  39 INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
  40 INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
  41 INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
  42 INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
  43 INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
  44 INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
  45 INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
  46 INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
  47 INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
  48 INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
  49 INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
  50 INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
  51 INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
  52 INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
  53 INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
  54 INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
  55 INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
  56 INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
  57 INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
  58 INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
  59 -- This entry is needed to test that TOASTED values are copied correctly.
  60 INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
  61
  62 CLUSTER clstr_tst_c ON clstr_tst;
  63
  64 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
  65 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
  66 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
  67 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
  68
  69 -- Verify that inheritance link still works
  70 INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
  71 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
  72
  73 -- Verify that foreign key link still works
  74 INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
  75
  76 SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
  77 ORDER BY 1;
  78
  79
  80 SELECT relname, relkind,
  81     EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
  82 FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
  83
  84 -- Verify that indisclustered is correctly set
  85 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
  86 WHERE pg_class.oid=indexrelid
  87         AND indrelid=pg_class_2.oid
  88         AND pg_class_2.relname = 'clstr_tst'
  89         AND indisclustered;
  90
  91 -- Try changing indisclustered
  92 ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
  93 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
  94 WHERE pg_class.oid=indexrelid
  95         AND indrelid=pg_class_2.oid
  96         AND pg_class_2.relname = 'clstr_tst'
  97         AND indisclustered;
  98
  99 -- Try turning off all clustering
 100 ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
 101 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
 102 WHERE pg_class.oid=indexrelid
 103         AND indrelid=pg_class_2.oid
 104         AND pg_class_2.relname = 'clstr_tst'
 105         AND indisclustered;
 106
 107 -- Verify that toast tables are clusterable
 108 CLUSTER pg_toast.pg_toast_826 USING pg_toast_826_index;
 109
 110 -- Verify that clustering all tables does in fact cluster the right ones
 111 CREATE USER regress_clstr_user;
 112 CREATE TABLE clstr_1 (a INT PRIMARY KEY);
 113 CREATE TABLE clstr_2 (a INT PRIMARY KEY);
 114 CREATE TABLE clstr_3 (a INT PRIMARY KEY);
 115 ALTER TABLE clstr_1 OWNER TO regress_clstr_user;
 116 ALTER TABLE clstr_3 OWNER TO regress_clstr_user;
 117 GRANT SELECT ON clstr_2 TO regress_clstr_user;
 118 INSERT INTO clstr_1 VALUES (2);
 119 INSERT INTO clstr_1 VALUES (1);
 120 INSERT INTO clstr_2 VALUES (2);
 121 INSERT INTO clstr_2 VALUES (1);
 122 INSERT INTO clstr_3 VALUES (2);
 123 INSERT INTO clstr_3 VALUES (1);
 124
 125 -- "CLUSTER <tablename>" on a table that hasn't been clustered
 126 CLUSTER clstr_2;
 127
 128 CLUSTER clstr_1_pkey ON clstr_1;
 129 CLUSTER clstr_2 USING clstr_2_pkey;
 130 SELECT * FROM clstr_1 UNION ALL
 131   SELECT * FROM clstr_2 UNION ALL
 132   SELECT * FROM clstr_3;
 133
 134 -- revert to the original state
 135 DELETE FROM clstr_1;
 136 DELETE FROM clstr_2;
 137 DELETE FROM clstr_3;
 138 INSERT INTO clstr_1 VALUES (2);
 139 INSERT INTO clstr_1 VALUES (1);
 140 INSERT INTO clstr_2 VALUES (2);
 141 INSERT INTO clstr_2 VALUES (1);
 142 INSERT INTO clstr_3 VALUES (2);
 143 INSERT INTO clstr_3 VALUES (1);
 144
 145 -- this user can only cluster clstr_1 and clstr_3, but the latter
 146 -- has not been clustered
 147 SET SESSION AUTHORIZATION regress_clstr_user;
 148 CLUSTER;
 149 SELECT * FROM clstr_1 UNION ALL
 150   SELECT * FROM clstr_2 UNION ALL
 151   SELECT * FROM clstr_3;
 152
 153 -- cluster a single table using the indisclustered bit previously set
 154 DELETE FROM clstr_1;
 155 INSERT INTO clstr_1 VALUES (2);
 156 INSERT INTO clstr_1 VALUES (1);
 157 CLUSTER clstr_1;
 158 SELECT * FROM clstr_1;
 159
 160 -- Test MVCC-safety of cluster. There isn't much we can do to verify the
 161 -- results with a single backend...
 162
 163 CREATE TABLE clustertest (key int PRIMARY KEY);
 164
 165 INSERT INTO clustertest VALUES (10);
 166 INSERT INTO clustertest VALUES (20);
 167 INSERT INTO clustertest VALUES (30);
 168 INSERT INTO clustertest VALUES (40);
 169 INSERT INTO clustertest VALUES (50);
 170
 171 -- Use a transaction so that updates are not committed when CLUSTER sees 'em
 172 BEGIN;
 173
 174 -- Test update where the old row version is found first in the scan
 175 UPDATE clustertest SET key = 100 WHERE key = 10;
 176
 177 -- Test update where the new row version is found first in the scan
 178 UPDATE clustertest SET key = 35 WHERE key = 40;
 179
 180 -- Test longer update chain
 181 UPDATE clustertest SET key = 60 WHERE key = 50;
 182 UPDATE clustertest SET key = 70 WHERE key = 60;
 183 UPDATE clustertest SET key = 80 WHERE key = 70;
 184
 185 SELECT * FROM clustertest;
 186 CLUSTER clustertest_pkey ON clustertest;
 187 SELECT * FROM clustertest;
 188
 189 COMMIT;
 190
 191 SELECT * FROM clustertest;
 192
 193 -- check that temp tables can be clustered
 194 create temp table clstr_temp (col1 int primary key, col2 text);
 195 insert into clstr_temp values (2, 'two'), (1, 'one');
 196 cluster clstr_temp using clstr_temp_pkey;
 197 select * from clstr_temp;
 198 drop table clstr_temp;
 199
 200 RESET SESSION AUTHORIZATION;
 201
 202 -- check clustering an empty table
 203 DROP TABLE clustertest;
 204 CREATE TABLE clustertest (f1 int PRIMARY KEY);
 205 CLUSTER clustertest USING clustertest_pkey;
 206 CLUSTER clustertest;
 207
 208 -- Check that partitioned tables can be clustered
 209 CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
 210 CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1) TO (10) PARTITION BY RANGE (a);
 211 CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1) TO (5);
 212 CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (5) TO (10) PARTITION BY RANGE (a);
 213 CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (10) TO (20);
 214 CREATE TABLE clstrpart3 PARTITION OF clstrpart DEFAULT PARTITION BY RANGE (a);
 215 CREATE TABLE clstrpart33 PARTITION OF clstrpart3 DEFAULT;
 216 CREATE INDEX clstrpart_only_idx ON ONLY clstrpart (a);
 217 CLUSTER clstrpart USING clstrpart_only_idx; -- fails
 218 DROP INDEX clstrpart_only_idx;
 219 CREATE INDEX clstrpart_idx ON clstrpart (a);
 220 -- Check that clustering sets new relfilenodes:
 221 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 ;
 222 CLUSTER clstrpart USING clstrpart_idx;
 223 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 ;
 224 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";
 225 -- Partitioned indexes aren't and can't be marked un/clustered:
 226 \d clstrpart
 227 CLUSTER clstrpart;
 228 ALTER TABLE clstrpart SET WITHOUT CLUSTER;
 229 ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
 230 DROP TABLE clstrpart;
 231
 232 -- Ownership of partitions is checked
 233 CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
 234 CREATE INDEX ptnowner_i_idx ON ptnowner(i);
 235 CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);
 236 CREATE ROLE regress_ptnowner;
 237 CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);
 238 ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
 239 ALTER TABLE ptnowner OWNER TO regress_ptnowner;
 240 CREATE TEMP TABLE ptnowner_oldnodes AS
 241   SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
 242   JOIN pg_class AS c ON c.oid=tree.relid;
 243 SET SESSION AUTHORIZATION regress_ptnowner;
 244 CLUSTER ptnowner USING ptnowner_i_idx;
 245 RESET SESSION AUTHORIZATION;
 246 SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
 247   JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";
 248 DROP TABLE ptnowner;
 249 DROP ROLE regress_ptnowner;
 250
 251 -- Test CLUSTER with external tuplesorting
 252
 253 create table clstr_4 as select * from tenk1;
 254 create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
 255 -- ensure we don't use the index in CLUSTER nor the checking SELECTs
 256 set enable_indexscan = off;
 257
 258 -- Use external sort:
 259 set maintenance_work_mem = '1MB';
 260 cluster clstr_4 using cluster_sort;
 261 select * from
 262 (select hundred, lag(hundred) over () as lhundred,
 263         thousand, lag(thousand) over () as lthousand,
 264         tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss
 265 where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
 266
 267 reset enable_indexscan;
 268 reset maintenance_work_mem;
 269
 270 -- test CLUSTER on expression index
 271 CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
 272 INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i);
 273 CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
 274 CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));
 275
 276 -- verify indexes work before cluster
 277 BEGIN;
 278 SET LOCAL enable_seqscan = false;
 279 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 280 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 281 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 282 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 283 COMMIT;
 284
 285 -- and after clustering on clstr_expression_minus_a
 286 CLUSTER clstr_expression USING clstr_expression_minus_a;
 287 WITH rows AS
 288   (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression)
 289 SELECT * FROM rows WHERE la < a;
 290 BEGIN;
 291 SET LOCAL enable_seqscan = false;
 292 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 293 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 294 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 295 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 296 COMMIT;
 297
 298 -- and after clustering on clstr_expression_upper_b
 299 CLUSTER clstr_expression USING clstr_expression_upper_b;
 300 WITH rows AS
 301   (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression)
 302 SELECT * FROM rows WHERE upper(lb) > upper(b);
 303 BEGIN;
 304 SET LOCAL enable_seqscan = false;
 305 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 306 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
 307 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 308 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
 309 COMMIT;
 310
 311 -- clean up
 312 DROP TABLE clustertest;
 313 DROP TABLE clstr_1;
 314 DROP TABLE clstr_2;
 315 DROP TABLE clstr_3;
 316 DROP TABLE clstr_4;
 317 DROP TABLE clstr_expression;
 318
 319 DROP USER regress_clstr_user;