expected/gevel.out.9.6

   1 SET client_min_messages = warning;
   2 \set ECHO none
   3 RESET client_min_messages;
   4 CREATE TABLE gevelt ( t box );
   5 \copy gevelt from 'data/rect.data'
   6 SELECT center(t) AS p INTO gevelp FROM gevelt;
   7 CREATE INDEX gist_idx ON gevelt USING gist ( t );
   8 CREATE INDEX spgist_idx ON gevelp USING spgist ( p );
   9 CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops);
  10 --GiST
  11 SELECT gist_stat('gist_idx');
  12                 gist_stat
  13 -----------------------------------------
  14  Number of levels:          2           +
  15  Number of pages:           29          +
  16  Number of leaf pages:      28          +
  17  Number of tuples:          3406        +
  18  Number of invalid tuples:  0           +
  19  Number of leaf tuples:     3378        +
  20  Total size of tuples:      143516 bytes+
  21  Total size of leaf tuples: 142296 bytes+
  22  Total size of index:       237568 bytes+
  23
  24 (1 row)
  25
  26 SELECT gist_tree('gist_idx');
  27                                            gist_tree
  28 -----------------------------------------------------------------------------------------------
  29  0(l:0) blk: 0 numTuple: 28 free: 6940b(14.95%) rightlink:4294967295 (InvalidBlockNumber)     +
  30      1(l:1) blk: 2 numTuple: 278 free: 2588b(68.28%) rightlink:4294967295 (InvalidBlockNumber)+
  31      2(l:1) blk: 19 numTuple: 123 free: 2736b(66.47%) rightlink:3 (OK)                        +
  32      3(l:1) blk: 8 numTuple: 128 free: 2516b(69.17%) rightlink:22 (OK)                        +
  33      4(l:1) blk: 4 numTuple: 92 free: 4100b(49.75%) rightlink:23 (OK)                         +
  34      5(l:1) blk: 20 numTuple: 122 free: 2780b(65.93%) rightlink:7 (OK)                        +
  35      6(l:1) blk: 15 numTuple: 127 free: 2560b(68.63%) rightlink:11 (OK)                       +
  36      7(l:1) blk: 18 numTuple: 138 free: 2076b(74.56%) rightlink:9 (OK)                        +
  37      8(l:1) blk: 23 numTuple: 126 free: 2604b(68.09%) rightlink:8 (OK)                        +
  38      9(l:1) blk: 10 numTuple: 156 free: 1284b(84.26%) rightlink:19 (OK)                       +
  39      10(l:1) blk: 13 numTuple: 130 free: 2428b(70.25%) rightlink:20 (OK)                      +
  40      11(l:1) blk: 12 numTuple: 70 free: 5068b(37.89%) rightlink:24 (OK)                       +
  41      12(l:1) blk: 22 numTuple: 112 free: 3220b(60.54%) rightlink:10 (OK)                      +
  42      13(l:1) blk: 16 numTuple: 89 free: 4232b(48.14%) rightlink:26 (OK)                       +
  43      14(l:1) blk: 26 numTuple: 108 free: 3396b(58.38%) rightlink:5 (OK)                       +
  44      15(l:1) blk: 7 numTuple: 99 free: 3792b(53.53%) rightlink:16 (OK)                        +
  45      16(l:1) blk: 6 numTuple: 120 free: 2868b(64.85%) rightlink:18 (OK)                       +
  46      17(l:1) blk: 14 numTuple: 163 free: 976b(88.04%) rightlink:6 (OK)                        +
  47      18(l:1) blk: 28 numTuple: 122 free: 2780b(65.93%) rightlink:12 (OK)                      +
  48      19(l:1) blk: 3 numTuple: 57 free: 5640b(30.88%) rightlink:27 (OK)                        +
  49      20(l:1) blk: 25 numTuple: 58 free: 5596b(31.42%) rightlink:15 (OK)                       +
  50      21(l:1) blk: 17 numTuple: 87 free: 4320b(47.06%) rightlink:4 (OK)                        +
  51      22(l:1) blk: 9 numTuple: 128 free: 2516b(69.17%) rightlink:25 (OK)                       +
  52      23(l:1) blk: 21 numTuple: 166 free: 844b(89.66%) rightlink:13 (OK)                       +
  53      24(l:1) blk: 27 numTuple: 124 free: 2692b(67.01%) rightlink:14 (OK)                      +
  54      25(l:1) blk: 11 numTuple: 146 free: 1724b(78.87%) rightlink:2 (OK)                       +
  55      26(l:1) blk: 5 numTuple: 61 free: 5464b(33.04%) rightlink:28 (OK)                        +
  56      27(l:1) blk: 1 numTuple: 92 free: 4100b(49.75%) rightlink:21 (OK)                        +
  57      28(l:1) blk: 24 numTuple: 156 free: 1284b(84.26%) rightlink:17 (OK)                      +
  58
  59 (1 row)
  60
  61 SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where level=1;
  62  level | valid |              a
  63 -------+-------+-----------------------------
  64      1 | t     |
  65      1 | t     | (32261,50040),(20397,42779)
  66      1 | t     | (20400,41715),(9175,31485)
  67      1 | t     | (8924,39250),(3,31083)
  68      1 | t     | (20594,19151),(10869,10535)
  69      1 | t     | (49981,42533),(43290,28026)
  70      1 | t     | (49999,27727),(40603,15778)
  71      1 | t     | (8993,50012),(64,39781)
  72      1 | t     | (32394,42395),(20513,31557)
  73      1 | t     | (20715,10161),(10407,51)
  74      1 | t     | (32512,16364),(20947,11843)
  75      1 | t     | (20327,49975),(9216,42130)
  76      1 | t     | (13397,31188),(7334,19288)
  77      1 | t     | (20856,31294),(13642,19263)
  78      1 | t     | (6986,30859),(107,19466)
  79      1 | t     | (40397,28538),(32515,15759)
  80      1 | t     | (49985,15380),(42047,43)
  81      1 | t     | (32715,11509),(24675,6)
  82      1 | t     | (41916,5154),(32914,357)
  83      1 | t     | (43056,42360),(32661,37729)
  84      1 | t     | (32440,31262),(21146,25161)
  85      1 | t     | (42941,37586),(32606,28547)
  86      1 | t     | (10104,18999),(15,7761)
  87      1 | t     | (41835,15752),(32828,5360)
  88      1 | t     | (50027,50073),(32453,42694)
  89      1 | t     | (24423,11473),(20828,181)
  90      1 | t     | (10146,7495),(138,81)
  91      1 | t     | (32705,25105),(20838,16626)
  92 (28 rows)
  93
  94 --SPGiST
  95 SELECT spgist_stat('spgist_idx');
  96            spgist_stat
  97 ----------------------------------
  98  totalPages:        21           +
  99  deletedPages:      0            +
 100  innerPages:        2            +
 101  leafPages:         19           +
 102  emptyPages:        0            +
 103  usedSpace:         126.70 kbytes+
 104  usedInnerSpace:    1.17 kbytes  +
 105  usedLeafSpace:     125.53 kbytes+
 106  freeSpace:         40.64 kbytes +
 107  fillRatio:         75.71%       +
 108  leafTuples:        3947         +
 109  innerTuples:       20           +
 110  innerAllTheSame:   0            +
 111  leafPlaceholders:  569          +
 112  innerPlaceholders: 0            +
 113  leafRedirects:     0            +
 114  innerRedirects:    0
 115 (1 row)
 116
 117 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);
 118    tid    | allthesame | node_n | level | tid_pointer | prefix  | node_label |    leaf_value
 119 ----------+------------+--------+-------+-------------+---------+------------+-------------------
 120  (1,1)    | f          |      0 |     1 | (5,1)       |   23030 |            |
 121  (1,1)    | f          |      1 |     1 | (5,2)       |   23030 |            |
 122  (5,2)    | f          |      0 |     2 | (5,6)       |   21664 |            |
 123  (5,2)    | f          |      1 |     2 | (5,3)       |   21664 |            |
 124  (5,3)    | f          |      0 |     3 | (5,5)       | 37159.5 |            |
 125  (5,3)    | f          |      1 |     3 | (5,12)      | 37159.5 |            |
 126  (5,12)   | f          |      0 |     4 | (5,13)      | 36357.5 |            |
 127  (5,12)   | f          |      1 |     4 | (14,226)    | 36357.5 |            |
 128  (14,226) |            |        |     5 |             |         |            | (43240,47256.5)
 129  (5,13)   | f          |      0 |     5 |             |   43507 |            |
 130  (5,13)   | f          |      1 |     5 |             |   43507 |            |
 131  (5,25)   | f          |      0 |     6 |             |   28862 |            |
 132  (5,25)   | f          |      1 |     6 |             |   28862 |            |
 133  (22,115) |            |        |     7 |             |         |            | (30902,44532)
 134  (22,116) |            |        |     7 |             |         |            | (23048,47416)
 135  (3,112)  |            |        |     6 |             |         |            | (35218.5,42124)
 136  (5,5)    | f          |      0 |     4 |             |   33416 |            |
 137  (5,5)    | f          |      1 |     4 |             |   33416 |            |
 138  (5,22)   | f          |      0 |     5 |             |   30991 |            |
 139  (5,22)   | f          |      1 |     5 |             |   30991 |            |
 140  (5,23)   | f          |      0 |     6 |             |   41820 |            |
 141  (5,23)   | f          |      1 |     6 |             |   41820 |            |
 142  (9,114)  |            |        |     7 |             |         |            | (44732,32182)
 143  (9,112)  |            |        |     7 |             |         |            | (35580,33526.5)
 144  (20,225) |            |        |     6 |             |         |            | (47724.5,27185.5)
 145  (5,11)   | f          |      0 |     5 |             | 29986.5 |            |
 146  (5,11)   | f          |      1 |     5 |             | 29986.5 |            |
 147  (13,100) |            |        |     6 |             |         |            | (24069,30850.5)
 148  (13,101) |            |        |     6 |             |         |            | (29539,25566)
 149  (5,6)    | f          |      0 |     3 |             |   36774 |            |
 150  (5,6)    | f          |      1 |     3 |             |   36774 |            |
 151  (5,19)   | f          |      0 |     4 |             | 10075.5 |            |
 152  (5,19)   | f          |      1 |     4 |             | 10075.5 |            |
 153  (18,225) |            |        |     5 |             |         |            | (20920.5,49105.5)
 154  (5,20)   | f          |      0 |     5 |             | 44171.5 |            |
 155  (5,20)   | f          |      1 |     5 |             | 44171.5 |            |
 156  (10,113) |            |        |     6 |             |         |            | (93,46797)
 157  (10,110) |            |        |     6 |             |         |            | (28.5,38640.5)
 158  (5,7)    | f          |      0 |     4 |             |    9517 |            |
 159  (5,7)    | f          |      1 |     4 |             |    9517 |            |
 160  (5,21)   | f          |      0 |     5 |             | 28907.5 |            |
 161  (5,21)   | f          |      1 |     5 |             | 28907.5 |            |
 162  (19,112) |            |        |     6 |             |         |            | (11916.5,31668)
 163  (19,115) |            |        |     6 |             |         |            | (20622.5,27462.5)
 164  (7,113)  |            |        |     5 |             |         |            | (9296,35157)
 165  (5,1)    | f          |      0 |     2 |             |   26938 |            |
 166  (5,1)    | f          |      1 |     2 |             |   26938 |            |
 167  (5,4)    | f          |      0 |     3 |             |    9532 |            |
 168  (5,4)    | f          |      1 |     3 |             |    9532 |            |
 169  (5,14)   | f          |      0 |     4 |             |   38603 |            |
 170  (5,14)   | f          |      1 |     4 |             |   38603 |            |
 171  (15,222) |            |        |     5 |             |         |            | (41926.5,17934.5)
 172  (5,15)   | f          |      0 |     5 |             |   16345 |            |
 173  (5,15)   | f          |      1 |     5 |             |   16345 |            |
 174  (8,112)  |            |        |     6 |             |         |            | (32425,20702.5)
 175  (8,113)  |            |        |     6 |             |         |            | (29134,15559.5)
 176  (5,10)   | f          |      0 |     4 |             | 38800.5 |            |
 177  (5,10)   | f          |      1 |     4 |             | 38800.5 |            |
 178  (5,24)   | f          |      0 |     5 |             |    4752 |            |
 179  (5,24)   | f          |      1 |     5 |             |    4752 |            |
 180  (21,107) |            |        |     6 |             |         |            | (49822.5,7097.5)
 181  (21,108) |            |        |     6 |             |         |            | (40315.5,1689.5)
 182  (12,94)  |            |        |     5 |             |         |            | (30295.5,5090)
 183  (5,8)    | f          |      0 |     3 |             | 11733.5 |            |
 184  (5,8)    | f          |      1 |     3 |             | 11733.5 |            |
 185  (5,9)    | f          |      0 |     4 |             |   11993 |            |
 186  (5,9)    | f          |      1 |     4 |             |   11993 |            |
 187  (5,16)   | f          |      0 |     5 |             |   17591 |            |
 188  (5,16)   | f          |      1 |     5 |             |   17591 |            |
 189  (16,127) |            |        |     6 |             |         |            | (18352.5,19366)
 190  (16,123) |            |        |     6 |             |         |            | (24795,14921)
 191  (6,114)  |            |        |     5 |             |         |            | (6706,16676)
 192  (5,17)   | f          |      0 |     4 |             | 13329.5 |            |
 193  (5,17)   | f          |      1 |     4 |             | 13329.5 |            |
 194  (17,226) |            |        |     5 |             |         |            | (23690,10214.5)
 195  (5,18)   | f          |      0 |     5 |             |    6375 |            |
 196  (5,18)   | f          |      1 |     5 |             |    6375 |            |
 197  (11,109) |            |        |     6 |             |         |            | (5501.5,9916)
 198  (11,113) |            |        |     6 |             |         |            | (1072.5,4752)
 199 (79 rows)
 200
 201 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;
 202   tid  | allthesame | node_n | level | tid_pointer |               prefix                | node_label | leaf_value
 203 -------+------------+--------+-------+-------------+-------------------------------------+------------+------------
 204  (1,1) | f          |      0 |     1 |             | (24530.2070484581,23595.7092511013) |            |
 205  (1,1) | f          |      1 |     1 |             | (24530.2070484581,23595.7092511013) |            |
 206  (1,1) | f          |      2 |     1 |             | (24530.2070484581,23595.7092511013) |            |
 207  (1,1) | f          |      3 |     1 |             | (24530.2070484581,23595.7092511013) |            |
 208 (4 rows)
 209
 210 --GIN
 211 CREATE TABLE test__int( a int[] );
 212 \copy test__int from 'data/test__int.data'
 213 CREATE INDEX gin_idx ON test__int USING gin ( a );
 214 INSERT INTO test__int ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t );
 215 INSERT INTO test__int ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) );
 216 VACUUM ANALYZE test__int;
 217 SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int);
 218  value | nrow
 219 -------+------
 220      0 |   38
 221      1 |  257
 222      2 |  244
 223      3 |  222
 224      4 |  228
 225      5 |  227
 226      6 |  243
 227      7 |  238
 228      8 |  236
 229      9 |  224
 230     10 |  236
 231     11 |  248
 232     12 |  224
 233     13 |  235
 234     14 |  237
 235     15 |  253
 236     16 |  234
 237     17 |  245
 238     18 |  246
 239     19 |  234
 240     20 |  236
 241     21 |  213
 242     22 |  235
 243     23 |  222
 244     24 |  197
 245     25 |  253
 246     26 |  218
 247     27 |  239
 248     28 |  198
 249     29 |  244
 250     30 |  247
 251     31 |  267
 252     32 |  234
 253     33 |  209
 254     34 |  231
 255     35 |  218
 256     36 |  242
 257     37 |  232
 258     38 |  221
 259     39 |  232
 260     40 |  241
 261     41 |  239
 262     42 |  221
 263     43 |  211
 264     44 |  231
 265     45 |  213
 266     46 |  236
 267     47 |  264
 268     48 |  221
 269     49 |  235
 270     50 |  219
 271     51 |  250
 272     52 |  270
 273     53 |  222
 274     54 |  242
 275     55 |  237
 276     56 |  237
 277     57 |  251
 278     58 |  231
 279     59 |  272
 280     60 |  219
 281     61 |  233
 282     62 |  235
 283     63 |  260
 284     64 |  252
 285     65 |  221
 286     66 |  228
 287     67 |  253
 288     68 |  196
 289     69 |  232
 290     70 |  232
 291     71 |  223
 292     72 |  247
 293     73 |  214
 294     74 |  232
 295     75 |  229
 296     76 |  233
 297     77 |  221
 298     78 |  227
 299     79 |  233
 300     80 |  216
 301     81 |  244
 302     82 |  223
 303     83 |  254
 304     84 |  227
 305     85 |  247
 306     86 |  255
 307     87 |  239
 308     88 |  258
 309     89 |  249
 310     90 |  244
 311     91 |  226
 312     92 |  225
 313     93 |  230
 314     94 |  218
 315     95 |  232
 316     96 |  239
 317     97 |  226
 318     98 |  209
 319     99 |  211
 320    100 |  216
 321    101 |   49
 322    102 |   47
 323    103 |   59
 324    104 |   55
 325    105 |   48
 326    106 |   49
 327    107 |   49
 328    108 |   51
 329    109 |   47
 330    110 |   51
 331    111 |   45
 332    112 |   46
 333    113 |   48
 334    114 |   38
 335    115 |   39
 336    116 |   43
 337    117 |   44
 338    118 |   46
 339    119 |   45
 340    120 |   52
 341    121 |   41
 342    122 |   64
 343    123 |   50
 344    124 |   41
 345    125 |   55
 346    126 |   41
 347    127 |   50
 348    128 |   54
 349    129 |   43
 350    130 |   44
 351    131 |   50
 352    132 |   57
 353    133 |   40
 354    134 |   41
 355    135 |   44
 356    136 |   58
 357    137 |   48
 358    138 |   44
 359    139 |   50
 360    140 |   49
 361    141 |   48
 362    142 |   43
 363    143 |   36
 364    144 |   43
 365    145 |   49
 366    146 |   46
 367    147 |   40
 368    148 |   52
 369    149 |   46
 370    150 |   49
 371    151 |   42
 372    152 |   58
 373    153 |   49
 374    154 |   51
 375    155 |   49
 376    156 |   45
 377    157 |   53
 378    158 |   59
 379    159 |   38
 380    160 |   53
 381    161 |   48
 382    162 |   41
 383    163 |   40
 384    164 |   52
 385    165 |   50
 386    166 |   40
 387    167 |   48
 388    168 |   45
 389    169 |   49
 390    170 |   50
 391    171 |   45
 392    172 |   46
 393    173 |   47
 394    174 |   41
 395    175 |   51
 396    176 |   50
 397    177 |   41
 398    178 |   42
 399    179 |   48
 400    180 |   47
 401    181 |   49
 402    182 |   40
 403    183 |   46
 404    184 |   52
 405    185 |   36
 406    186 |   45
 407    187 |   41
 408    188 |   47
 409    189 |   49
 410    190 |   46
 411    191 |   45
 412    192 |   39
 413    193 |   50
 414    194 |   39
 415    195 |   54
 416    196 |   43
 417    197 |   50
 418    198 |   46
 419    199 |   42
 420    200 |   31
 421    201 |   28
 422    202 |   28
 423    203 |   28
 424    204 |   30
 425    205 |   22
 426    206 |   25
 427    207 |   27
 428    208 |   32
 429    209 |   22
 430    210 |   21
 431    211 |   31
 432    212 |   22
 433    213 |   37
 434    214 |   19
 435    215 |   25
 436    216 |   27
 437    217 |   26
 438    218 |   24
 439    219 |   25
 440    220 |   26
 441    221 |   21
 442    222 |   23
 443    223 |   20
 444    224 |   26
 445    225 |   25
 446    226 |   25
 447    227 |   23
 448    228 |   30
 449    229 |   30
 450    230 |   24
 451    231 |   22
 452    232 |   27
 453    233 |   27
 454    234 |   21
 455    235 |   27
 456    236 |   24
 457    237 |   24
 458    238 |   29
 459    239 |   32
 460    240 |   31
 461    241 |   24
 462    242 |   36
 463    243 |   21
 464    244 |   29
 465    245 |   22
 466    246 |   23
 467    247 |   21
 468    248 |   26
 469    249 |   29
 470    250 |   24
 471    251 |   29
 472    252 |   25
 473    253 |   28
 474    254 |   25
 475    255 |   19
 476    256 |   23
 477    257 |   20
 478    258 |   24
 479    259 |   31
 480    260 |   29
 481    261 |   20
 482    262 |   25
 483    263 |   23
 484    264 |   25
 485    265 |   23
 486    266 |   31
 487    267 |   22
 488    268 |   26
 489    269 |   33
 490    270 |   25
 491    271 |   27
 492    272 |   29
 493    273 |   29
 494    274 |   26
 495    275 |   28
 496    276 |   30
 497    277 |   28
 498    278 |   29
 499    279 |   22
 500    280 |   29
 501    281 |   23
 502    282 |   30
 503    283 |   29
 504    284 |   23
 505    285 |   37
 506    286 |   27
 507    287 |   31
 508    288 |   28
 509    289 |   26
 510    290 |   25
 511    291 |   29
 512    292 |   22
 513    293 |   26
 514    294 |   29
 515    295 |   24
 516    296 |   27
 517    297 |   30
 518    298 |   29
 519    299 |   26
 520    300 |   13
 521   1000 |  300
 522   1001 | 3600
 523        |    9
 524        |  244
 525 (305 rows)
 526