| blob | e46a66952f0c408c98c67f4096df499653b92b1d |
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_con
253 clstr_tst_pkey
254 (2 rows)
256 SELECT relname, relkind,
257 EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
258 FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname;
259 relname | relkind | hastoast
260 ----------------------+---------+----------
261 clstr_tst | r | t
262 clstr_tst_a_seq | S | f
263 clstr_tst_b | i | f
264 clstr_tst_b_c | i | f
265 clstr_tst_c | i | f
266 clstr_tst_c_b | i | f
267 clstr_tst_inh | r | t
268 clstr_tst_pkey | i | f
269 clstr_tst_s | r | f
270 clstr_tst_s_pkey | i | f
271 clstr_tst_s_rf_a_seq | S | f
272 (11 rows)
274 -- Verify that indisclustered is correctly set
275 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
276 WHERE pg_class.oid=indexrelid
277 AND indrelid=pg_class_2.oid
278 AND pg_class_2.relname = 'clstr_tst'
279 AND indisclustered;
280 relname
281 -------------
282 clstr_tst_c
283 (1 row)
285 -- Try changing indisclustered
286 ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
287 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
288 WHERE pg_class.oid=indexrelid
289 AND indrelid=pg_class_2.oid
290 AND pg_class_2.relname = 'clstr_tst'
291 AND indisclustered;
292 relname
293 ---------------
294 clstr_tst_b_c
295 (1 row)
297 -- Try turning off all clustering
298 ALTER TABLE clstr_tst SET WITHOUT CLUSTER;
299 SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
300 WHERE pg_class.oid=indexrelid
301 AND indrelid=pg_class_2.oid
302 AND pg_class_2.relname = 'clstr_tst'
303 AND indisclustered;
304 relname
305 ---------
306 (0 rows)
308 -- Verify that clustering all tables does in fact cluster the right ones
309 CREATE USER regress_clstr_user;
310 CREATE TABLE clstr_1 (a INT PRIMARY KEY);
311 CREATE TABLE clstr_2 (a INT PRIMARY KEY);
312 CREATE TABLE clstr_3 (a INT PRIMARY KEY);
313 ALTER TABLE clstr_1 OWNER TO regress_clstr_user;
314 ALTER TABLE clstr_3 OWNER TO regress_clstr_user;
315 GRANT SELECT ON clstr_2 TO regress_clstr_user;
316 INSERT INTO clstr_1 VALUES (2);
317 INSERT INTO clstr_1 VALUES (1);
318 INSERT INTO clstr_2 VALUES (2);
319 INSERT INTO clstr_2 VALUES (1);
320 INSERT INTO clstr_3 VALUES (2);
321 INSERT INTO clstr_3 VALUES (1);
322 -- "CLUSTER <tablename>" on a table that hasn't been clustered
323 CLUSTER clstr_2;
324 ERROR: there is no previously clustered index for table "clstr_2"
325 CLUSTER clstr_1_pkey ON clstr_1;
326 CLUSTER clstr_2 USING clstr_2_pkey;
327 SELECT * FROM clstr_1 UNION ALL
328 SELECT * FROM clstr_2 UNION ALL
329 SELECT * FROM clstr_3;
330 a
331 ---
332 1
333 2
334 1
335 2
336 2
337 1
338 (6 rows)
340 -- revert to the original state
341 DELETE FROM clstr_1;
342 DELETE FROM clstr_2;
343 DELETE FROM clstr_3;
344 INSERT INTO clstr_1 VALUES (2);
345 INSERT INTO clstr_1 VALUES (1);
346 INSERT INTO clstr_2 VALUES (2);
347 INSERT INTO clstr_2 VALUES (1);
348 INSERT INTO clstr_3 VALUES (2);
349 INSERT INTO clstr_3 VALUES (1);
350 -- this user can only cluster clstr_1 and clstr_3, but the latter
351 -- has not been clustered
352 SET SESSION AUTHORIZATION regress_clstr_user;
353 CLUSTER;
354 SELECT * FROM clstr_1 UNION ALL
355 SELECT * FROM clstr_2 UNION ALL
356 SELECT * FROM clstr_3;
357 a
358 ---
359 1
360 2
361 2
362 1
363 2
364 1
365 (6 rows)
367 -- cluster a single table using the indisclustered bit previously set
368 DELETE FROM clstr_1;
369 INSERT INTO clstr_1 VALUES (2);
370 INSERT INTO clstr_1 VALUES (1);
371 CLUSTER clstr_1;
372 SELECT * FROM clstr_1;
373 a
374 ---
375 1
376 2
377 (2 rows)
379 -- Test MVCC-safety of cluster. There isn't much we can do to verify the
380 -- results with a single backend...
381 CREATE TABLE clustertest (key int PRIMARY KEY);
382 INSERT INTO clustertest VALUES (10);
383 INSERT INTO clustertest VALUES (20);
384 INSERT INTO clustertest VALUES (30);
385 INSERT INTO clustertest VALUES (40);
386 INSERT INTO clustertest VALUES (50);
387 -- Use a transaction so that updates are not committed when CLUSTER sees 'em
388 BEGIN;
389 -- Test update where the old row version is found first in the scan
390 UPDATE clustertest SET key = 100 WHERE key = 10;
391 -- Test update where the new row version is found first in the scan
392 UPDATE clustertest SET key = 35 WHERE key = 40;
393 -- Test longer update chain
394 UPDATE clustertest SET key = 60 WHERE key = 50;
395 UPDATE clustertest SET key = 70 WHERE key = 60;
396 UPDATE clustertest SET key = 80 WHERE key = 70;
397 SELECT * FROM clustertest;
398 key
399 -----
400 20
401 30
402 100
403 35
404 80
405 (5 rows)
407 CLUSTER clustertest_pkey ON clustertest;
408 SELECT * FROM clustertest;
409 key
410 -----
411 20
412 30
413 35
414 80
415 100
416 (5 rows)
418 COMMIT;
419 SELECT * FROM clustertest;
420 key
421 -----
422 20
423 30
424 35
425 80
426 100
427 (5 rows)
429 -- check that temp tables can be clustered
430 create temp table clstr_temp (col1 int primary key, col2 text);
431 insert into clstr_temp values (2, 'two'), (1, 'one');
432 cluster clstr_temp using clstr_temp_pkey;
433 select * from clstr_temp;
434 col1 | col2
435 ------+------
436 1 | one
437 2 | two
438 (2 rows)
440 drop table clstr_temp;
441 RESET SESSION AUTHORIZATION;
442 -- check clustering an empty table
443 DROP TABLE clustertest;
444 CREATE TABLE clustertest (f1 int PRIMARY KEY);
445 CLUSTER clustertest USING clustertest_pkey;
446 CLUSTER clustertest;
447 -- Check that partitioned tables cannot be clustered
448 CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
449 CREATE INDEX clstrpart_idx ON clstrpart (a);
450 ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
451 ERROR: cannot mark index clustered in partitioned table
452 CLUSTER clstrpart USING clstrpart_idx;
453 ERROR: cannot cluster a partitioned table
454 DROP TABLE clstrpart;
455 -- Test CLUSTER with external tuplesorting
456 create table clstr_4 as select * from tenk1;
457 create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
458 -- ensure we don't use the index in CLUSTER nor the checking SELECTs
459 set enable_indexscan = off;
460 -- Use external sort:
461 set maintenance_work_mem = '1MB';
462 cluster clstr_4 using cluster_sort;
463 select * from
464 (select hundred, lag(hundred) over () as lhundred,
465 thousand, lag(thousand) over () as lthousand,
466 tenthous, lag(tenthous) over () as ltenthous from clstr_4) ss
467 where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
468 hundred | lhundred | thousand | lthousand | tenthous | ltenthous
469 ---------+----------+----------+-----------+----------+-----------
470 (0 rows)
472 reset enable_indexscan;
473 reset maintenance_work_mem;
474 -- test CLUSTER on expression index
475 CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
476 INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i);
477 CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
478 CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));
479 -- verify indexes work before cluster
480 BEGIN;
481 SET LOCAL enable_seqscan = false;
482 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
483 QUERY PLAN
484 ---------------------------------------------------------------
485 Index Scan using clstr_expression_upper_b on clstr_expression
486 Index Cond: (upper(b) = 'PREFIX3'::text)
487 (2 rows)
489 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
490 id | a | b
491 ----+---+---------
492 3 | 3 | prefix3
493 (1 row)
495 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
496 QUERY PLAN
497 ---------------------------------------------------------------
498 Index Scan using clstr_expression_minus_a on clstr_expression
499 Index Cond: ((- a) = '-3'::integer)
500 (2 rows)
502 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
503 id | a | b
504 -----+---+-----------
505 129 | 3 | prefix129
506 3 | 3 | prefix3
507 45 | 3 | prefix45
508 87 | 3 | prefix87
509 (4 rows)
511 COMMIT;
512 -- and after clustering on clstr_expression_minus_a
513 CLUSTER clstr_expression USING clstr_expression_minus_a;
514 BEGIN;
515 SET LOCAL enable_seqscan = false;
516 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
517 QUERY PLAN
518 ---------------------------------------------------------------
519 Index Scan using clstr_expression_upper_b on clstr_expression
520 Index Cond: (upper(b) = 'PREFIX3'::text)
521 (2 rows)
523 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
524 id | a | b
525 ----+---+---------
526 3 | 3 | prefix3
527 (1 row)
529 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
530 QUERY PLAN
531 ---------------------------------------------------------------
532 Index Scan using clstr_expression_minus_a on clstr_expression
533 Index Cond: ((- a) = '-3'::integer)
534 (2 rows)
536 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
537 id | a | b
538 -----+---+-----------
539 129 | 3 | prefix129
540 3 | 3 | prefix3
541 45 | 3 | prefix45
542 87 | 3 | prefix87
543 (4 rows)
545 COMMIT;
546 -- and after clustering on clstr_expression_upper_b
547 CLUSTER clstr_expression USING clstr_expression_upper_b;
548 BEGIN;
549 SET LOCAL enable_seqscan = false;
550 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
551 QUERY PLAN
552 ---------------------------------------------------------------
553 Index Scan using clstr_expression_upper_b on clstr_expression
554 Index Cond: (upper(b) = 'PREFIX3'::text)
555 (2 rows)
557 SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
558 id | a | b
559 ----+---+---------
560 3 | 3 | prefix3
561 (1 row)
563 EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
564 QUERY PLAN
565 ---------------------------------------------------------------
566 Index Scan using clstr_expression_minus_a on clstr_expression
567 Index Cond: ((- a) = '-3'::integer)
568 (2 rows)
570 SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
571 id | a | b
572 -----+---+-----------
573 129 | 3 | prefix129
574 3 | 3 | prefix3
575 45 | 3 | prefix45
576 87 | 3 | prefix87
577 (4 rows)
579 COMMIT;
580 -- clean up
581 DROP TABLE clustertest;
582 DROP TABLE clstr_1;
583 DROP TABLE clstr_2;
584 DROP TABLE clstr_3;
585 DROP TABLE clstr_4;
586 DROP TABLE clstr_expression;
587 DROP USER regress_clstr_user;