1 SET client_min_messages = warning;
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 ERROR: access method "spgist" does not exist
10 CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops);
11 ERROR: access method "spgist" does not exist
13 SELECT gist_stat('gist_idx');
15 -----------------------------------------
18 Number of leaf pages: 32
19 Number of tuples: 3410
20 Number of invalid tuples: 0
21 Number of leaf tuples: 3378
22 Total size of tuples: 143740 bytes
23 Total size of leaf tuples: 142344 bytes
24 Total size of index: 270336 bytes
28 SELECT gist_tree('gist_idx');
30 -----------------------------------------------------------------------------------------------
31 0(l:0) blk: 0 numTuple: 32 free: 6764b(17.11%) rightlink:4294967295 (InvalidBlockNumber)
32 1(l:1) blk: 2 numTuple: 278 free: 2588b(68.28%) rightlink:4294967295 (InvalidBlockNumber)
33 2(l:1) blk: 18 numTuple: 109 free: 3352b(58.92%) rightlink:3 (OK)
34 3(l:1) blk: 3 numTuple: 98 free: 3836b(52.99%) rightlink:20 (OK)
35 4(l:1) blk: 20 numTuple: 114 free: 3132b(61.62%) rightlink:16 (OK)
36 5(l:1) blk: 13 numTuple: 110 free: 3308b(59.46%) rightlink:18 (OK)
37 6(l:1) blk: 6 numTuple: 102 free: 3660b(55.15%) rightlink:22 (OK)
38 7(l:1) blk: 22 numTuple: 102 free: 3660b(55.15%) rightlink:13 (OK)
39 8(l:1) blk: 8 numTuple: 103 free: 3616b(55.69%) rightlink:19 (OK)
40 9(l:1) blk: 9 numTuple: 97 free: 3880b(52.45%) rightlink:25 (OK)
41 10(l:1) blk: 26 numTuple: 100 free: 3748b(54.07%) rightlink:7 (OK)
42 11(l:1) blk: 10 numTuple: 94 free: 4012b(50.83%) rightlink:26 (OK)
43 12(l:1) blk: 24 numTuple: 96 free: 3924b(51.91%) rightlink:10 (OK)
44 13(l:1) blk: 4 numTuple: 88 free: 4276b(47.60%) rightlink:27 (OK)
45 14(l:1) blk: 27 numTuple: 93 free: 4056b(50.29%) rightlink:12 (OK)
46 15(l:1) blk: 17 numTuple: 91 free: 4144b(49.22%) rightlink:28 (OK)
47 16(l:1) blk: 28 numTuple: 99 free: 3792b(53.53%) rightlink:4 (OK)
48 17(l:1) blk: 30 numTuple: 91 free: 4144b(49.22%) rightlink:2 (OK)
49 18(l:1) blk: 5 numTuple: 104 free: 3572b(56.23%) rightlink:24 (OK)
50 19(l:1) blk: 15 numTuple: 92 free: 4100b(49.75%) rightlink:29 (OK)
51 20(l:1) blk: 7 numTuple: 90 free: 4188b(48.68%) rightlink:21 (OK)
52 21(l:1) blk: 29 numTuple: 100 free: 3748b(54.07%) rightlink:9 (OK)
53 22(l:1) blk: 25 numTuple: 96 free: 3924b(51.91%) rightlink:17 (OK)
54 23(l:1) blk: 1 numTuple: 99 free: 3792b(53.53%) rightlink:23 (OK)
55 24(l:1) blk: 21 numTuple: 107 free: 3440b(57.84%) rightlink:11 (OK)
56 25(l:1) blk: 14 numTuple: 90 free: 4188b(48.68%) rightlink:31 (OK)
57 26(l:1) blk: 31 numTuple: 90 free: 4188b(48.68%) rightlink:5 (OK)
58 27(l:1) blk: 32 numTuple: 88 free: 4276b(47.60%) rightlink:8 (OK)
59 28(l:1) blk: 12 numTuple: 164 free: 932b(88.58%) rightlink:6 (OK)
60 29(l:1) blk: 19 numTuple: 94 free: 4012b(50.83%) rightlink:14 (OK)
61 30(l:1) blk: 11 numTuple: 94 free: 4012b(50.83%) rightlink:30 (OK)
62 31(l:1) blk: 23 numTuple: 101 free: 3704b(54.61%) rightlink:15 (OK)
63 32(l:1) blk: 16 numTuple: 104 free: 3572b(56.23%) rightlink:32 (OK)
67 SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where level=1;
69 -------+-------+-----------------------------
71 1 | t | (24240,50012),(204,46917)
72 1 | t | (28102,24428),(25047,579)
73 1 | t | (31193,24679),(28142,6)
74 1 | t | (24938,46902),(64,43729)
75 1 | t | (24898,40568),(3,37480)
76 1 | t | (24647,43650),(179,40585)
77 1 | t | (32737,49923),(31178,1038)
78 1 | t | (25002,6338),(12350,51)
79 1 | t | (49985,25005),(46856,499)
80 1 | t | (46830,24767),(43743,222)
81 1 | t | (43689,24613),(40603,43)
82 1 | t | (6348,37358),(120,25034)
83 1 | t | (12528,37333),(6305,24861)
84 1 | t | (25054,18890),(12555,12796)
85 1 | t | (24855,24987),(12403,18887)
86 1 | t | (50027,49912),(37338,43927)
87 1 | t | (40565,24963),(37375,491)
88 1 | t | (9308,24151),(6200,620)
89 1 | t | (43715,37586),(37415,25070)
90 1 | t | (12343,24542),(9295,294)
91 1 | t | (24919,12698),(12654,6518)
92 1 | t | (3088,24465),(15,81)
93 1 | t | (49999,37405),(43837,25153)
94 1 | t | (35828,49443),(34242,972)
95 1 | t | (37357,50073),(35769,357)
96 1 | t | (31171,50040),(24900,37723)
97 1 | t | (24840,37414),(12552,24965)
98 1 | t | (34270,49382),(32763,594)
99 1 | t | (49957,43846),(37423,37741)
100 1 | t | (6197,24629),(3113,108)
101 1 | t | (31179,37276),(24921,25000)
105 SELECT spgist_stat('spgist_idx');
106 NOTICE: Function is not working under PgSQL < 9.2
112 SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point);
113 NOTICE: Function is not working under PgSQL < 9.2
114 ERROR: cache lookup failed for type 2779096485
115 SELECT * FROM spgist_print('spgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1;
116 NOTICE: Function is not working under PgSQL < 9.2
117 ERROR: cache lookup failed for type 2779096485
119 CREATE TABLE test__int( a int[] );
120 \copy test__int from 'data/test__int.data'
121 CREATE INDEX gin_idx ON test__int USING gin ( a );
122 INSERT INTO test__int ( SELECT ARRAY[t] || '{1000}'::_int4 FROM generate_series (1,300) as t );
123 INSERT INTO test__int ( SELECT ARRAY[t] || '{1001}'::_int4 FROM generate_series (1,300) as t, generate_series(1,12) );
124 VACUUM ANALYZE test__int;
125 SELECT * FROM gin_stat('gin_idx') as t(value int, nrow int);