1--
2-- Test GiST indexes.
3--
4-- There are other tests to test different GiST opclasses. This is for
5-- testing GiST code itself. Vacuuming in particular.
6
7create table gist_point_tbl(id int4, p point);
8create index gist_pointidx on gist_point_tbl using gist(p);
9
10-- Verify the fillfactor and buffering options
11create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
12create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
13create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
14drop index gist_pointidx2, gist_pointidx3, gist_pointidx4;
15
16-- Make sure bad values are refused
17create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
18create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
19create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
20
21-- Insert enough data to create a tree that's a couple of levels deep.
22insert into gist_point_tbl (id, p)
23select g,        point(g*10, g*10) from generate_series(1, 10000) g;
24
25insert into gist_point_tbl (id, p)
26select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
27
28-- To test vacuum, delete some entries from all over the index.
29delete from gist_point_tbl where id % 2 = 1;
30
31-- And also delete some concentration of values.
32delete from gist_point_tbl where id > 5000;
33
34vacuum analyze gist_point_tbl;
35
36-- rebuild the index with a different fillfactor
37alter index gist_pointidx SET (fillfactor = 40);
38reindex index gist_pointidx;
39
40--
41-- Test Index-only plans on GiST indexes
42--
43
44create table gist_tbl (b box, p point, c circle);
45
46insert into gist_tbl
47select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
48       point(0.05*i, 0.05*i),
49       circle(point(0.05*i, 0.05*i), 1.0)
50from generate_series(0,10000) as i;
51
52vacuum analyze gist_tbl;
53
54set enable_seqscan=off;
55set enable_bitmapscan=off;
56set enable_indexonlyscan=on;
57
58-- Test index-only scan with point opclass
59create index gist_tbl_point_index on gist_tbl using gist (p);
60
61-- check that the planner chooses an index-only scan
62explain (costs off)
63select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
64
65-- execute the same
66select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
67
68-- Also test an index-only knn-search
69explain (costs off)
70select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
71order by p <-> point(0.201, 0.201);
72
73select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
74order by p <-> point(0.201, 0.201);
75
76-- Check commuted case as well
77explain (costs off)
78select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
79order by point(0.101, 0.101) <-> p;
80
81select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
82order by point(0.101, 0.101) <-> p;
83
84-- Check case with multiple rescans (bug #14641)
85explain (costs off)
86select p from
87  (values (box(point(0,0), point(0.5,0.5))),
88          (box(point(0.5,0.5), point(0.75,0.75))),
89          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
90cross join lateral
91  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
92
93select p from
94  (values (box(point(0,0), point(0.5,0.5))),
95          (box(point(0.5,0.5), point(0.75,0.75))),
96          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
97cross join lateral
98  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
99
100drop index gist_tbl_point_index;
101
102-- Test index-only scan with box opclass
103create index gist_tbl_box_index on gist_tbl using gist (b);
104
105-- check that the planner chooses an index-only scan
106explain (costs off)
107select b from gist_tbl where b <@ box(point(5,5), point(6,6));
108
109-- execute the same
110select b from gist_tbl where b <@ box(point(5,5), point(6,6));
111
112-- Also test an index-only knn-search
113explain (costs off)
114select b from gist_tbl where b <@ box(point(5,5), point(6,6))
115order by b <-> point(5.2, 5.91);
116
117select b from gist_tbl where b <@ box(point(5,5), point(6,6))
118order by b <-> point(5.2, 5.91);
119
120-- Check commuted case as well
121explain (costs off)
122select b from gist_tbl where b <@ box(point(5,5), point(6,6))
123order by point(5.2, 5.91) <-> b;
124
125select b from gist_tbl where b <@ box(point(5,5), point(6,6))
126order by point(5.2, 5.91) <-> b;
127
128drop index gist_tbl_box_index;
129
130-- Test that an index-only scan is not chosen, when the query involves the
131-- circle column (the circle opclass does not support index-only scans).
132create index gist_tbl_multi_index on gist_tbl using gist (p, c);
133
134explain (costs off)
135select p, c from gist_tbl
136where p <@ box(point(5,5), point(6, 6));
137
138-- execute the same
139select b, p from gist_tbl
140where b <@ box(point(4.5, 4.5), point(5.5, 5.5))
141and p <@ box(point(5,5), point(6, 6));
142
143drop index gist_tbl_multi_index;
144
145-- Clean up
146reset enable_seqscan;
147reset enable_bitmapscan;
148reset enable_indexonlyscan;
149
150drop table gist_tbl;
151