-- -- 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 " 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;