SET client_min_messages = warning; \set ECHO none RESET client_min_messages; CREATE TABLE gevelt ( t box ); \copy gevelt from 'data/rect.data' SELECT center(t) AS p INTO gevelp FROM gevelt; CREATE INDEX gist_idx ON gevelt USING gist ( t ); CREATE INDEX spgist_idx ON gevelp USING spgist ( p ); CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops); --GiST SELECT gist_stat('gist_idx'); gist_stat ----------------------------------------- Number of levels: 2 + Number of pages: 29 + Number of leaf pages: 28 + Number of tuples: 3406 + Number of invalid tuples: 0 + Number of leaf tuples: 3378 + Total size of tuples: 143516 bytes+ Total size of leaf tuples: 142296 bytes+ Total size of index: 237568 bytes+ (1 row) SELECT gist_tree('gist_idx'); gist_tree ----------------------------------------------------------------------------------------------- 0(l:0) blk: 0 numTuple: 28 free: 6940b(14.95%) rightlink:4294967295 (InvalidBlockNumber) + 1(l:1) blk: 2 numTuple: 278 free: 2588b(68.28%) rightlink:4294967295 (InvalidBlockNumber)+ 2(l:1) blk: 19 numTuple: 123 free: 2736b(66.47%) rightlink:3 (OK) + 3(l:1) blk: 8 numTuple: 128 free: 2516b(69.17%) rightlink:22 (OK) + 4(l:1) blk: 4 numTuple: 92 free: 4100b(49.75%) rightlink:23 (OK) + 5(l:1) blk: 20 numTuple: 122 free: 2780b(65.93%) rightlink:7 (OK) + 6(l:1) blk: 15 numTuple: 127 free: 2560b(68.63%) rightlink:11 (OK) + 7(l:1) blk: 18 numTuple: 138 free: 2076b(74.56%) rightlink:9 (OK) + 8(l:1) blk: 23 numTuple: 126 free: 2604b(68.09%) rightlink:8 (OK) + 9(l:1) blk: 10 numTuple: 156 free: 1284b(84.26%) rightlink:19 (OK) + 10(l:1) blk: 13 numTuple: 130 free: 2428b(70.25%) rightlink:20 (OK) + 11(l:1) blk: 12 numTuple: 70 free: 5068b(37.89%) rightlink:24 (OK) + 12(l:1) blk: 22 numTuple: 112 free: 3220b(60.54%) rightlink:10 (OK) + 13(l:1) blk: 16 numTuple: 89 free: 4232b(48.14%) rightlink:26 (OK) + 14(l:1) blk: 26 numTuple: 108 free: 3396b(58.38%) rightlink:5 (OK) + 15(l:1) blk: 7 numTuple: 99 free: 3792b(53.53%) rightlink:16 (OK) + 16(l:1) blk: 6 numTuple: 120 free: 2868b(64.85%) rightlink:18 (OK) + 17(l:1) blk: 14 numTuple: 163 free: 976b(88.04%) rightlink:6 (OK) + 18(l:1) blk: 28 numTuple: 122 free: 2780b(65.93%) rightlink:12 (OK) + 19(l:1) blk: 3 numTuple: 57 free: 5640b(30.88%) rightlink:27 (OK) + 20(l:1) blk: 25 numTuple: 58 free: 5596b(31.42%) rightlink:15 (OK) + 21(l:1) blk: 17 numTuple: 87 free: 4320b(47.06%) rightlink:4 (OK) + 22(l:1) blk: 9 numTuple: 128 free: 2516b(69.17%) rightlink:25 (OK) + 23(l:1) blk: 21 numTuple: 166 free: 844b(89.66%) rightlink:13 (OK) + 24(l:1) blk: 27 numTuple: 124 free: 2692b(67.01%) rightlink:14 (OK) + 25(l:1) blk: 11 numTuple: 146 free: 1724b(78.87%) rightlink:2 (OK) + 26(l:1) blk: 5 numTuple: 61 free: 5464b(33.04%) rightlink:28 (OK) + 27(l:1) blk: 1 numTuple: 92 free: 4100b(49.75%) rightlink:21 (OK) + 28(l:1) blk: 24 numTuple: 156 free: 1284b(84.26%) rightlink:17 (OK) + (1 row) SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where level=1; level | valid | a -------+-------+----------------------------- 1 | t | 1 | t | (32261,50040),(20397,42779) 1 | t | (20400,41715),(9175,31485) 1 | t | (8924,39250),(3,31083) 1 | t | (20594,19151),(10869,10535) 1 | t | (49981,42533),(43290,28026) 1 | t | (49999,27727),(40603,15778) 1 | t | (8993,50012),(64,39781) 1 | t | (32394,42395),(20513,31557) 1 | t | (20715,10161),(10407,51) 1 | t | (32512,16364),(20947,11843) 1 | t | (20327,49975),(9216,42130) 1 | t | (13397,31188),(7334,19288) 1 | t | (20856,31294),(13642,19263) 1 | t | (6986,30859),(107,19466) 1 | t | (40397,28538),(32515,15759) 1 | t | (49985,15380),(42047,43) 1 | t | (32715,11509),(24675,6) 1 | t | (41916,5154),(32914,357) 1 | t | (43056,42360),(32661,37729) 1 | t | (32440,31262),(21146,25161) 1 | t | (42941,37586),(32606,28547) 1 | t | (10104,18999),(15,7761) 1 | t | (41835,15752),(32828,5360) 1 | t | (50027,50073),(32453,42694) 1 | t | (24423,11473),(20828,181) 1 | t | (10146,7495),(138,81) 1 | t | (32705,25105),(20838,16626) (28 rows) --SPGiST SELECT spgist_stat('spgist_idx'); spgist_stat ---------------------------------- totalPages: 21 + deletedPages: 0 + innerPages: 2 + leafPages: 19 + emptyPages: 0 + usedSpace: 126.70 kbytes+ usedInnerSpace: 1.17 kbytes + usedLeafSpace: 125.53 kbytes+ freeSpace: 40.64 kbytes + fillRatio: 75.71% + leafTuples: 3947 + innerTuples: 20 + innerAllTheSame: 0 + leafPlaceholders: 569 + innerPlaceholders: 0 + leafRedirects: 0 + innerRedirects: 0 (1 row) SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, allthesame bool, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point); tid | allthesame | node_n | level | tid_pointer | prefix | node_label | leaf_value ----------+------------+--------+-------+-------------+---------+------------+------------------- (1,1) | f | 0 | 1 | (5,1) | 23030 | | (1,1) | f | 1 | 1 | (5,2) | 23030 | | (5,2) | f | 0 | 2 | (5,6) | 21664 | | (5,2) | f | 1 | 2 | (5,3) | 21664 | | (5,3) | f | 0 | 3 | (5,5) | 37159.5 | | (5,3) | f | 1 | 3 | (5,12) | 37159.5 | | (5,12) | f | 0 | 4 | (5,13) | 36357.5 | | (5,12) | f | 1 | 4 | (14,226) | 36357.5 | | (14,226) | | | 5 | | | | (43240,47256.5) (5,13) | f | 0 | 5 | | 43507 | | (5,13) | f | 1 | 5 | | 43507 | | (5,25) | f | 0 | 6 | | 28862 | | (5,25) | f | 1 | 6 | | 28862 | | (22,115) | | | 7 | | | | (30902,44532) (22,116) | | | 7 | | | | (23048,47416) (3,112) | | | 6 | | | | (35218.5,42124) (5,5) | f | 0 | 4 | | 33416 | | (5,5) | f | 1 | 4 | | 33416 | | (5,22) | f | 0 | 5 | | 30991 | | (5,22) | f | 1 | 5 | | 30991 | | (5,23) | f | 0 | 6 | | 41820 | | (5,23) | f | 1 | 6 | | 41820 | | (9,114) | | | 7 | | | | (44732,32182) (9,112) | | | 7 | | | | (35580,33526.5) (20,225) | | | 6 | | | | (47724.5,27185.5) (5,11) | f | 0 | 5 | | 29986.5 | | (5,11) | f | 1 | 5 | | 29986.5 | | (13,100) | | | 6 | | | | (24069,30850.5) (13,101) | | | 6 | | | | (29539,25566) (5,6) | f | 0 | 3 | | 36774 | | (5,6) | f | 1 | 3 | | 36774 | | (5,19) | f | 0 | 4 | | 10075.5 | | (5,19) | f | 1 | 4 | | 10075.5 | | (18,225) | | | 5 | | | | (20920.5,49105.5) (5,20) | f | 0 | 5 | | 44171.5 | | (5,20) | f | 1 | 5 | | 44171.5 | | (10,113) | | | 6 | | | | (93,46797) (10,110) | | | 6 | | | | (28.5,38640.5) (5,7) | f | 0 | 4 | | 9517 | | (5,7) | f | 1 | 4 | | 9517 | | (5,21) | f | 0 | 5 | | 28907.5 | | (5,21) | f | 1 | 5 | | 28907.5 | | (19,112) | | | 6 | | | | (11916.5,31668) (19,115) | | | 6 | | | | (20622.5,27462.5) (7,113) | | | 5 | | | | (9296,35157) (5,1) | f | 0 | 2 | | 26938 | | (5,1) | f | 1 | 2 | | 26938 | | (5,4) | f | 0 | 3 | | 9532 | | (5,4) | f | 1 | 3 | | 9532 | | (5,14) | f | 0 | 4 | | 38603 | | (5,14) | f | 1 | 4 | | 38603 | | (15,222) | | | 5 | | | | (41926.5,17934.5) (5,15) | f | 0 | 5 | | 16345 | | (5,15) | f | 1 | 5 | | 16345 | | (8,112) | | | 6 | | | | (32425,20702.5) (8,113) | | | 6 | | | | (29134,15559.5) (5,10) | f | 0 | 4 | | 38800.5 | | (5,10) | f | 1 | 4 | | 38800.5 | | (5,24) | f | 0 | 5 | | 4752 | | (5,24) | f | 1 | 5 | | 4752 | | (21,107) | | | 6 | | | | (49822.5,7097.5) (21,108) | | | 6 | | | | (40315.5,1689.5) (12,94) | | | 5 | | | | (30295.5,5090) (5,8) | f | 0 | 3 | | 11733.5 | | (5,8) | f | 1 | 3 | | 11733.5 | | (5,9) | f | 0 | 4 | | 11993 | | (5,9) | f | 1 | 4 | | 11993 | | (5,16) | f | 0 | 5 | | 17591 | | (5,16) | f | 1 | 5 | | 17591 | | (16,127) | | | 6 | | | | (18352.5,19366) (16,123) | | | 6 | | | | (24795,14921) (6,114) | | | 5 | | | | (6706,16676) (5,17) | f | 0 | 4 | | 13329.5 | | (5,17) | f | 1 | 4 | | 13329.5 | | (17,226) | | | 5 | | | | (23690,10214.5) (5,18) | f | 0 | 5 | | 6375 | | (5,18) | f | 1 | 5 | | 6375 | | (11,109) | | | 6 | | | | (5501.5,9916) (11,113) | | | 6 | | | | (1072.5,4752) (79 rows) SELECT * FROM spgist_print('spgist_idx') as t(tid tid, allthesame bool, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1; tid | allthesame | node_n | level | tid_pointer | prefix | node_label | leaf_value -------+------------+--------+-------+-------------+-------------------------------------+------------+------------ (1,1) | f | 0 | 1 | | (24530.2070484581,23595.7092511013) | | (1,1) | f | 1 | 1 | | (24530.2070484581,23595.7092511013) | | (1,1) | f | 2 | 1 | | (24530.2070484581,23595.7092511013) | | (1,1) | f | 3 | 1 | | (24530.2070484581,23595.7092511013) | | (4 rows) --GIN CREATE TABLE test__int( a int[] ); \copy test__int from 'data/test__int.data' CREATE INDEX gin_idx ON test__int USING gin ( a ); INSERT INTO test__int ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t ); INSERT INTO test__int ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) ); VACUUM ANALYZE test__int; SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int); value | nrow -------+------ 0 | 38 1 | 257 2 | 244 3 | 222 4 | 228 5 | 227 6 | 243 7 | 238 8 | 236 9 | 224 10 | 236 11 | 248 12 | 224 13 | 235 14 | 237 15 | 253 16 | 234 17 | 245 18 | 246 19 | 234 20 | 236 21 | 213 22 | 235 23 | 222 24 | 197 25 | 253 26 | 218 27 | 239 28 | 198 29 | 244 30 | 247 31 | 267 32 | 234 33 | 209 34 | 231 35 | 218 36 | 242 37 | 232 38 | 221 39 | 232 40 | 241 41 | 239 42 | 221 43 | 211 44 | 231 45 | 213 46 | 236 47 | 264 48 | 221 49 | 235 50 | 219 51 | 250 52 | 270 53 | 222 54 | 242 55 | 237 56 | 237 57 | 251 58 | 231 59 | 272 60 | 219 61 | 233 62 | 235 63 | 260 64 | 252 65 | 221 66 | 228 67 | 253 68 | 196 69 | 232 70 | 232 71 | 223 72 | 247 73 | 214 74 | 232 75 | 229 76 | 233 77 | 221 78 | 227 79 | 233 80 | 216 81 | 244 82 | 223 83 | 254 84 | 227 85 | 247 86 | 255 87 | 239 88 | 258 89 | 249 90 | 244 91 | 226 92 | 225 93 | 230 94 | 218 95 | 232 96 | 239 97 | 226 98 | 209 99 | 211 100 | 216 101 | 49 102 | 47 103 | 59 104 | 55 105 | 48 106 | 49 107 | 49 108 | 51 109 | 47 110 | 51 111 | 45 112 | 46 113 | 48 114 | 38 115 | 39 116 | 43 117 | 44 118 | 46 119 | 45 120 | 52 121 | 41 122 | 64 123 | 50 124 | 41 125 | 55 126 | 41 127 | 50 128 | 54 129 | 43 130 | 44 131 | 50 132 | 57 133 | 40 134 | 41 135 | 44 136 | 58 137 | 48 138 | 44 139 | 50 140 | 49 141 | 48 142 | 43 143 | 36 144 | 43 145 | 49 146 | 46 147 | 40 148 | 52 149 | 46 150 | 49 151 | 42 152 | 58 153 | 49 154 | 51 155 | 49 156 | 45 157 | 53 158 | 59 159 | 38 160 | 53 161 | 48 162 | 41 163 | 40 164 | 52 165 | 50 166 | 40 167 | 48 168 | 45 169 | 49 170 | 50 171 | 45 172 | 46 173 | 47 174 | 41 175 | 51 176 | 50 177 | 41 178 | 42 179 | 48 180 | 47 181 | 49 182 | 40 183 | 46 184 | 52 185 | 36 186 | 45 187 | 41 188 | 47 189 | 49 190 | 46 191 | 45 192 | 39 193 | 50 194 | 39 195 | 54 196 | 43 197 | 50 198 | 46 199 | 42 200 | 31 201 | 28 202 | 28 203 | 28 204 | 30 205 | 22 206 | 25 207 | 27 208 | 32 209 | 22 210 | 21 211 | 31 212 | 22 213 | 37 214 | 19 215 | 25 216 | 27 217 | 26 218 | 24 219 | 25 220 | 26 221 | 21 222 | 23 223 | 20 224 | 26 225 | 25 226 | 25 227 | 23 228 | 30 229 | 30 230 | 24 231 | 22 232 | 27 233 | 27 234 | 21 235 | 27 236 | 24 237 | 24 238 | 29 239 | 32 240 | 31 241 | 24 242 | 36 243 | 21 244 | 29 245 | 22 246 | 23 247 | 21 248 | 26 249 | 29 250 | 24 251 | 29 252 | 25 253 | 28 254 | 25 255 | 19 256 | 23 257 | 20 258 | 24 259 | 31 260 | 29 261 | 20 262 | 25 263 | 23 264 | 25 265 | 23 266 | 31 267 | 22 268 | 26 269 | 33 270 | 25 271 | 27 272 | 29 273 | 29 274 | 26 275 | 28 276 | 30 277 | 28 278 | 29 279 | 22 280 | 29 281 | 23 282 | 30 283 | 29 284 | 23 285 | 37 286 | 27 287 | 31 288 | 28 289 | 26 290 | 25 291 | 29 292 | 22 293 | 26 294 | 29 295 | 24 296 | 27 297 | 30 298 | 29 299 | 26 300 | 13 1000 | 300 1001 | 3600 | 9 | 244 (305 rows)