1-- 2-- HASH_INDEX 3-- grep 843938989 hash.data 4-- 5 6SELECT * FROM hash_i4_heap 7 WHERE hash_i4_heap.random = 843938989; 8 9-- 10-- hash index 11-- grep 66766766 hash.data 12-- 13SELECT * FROM hash_i4_heap 14 WHERE hash_i4_heap.random = 66766766; 15 16-- 17-- hash index 18-- grep 1505703298 hash.data 19-- 20SELECT * FROM hash_name_heap 21 WHERE hash_name_heap.random = '1505703298'::name; 22 23-- 24-- hash index 25-- grep 7777777 hash.data 26-- 27SELECT * FROM hash_name_heap 28 WHERE hash_name_heap.random = '7777777'::name; 29 30-- 31-- hash index 32-- grep 1351610853 hash.data 33-- 34SELECT * FROM hash_txt_heap 35 WHERE hash_txt_heap.random = '1351610853'::text; 36 37-- 38-- hash index 39-- grep 111111112222222233333333 hash.data 40-- 41SELECT * FROM hash_txt_heap 42 WHERE hash_txt_heap.random = '111111112222222233333333'::text; 43 44-- 45-- hash index 46-- grep 444705537 hash.data 47-- 48SELECT * FROM hash_f8_heap 49 WHERE hash_f8_heap.random = '444705537'::float8; 50 51-- 52-- hash index 53-- grep 88888888 hash.data 54-- 55SELECT * FROM hash_f8_heap 56 WHERE hash_f8_heap.random = '88888888'::float8; 57 58-- 59-- hash index 60-- grep '^90[^0-9]' hashovfl.data 61-- 62-- SELECT count(*) AS i988 FROM hash_ovfl_heap 63-- WHERE x = 90; 64 65-- 66-- hash index 67-- grep '^1000[^0-9]' hashovfl.data 68-- 69-- SELECT count(*) AS i0 FROM hash_ovfl_heap 70-- WHERE x = 1000; 71 72-- 73-- HASH 74-- 75UPDATE hash_i4_heap 76 SET random = 1 77 WHERE hash_i4_heap.seqno = 1492; 78 79SELECT h.seqno AS i1492, h.random AS i1 80 FROM hash_i4_heap h 81 WHERE h.random = 1; 82 83UPDATE hash_i4_heap 84 SET seqno = 20000 85 WHERE hash_i4_heap.random = 1492795354; 86 87SELECT h.seqno AS i20000 88 FROM hash_i4_heap h 89 WHERE h.random = 1492795354; 90 91UPDATE hash_name_heap 92 SET random = '0123456789abcdef'::name 93 WHERE hash_name_heap.seqno = 6543; 94 95SELECT h.seqno AS i6543, h.random AS c0_to_f 96 FROM hash_name_heap h 97 WHERE h.random = '0123456789abcdef'::name; 98 99UPDATE hash_name_heap 100 SET seqno = 20000 101 WHERE hash_name_heap.random = '76652222'::name; 102 103-- 104-- this is the row we just replaced; index scan should return zero rows 105-- 106SELECT h.seqno AS emptyset 107 FROM hash_name_heap h 108 WHERE h.random = '76652222'::name; 109 110UPDATE hash_txt_heap 111 SET random = '0123456789abcdefghijklmnop'::text 112 WHERE hash_txt_heap.seqno = 4002; 113 114SELECT h.seqno AS i4002, h.random AS c0_to_p 115 FROM hash_txt_heap h 116 WHERE h.random = '0123456789abcdefghijklmnop'::text; 117 118UPDATE hash_txt_heap 119 SET seqno = 20000 120 WHERE hash_txt_heap.random = '959363399'::text; 121 122SELECT h.seqno AS t20000 123 FROM hash_txt_heap h 124 WHERE h.random = '959363399'::text; 125 126UPDATE hash_f8_heap 127 SET random = '-1234.1234'::float8 128 WHERE hash_f8_heap.seqno = 8906; 129 130SELECT h.seqno AS i8096, h.random AS f1234_1234 131 FROM hash_f8_heap h 132 WHERE h.random = '-1234.1234'::float8; 133 134UPDATE hash_f8_heap 135 SET seqno = 20000 136 WHERE hash_f8_heap.random = '488912369'::float8; 137 138SELECT h.seqno AS f20000 139 FROM hash_f8_heap h 140 WHERE h.random = '488912369'::float8; 141 142-- UPDATE hash_ovfl_heap 143-- SET x = 1000 144-- WHERE x = 90; 145 146-- this vacuums the index as well 147-- VACUUM hash_ovfl_heap; 148 149-- SELECT count(*) AS i0 FROM hash_ovfl_heap 150-- WHERE x = 90; 151 152-- SELECT count(*) AS i988 FROM hash_ovfl_heap 153-- WHERE x = 1000; 154 155-- 156-- Cause some overflow insert and splits. 157-- 158CREATE TABLE hash_split_heap (keycol INT); 159INSERT INTO hash_split_heap SELECT 1 FROM generate_series(1, 500) a; 160CREATE INDEX hash_split_index on hash_split_heap USING HASH (keycol); 161INSERT INTO hash_split_heap SELECT 1 FROM generate_series(1, 5000) a; 162 163-- Let's do a backward scan. 164BEGIN; 165SET enable_seqscan = OFF; 166SET enable_bitmapscan = OFF; 167 168DECLARE c CURSOR FOR SELECT * from hash_split_heap WHERE keycol = 1; 169MOVE FORWARD ALL FROM c; 170MOVE BACKWARD 10000 FROM c; 171MOVE BACKWARD ALL FROM c; 172CLOSE c; 173END; 174 175-- DELETE, INSERT, VACUUM. 176DELETE FROM hash_split_heap WHERE keycol = 1; 177INSERT INTO hash_split_heap SELECT a/2 FROM generate_series(1, 25000) a; 178 179VACUUM hash_split_heap; 180 181-- Rebuild the index using a different fillfactor 182ALTER INDEX hash_split_index SET (fillfactor = 10); 183REINDEX INDEX hash_split_index; 184 185-- Clean up. 186DROP TABLE hash_split_heap; 187 188-- Index on temp table. 189CREATE TEMP TABLE hash_temp_heap (x int, y int); 190INSERT INTO hash_temp_heap VALUES (1,1); 191CREATE INDEX hash_idx ON hash_temp_heap USING hash (x); 192DROP TABLE hash_temp_heap CASCADE; 193 194-- Float4 type. 195CREATE TABLE hash_heap_float4 (x float4, y int); 196INSERT INTO hash_heap_float4 VALUES (1.1,1); 197CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x); 198DROP TABLE hash_heap_float4 CASCADE; 199 200-- Test out-of-range fillfactor values 201CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) 202 WITH (fillfactor=9); 203CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) 204 WITH (fillfactor=101); 205