| blob | 4d40a6809ab46705673bc1e4a767e31443b90268 |
1 --
2 -- CLUSTER
3 --
4 CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY,
5 b INT);
6 CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY,
7 b INT,
8 c TEXT,
9 d TEXT,
10 CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s);
11 CREATE INDEX clstr_tst_b ON clstr_tst (b);
12 CREATE INDEX clstr_tst_c ON clstr_tst (c);
13 CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b);
14 CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c);
15 INSERT INTO clstr_tst_s (b) VALUES (0);
16 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
17 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
18 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
19 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
20 INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s;
21 CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst);
22 INSERT INTO clstr_tst (b, c) VALUES (11, 'once');
23 INSERT INTO clstr_tst (b, c) VALUES (10, 'diez');
24 INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno');
25 INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos');
26 INSERT INTO clstr_tst (b, c) VALUES (3, 'tres');
27 INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte');
28 INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres');
29 INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno');
30 INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro');
31 INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce');
32 INSERT INTO clstr_tst (b, c) VALUES (2, 'dos');
33 INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho');
34 INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete');
35 INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco');
36 INSERT INTO clstr_tst (b, c) VALUES (13, 'trece');
37 INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho');
38 INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos');
39 INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco');
40 INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve');
41 INSERT INTO clstr_tst (b, c) VALUES (1, 'uno');
42 INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro');
43 INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta');
44 INSERT INTO clstr_tst (b, c) VALUES (12, 'doce');
45 INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete');
46 INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve');
47 INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve');
48 INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis');
49 INSERT INTO clstr_tst (b, c) VALUES (15, 'quince');
50 INSERT INTO clstr_tst (b, c) VALUES (7, 'siete');
51 INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis');
52 INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho');
53 -- This entry is needed to test that TOASTED values are copied correctly.
54 INSERT INTO clstr_tst (b, c, d) VALUES (6, 'seis', repeat('xyzzy', 100000));
55 CLUSTER clstr_tst_c ON clstr_tst;
56 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
57 a | b | c | substring | length
58 ----+----+---------------+--------------------------------+--------
59 10 | 14 | catorce | |
60 18 | 5 | cinco | |
61 9 | 4 | cuatro | |
62 26 | 19 | diecinueve | |
63 12 | 18 | dieciocho | |
64 30 | 16 | dieciseis | |
65 24 | 17 | diecisiete | |
66 2 | 10 | diez | |
67 23 | 12 | doce | |
68 11 | 2 | dos | |
69 25 | 9 | nueve | |
70 31 | 8 | ocho | |
71 1 | 11 | once | |
72 28 | 15 | quince | |
73 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
74 29 | 7 | siete | |
75 15 | 13 | trece | |
76 22 | 30 | treinta | |
77 17 | 32 | treinta y dos | |
78 3 | 31 | treinta y uno | |
79 5 | 3 | tres | |
80 20 | 1 | uno | |
81 6 | 20 | veinte | |
82 14 | 25 | veinticinco | |
83 21 | 24 | veinticuatro | |
84 4 | 22 | veintidos | |
85 19 | 29 | veintinueve | |
86 16 | 28 | veintiocho | |
87 27 | 26 | veintiseis | |
88 13 | 27 | veintisiete | |
89 7 | 23 | veintitres | |
90 8 | 21 | veintiuno | |
91 (32 rows)
93 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY a;
94 a | b | c | substring | length
95 ----+----+---------------+--------------------------------+--------
96 1 | 11 | once | |
97 2 | 10 | diez | |
98 3 | 31 | treinta y uno | |
99 4 | 22 | veintidos | |
100 5 | 3 | tres | |
101 6 | 20 | veinte | |
102 7 | 23 | veintitres | |
103 8 | 21 | veintiuno | |
104 9 | 4 | cuatro | |
105 10 | 14 | catorce | |
106 11 | 2 | dos | |
107 12 | 18 | dieciocho | |
108 13 | 27 | veintisiete | |
109 14 | 25 | veinticinco | |
110 15 | 13 | trece | |
111 16 | 28 | veintiocho | |
112 17 | 32 | treinta y dos | |
113 18 | 5 | cinco | |
114 19 | 29 | veintinueve | |
115 20 | 1 | uno | |
116 21 | 24 | veinticuatro | |
117 22 | 30 | treinta | |
118 23 | 12 | doce | |
119 24 | 17 | diecisiete | |
120 25 | 9 | nueve | |
121 26 | 19 | diecinueve | |
122 27 | 26 | veintiseis | |
123 28 | 15 | quince | |
124 29 | 7 | siete | |
125 30 | 16 | dieciseis | |
126 31 | 8 | ocho | |
127 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
128 (32 rows)
130 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY b;
131 a | b | c | substring | length
132 ----+----+---------------+--------------------------------+--------
133 20 | 1 | uno | |
134 11 | 2 | dos | |
135 5 | 3 | tres | |
136 9 | 4 | cuatro | |
137 18 | 5 | cinco | |
138 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
139 29 | 7 | siete | |
140 31 | 8 | ocho | |
141 25 | 9 | nueve | |
142 2 | 10 | diez | |
143 1 | 11 | once | |
144 23 | 12 | doce | |
145 15 | 13 | trece | |
146 10 | 14 | catorce | |
147 28 | 15 | quince | |
148 30 | 16 | dieciseis | |
149 24 | 17 | diecisiete | |
150 12 | 18 | dieciocho | |
151 26 | 19 | diecinueve | |
152 6 | 20 | veinte | |
153 8 | 21 | veintiuno | |
154 4 | 22 | veintidos | |
155 7 | 23 | veintitres | |
156 21 | 24 | veinticuatro | |
157 14 | 25 | veinticinco | |
158 27 | 26 | veintiseis | |
159 13 | 27 | veintisiete | |
160 16 | 28 | veintiocho | |
161 19 | 29 | veintinueve | |
162 22 | 30 | treinta | |
163 3 | 31 | treinta y uno | |
164 17 | 32 | treinta y dos | |
165 (32 rows)
167 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst ORDER BY c;
168 a | b | c | substring | length
169 ----+----+---------------+--------------------------------+--------
170 10 | 14 | catorce | |
171 18 | 5 | cinco | |
172 9 | 4 | cuatro | |
173 26 | 19 | diecinueve | |
174 12 | 18 | dieciocho | |
175 30 | 16 | dieciseis | |
176 24 | 17 | diecisiete | |
177 2 | 10 | diez | |
178 23 | 12 | doce | |
179 11 | 2 | dos | |
180 25 | 9 | nueve | |
181 31 | 8 | ocho | |
182 1 | 11 | once | |
183 28 | 15 | quince | |
184 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
185 29 | 7 | siete | |
186 15 | 13 | trece | |
187 22 | 30 | treinta | |
188 17 | 32 | treinta y dos | |
189 3 | 31 | treinta y uno | |
190 5 | 3 | tres | |
191 20 | 1 | uno | |
192 6 | 20 | veinte | |
193 14 | 25 | veinticinco | |
194 21 | 24 | veinticuatro | |
195 4 | 22 | veintidos | |
196 19 | 29 | veintinueve | |
197 16 | 28 | veintiocho | |
198 27 | 26 | veintiseis | |
199 13 | 27 | veintisiete | |
200 7 | 23 | veintitres | |
201 8 | 21 | veintiuno | |
202 (32 rows)
204 -- Verify that inheritance link still works
205 INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table');
206 SELECT a,b,c,substring(d for 30), length(d) from clstr_tst;
207 a | b | c | substring | length
208 ----+-----+----------------+--------------------------------+--------
209 10 | 14 | catorce | |
210 18 | 5 | cinco | |
211 9 | 4 | cuatro | |
212 26 | 19 | diecinueve | |
213 12 | 18 | dieciocho | |
214 30 | 16 | dieciseis | |
215 24 | 17 | diecisiete | |
216 2 | 10 | diez | |
217 23 | 12 | doce | |
218 11 | 2 | dos | |
219 25 | 9 | nueve | |
220 31 | 8 | ocho | |
221 1 | 11 | once | |
222 28 | 15 | quince | |
223 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000
224 29 | 7 | siete | |
225 15 | 13 | trece | |
226 22 | 30 | treinta | |
227 17 | 32 | treinta y dos | |
228 3 | 31 | treinta y uno | |
229 5 | 3 | tres | |
230 20 | 1 | uno | |
231 6 | 20 | veinte | |
232 14 | 25 | veinticinco | |
233 21 | 24 | veinticuatro | |
234 4 | 22 | veintidos | |
235 19 | 29 | veintinueve | |
236 16 | 28 | veintiocho | |
237 27 | 26 | veintiseis | |
238 13 | 27 | veintisiete | |
239 7 | 23 | veintitres | |
240 8 | 21 | veintiuno | |
241 0 | 100 | in child table | |
242 (33 rows)
244 -- Verify that foreign key link still works
245 INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
246 ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con"
247 DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s".
248 SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
249 ORDER BY 1;
250 conname
251 ----------------------
252 clstr_tst_a_not_null
253 clstr_tst_con
254 clstr_tst_pkey
255 (3 rows)
257 SELECT relname, relkind,
258 EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
259 FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
260 relname | relkind | hastoast
261 ----------------------+---------+----------
262 clstr_tst | r | t
263 clstr_tst_a_seq | S | f
264 clstr_tst_b | i | f
265 clstr_tst_b_c | i | f
266 clstr_tst_c | i | f
267 clstr_tst_c_b | i | f
268 clstr_tst_inh | r | t
269 clstr_tst_pkey | i | f
270 clstr_tst_s | r | f
271 clstr_tst_s_pkey | i | f
272 clstr_tst_s_rf_a_seq | S | f
273 (11 rows)
275 -- Verify that indisclustered is correctly set
276 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
277 WHERE pg_class.oid=indexrelid
278 AND indrelid=pg_class_2.oid
279 AND pg_class_2.relname = 'clstr_tst'
280 AND indisclustered;
281 relname
282 -------------
283 clstr_tst_c
284 (1 row)
286 -- Try changing indisclustered
287 ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
288 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
289 WHERE pg_class.oid=indexrelid
290 AND indrelid=pg_class_2.oid
291 AND pg_class_2.relname = 'clstr_tst'
292 AND indisclustered;
293 relname
294 ---------------
295 clstr_tst_b_c
296 (1 row)
298 -- Try turning off all clustering
299 ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
300 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
301 WHERE pg_class.oid=indexrelid
302 AND indrelid=pg_class_2.oid
303 AND pg_class_2.relname = 'clstr_tst'
304 AND indisclustered;
305 relname
306 ---------
307 (0 rows)
309 -- Verify that toast tables are clusterable
310 CLUSTER pg_toast.pg_toast_826 USING pg_toast_826_index;
311 -- Verify that clustering all tables does in fact cluster the right ones
312 CREATE USER regress_clstr_user;
313 CREATE TABLE clstr_1 (a INT PRIMARY KEY);
314 CREATE TABLE clstr_2 (a INT PRIMARY KEY);
315 CREATE TABLE clstr_3 (a INT PRIMARY KEY);
316 ALTER TABLE clstr_1 OWNER TO regress_clstr_user;
317 ALTER TABLE clstr_3 OWNER TO regress_clstr_user;
318 GRANT SELECT ON clstr_2 TO regress_clstr_user;
319 INSERT INTO clstr_1 VALUES (2);
320 INSERT INTO clstr_1 VALUES (1);
321 INSERT INTO clstr_2 VALUES (2);
322 INSERT INTO clstr_2 VALUES (1);
323 INSERT INTO clstr_3 VALUES (2);
324 INSERT INTO clstr_3 VALUES (1);
325 -- "CLUSTER <tablename>" on a table that hasn't been clustered
326 CLUSTER clstr_2;
327 ERROR: there is no previously clustered index for table "clstr_2"
328 CLUSTER clstr_1_pkey ON clstr_1;
329 CLUSTER clstr_2 USING clstr_2_pkey;
330 SELECT * FROM clstr_1 UNION ALL
331 SELECT * FROM clstr_2 UNION ALL
332 SELECT * FROM clstr_3;
333 a
334 ---
335 1
336 2
337 1
338 2
339 2
340 1
341 (6 rows)
343 -- revert to the original state
344 DELETE FROM clstr_1;
345 DELETE FROM clstr_2;
346 DELETE FROM clstr_3;
347 INSERT INTO clstr_1 VALUES (2);
348 INSERT INTO clstr_1 VALUES (1);
349 INSERT INTO clstr_2 VALUES (2);
350 INSERT INTO clstr_2 VALUES (1);
351 INSERT INTO clstr_3 VALUES (2);
352 INSERT INTO clstr_3 VALUES (1);
353 -- this user can only cluster clstr_1 and clstr_3, but the latter
354 -- has not been clustered
355 SET SESSION AUTHORIZATION regress_clstr_user;
356 SET client_min_messages = ERROR; -- order of "skipping" warnings may vary
357 CLUSTER;
358 RESET client_min_messages;
359 SELECT * FROM clstr_1 UNION ALL
360 SELECT * FROM clstr_2 UNION ALL
361 SELECT * FROM clstr_3;
362 a
363 ---
364 1
365 2
366 2
367 1
368 2
369 1
370 (6 rows)
372 -- cluster a single table using the indisclustered bit previously set
373 DELETE FROM clstr_1;
374 INSERT INTO clstr_1 VALUES (2);
375 INSERT INTO clstr_1 VALUES (1);
376 CLUSTER clstr_1;
377 SELECT * FROM clstr_1;
378 a
379 ---
380 1
381 2
382 (2 rows)
384 -- Test MVCC-safety of cluster. There isn't much we can do to verify the
385 -- results with a single backend...
386 CREATE TABLE clustertest (key int PRIMARY KEY);
387 INSERT INTO clustertest VALUES (10);
388 INSERT INTO clustertest VALUES (20);
389 INSERT INTO clustertest VALUES (30);
390 INSERT INTO clustertest VALUES (40);
391 INSERT INTO clustertest VALUES (50);
392 -- Use a transaction so that updates are not committed when CLUSTER sees 'em
393 BEGIN;
394 -- Test update where the old row version is found first in the scan
395 UPDATE clustertest SET key = 100 WHERE key = 10;
396 -- Test update where the new row version is found first in the scan
397 UPDATE clustertest SET key = 35 WHERE key = 40;
398 -- Test longer update chain
399 UPDATE clustertest SET key = 60 WHERE key = 50;
400 UPDATE clustertest SET key = 70 WHERE key = 60;
401 UPDATE clustertest SET key = 80 WHERE key = 70;
402 SELECT * FROM clustertest;
403 key
404 -----
405 20
406 30
407 100
408 35
409 80
410 (5 rows)
412 CLUSTER clustertest_pkey ON clustertest;
413 SELECT * FROM clustertest;
414 key
415 -----
416 20
417 30
418 35
419 80
420 100
421 (5 rows)
423 COMMIT;
424 SELECT * FROM clustertest;
425 key
426 -----
427 20
428 30
429 35
430 80
431 100
432 (5 rows)
434 -- check that temp tables can be clustered
435 create temp table clstr_temp (col1 int primary key, col2 text);
436 insert into clstr_temp values (2, 'two'), (1, 'one');
437 cluster clstr_temp using clstr_temp_pkey;
438 select * from clstr_temp;
439 col1 | col2
440 ------+------
441 1 | one
442 2 | two
443 (2 rows)
445 drop table clstr_temp;
446 RESET SESSION AUTHORIZATION;
447 -- check clustering an empty table
448 DROP TABLE clustertest;
449 CREATE TABLE clustertest (f1 int PRIMARY KEY);
450 CLUSTER clustertest USING clustertest_pkey;
451 CLUSTER clustertest;
452 -- Check that partitioned tables can be clustered
453 CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
454 CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1) TO (10) PARTITION BY RANGE (a);
455 CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1) TO (5);
456 CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (5) TO (10) PARTITION BY RANGE (a);
457 CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (10) TO (20);
458 CREATE TABLE clstrpart3 PARTITION OF clstrpart DEFAULT PARTITION BY RANGE (a);
459 CREATE TABLE clstrpart33 PARTITION OF clstrpart3 DEFAULT;
460 CREATE INDEX clstrpart_only_idx ON ONLY clstrpart (a);
461 CLUSTER clstrpart USING clstrpart_only_idx; -- fails
462 ERROR: cannot cluster on invalid index "clstrpart_only_idx"
463 DROP INDEX clstrpart_only_idx;
464 CREATE INDEX clstrpart_idx ON clstrpart (a);
465 -- Check that clustering sets new relfilenodes:
466 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 ;
467 CLUSTER clstrpart USING clstrpart_idx;
468 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 ;
469 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";
470 relname | level | relkind | ?column?
471 -------------+-------+---------+----------
472 clstrpart | 0 | p | t
473 clstrpart1 | 1 | p | t
474 clstrpart11 | 2 | r | f
475 clstrpart12 | 2 | p | t
476 clstrpart2 | 1 | r | f
477 clstrpart3 | 1 | p | t
478 clstrpart33 | 2 | r | f
479 (7 rows)
481 -- Partitioned indexes aren't and can't be marked un/clustered:
482 \d clstrpart
483 Partitioned table "public.clstrpart"
484 Column | Type | Collation | Nullable | Default
485 --------+---------+-----------+----------+---------
486 a | integer | | |
487 Partition key: RANGE (a)
488 Indexes:
489 "clstrpart_idx" btree (a)
490 Number of partitions: 3 (Use \d+ to list them.)
492 CLUSTER clstrpart;
493 ERROR: there is no previously clustered index for table "clstrpart"
494 ALTER TABLE clstrpart SET WITHOUT CLUSTER;
495 ERROR: cannot mark index clustered in partitioned table
496 ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
497 ERROR: cannot mark index clustered in partitioned table
498 DROP TABLE clstrpart;
499 -- Ownership of partitions is checked
500 CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
501 CREATE INDEX ptnowner_i_idx ON ptnowner(i);
502 CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);
503 CREATE ROLE regress_ptnowner;
504 CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);
505 ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
506 SET SESSION AUTHORIZATION regress_ptnowner;
507 CLUSTER ptnowner USING ptnowner_i_idx;
508 ERROR: permission denied for table ptnowner
509 RESET SESSION AUTHORIZATION;
510 ALTER TABLE ptnowner OWNER TO regress_ptnowner;
511 CREATE TEMP TABLE ptnowner_oldnodes AS
512 SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
513 JOIN pg_class AS c ON c.oid=tree.relid;
514 SET SESSION AUTHORIZATION regress_ptnowner;
515 CLUSTER ptnowner USING ptnowner_i_idx;
516 WARNING: permission denied to cluster "ptnowner2", skipping it
517 RESET SESSION AUTHORIZATION;
518 SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
519 JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";
520 relname | ?column?
521 -----------+----------
522 ptnowner | t
523 ptnowner1 | f
524 ptnowner2 | t
525 (3 rows)
527 DROP TABLE ptnowner;
528 DROP ROLE regress_ptnowner;
529 -- Test CLUSTER with external tuplesorting
530 create table clstr_4 as select * from tenk1;
531 create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
532 -- ensure we don't use the index in CLUSTER nor the checking SELECTs
533 set enable_indexscan = off;
534 -- Use external sort:
535 set maintenance_work_mem = '1MB';
536 cluster clstr_4 using cluster_sort;
537 select * from
538 (select hundred, lag(hundred) over () as lhundred,
539 thousand, lag(thousand) over () as lthousand,
540 tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss
541 where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
542 hundred | lhundred | thousand | lthousand | tenthous | ltenthous
543 ---------+----------+----------+-----------+----------+-----------
544 (0 rows)
546 reset enable_indexscan;
547 reset maintenance_work_mem;
548 -- test CLUSTER on expression index
549 CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
550 INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i);
551 CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
552 CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));
553 -- verify indexes work before cluster
554 BEGIN;
555 SET LOCAL enable_seqscan = false;
556 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
557 QUERY PLAN
558 ---------------------------------------------------------------
559 Index Scan using clstr_expression_upper_b on clstr_expression
560 Index Cond: (upper(b) = 'PREFIX3'::text)
561 (2 rows)
563 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
564 id | a | b
565 ----+---+---------
566 3 | 3 | prefix3
567 (1 row)
569 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
570 QUERY PLAN
571 ---------------------------------------------------------------
572 Index Scan using clstr_expression_minus_a on clstr_expression
573 Index Cond: ((- a) = '-3'::integer)
574 (2 rows)
576 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
577 id | a | b
578 -----+---+-----------
579 129 | 3 | prefix129
580 3 | 3 | prefix3
581 45 | 3 | prefix45
582 87 | 3 | prefix87
583 (4 rows)
585 COMMIT;
586 -- and after clustering on clstr_expression_minus_a
587 CLUSTER clstr_expression USING clstr_expression_minus_a;
588 WITH rows AS
589 (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression)
590 SELECT * FROM rows WHERE la < a;
591 ctid | la | a
592 ------+----+---
593 (0 rows)
595 BEGIN;
596 SET LOCAL enable_seqscan = false;
597 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
598 QUERY PLAN
599 ---------------------------------------------------------------
600 Index Scan using clstr_expression_upper_b on clstr_expression
601 Index Cond: (upper(b) = 'PREFIX3'::text)
602 (2 rows)
604 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
605 id | a | b
606 ----+---+---------
607 3 | 3 | prefix3
608 (1 row)
610 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
611 QUERY PLAN
612 ---------------------------------------------------------------
613 Index Scan using clstr_expression_minus_a on clstr_expression
614 Index Cond: ((- a) = '-3'::integer)
615 (2 rows)
617 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
618 id | a | b
619 -----+---+-----------
620 129 | 3 | prefix129
621 3 | 3 | prefix3
622 45 | 3 | prefix45
623 87 | 3 | prefix87
624 (4 rows)
626 COMMIT;
627 -- and after clustering on clstr_expression_upper_b
628 CLUSTER clstr_expression USING clstr_expression_upper_b;
629 WITH rows AS
630 (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression)
631 SELECT * FROM rows WHERE upper(lb) > upper(b);
632 ctid | lb | b
633 ------+----+---
634 (0 rows)
636 BEGIN;
637 SET LOCAL enable_seqscan = false;
638 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
639 QUERY PLAN
640 ---------------------------------------------------------------
641 Index Scan using clstr_expression_upper_b on clstr_expression
642 Index Cond: (upper(b) = 'PREFIX3'::text)
643 (2 rows)
645 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
646 id | a | b
647 ----+---+---------
648 3 | 3 | prefix3
649 (1 row)
651 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
652 QUERY PLAN
653 ---------------------------------------------------------------
654 Index Scan using clstr_expression_minus_a on clstr_expression
655 Index Cond: ((- a) = '-3'::integer)
656 (2 rows)
658 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
659 id | a | b
660 -----+---+-----------
661 129 | 3 | prefix129
662 3 | 3 | prefix3
663 45 | 3 | prefix45
664 87 | 3 | prefix87
665 (4 rows)
667 COMMIT;
668 -- clean up
669 DROP TABLE clustertest;
670 DROP TABLE clstr_1;
671 DROP TABLE clstr_2;
672 DROP TABLE clstr_3;
673 DROP TABLE clstr_4;
674 DROP TABLE clstr_expression;
675 DROP USER regress_clstr_user;