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