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-- Insert enough data to create a tree that's a couple of levels deep.
11insert into gist_point_tbl (id, p)
12select g,        point(g*10, g*10) from generate_series(1, 10000) g;
13
14insert into gist_point_tbl (id, p)
15select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
16
17-- To test vacuum, delete some entries from all over the index.
18delete from gist_point_tbl where id % 2 = 1;
19
20-- And also delete some concentration of values. (GiST doesn't currently
21-- attempt to delete pages even when they become empty, but if it did, this
22-- would exercise it)
23delete from gist_point_tbl where id < 10000;
24
25vacuum analyze gist_point_tbl;
26
27
28--
29-- Test Index-only plans on GiST indexes
30--
31
32create table gist_tbl (b box, p point, c circle);
33
34insert into gist_tbl
35select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
36       point(0.05*i, 0.05*i),
37       circle(point(0.05*i, 0.05*i), 1.0)
38from generate_series(0,10000) as i;
39
40vacuum analyze gist_tbl;
41
42set enable_seqscan=off;
43set enable_bitmapscan=off;
44set enable_indexonlyscan=on;
45
46-- Test index-only scan with point opclass
47create index gist_tbl_point_index on gist_tbl using gist (p);
48
49-- check that the planner chooses an index-only scan
50explain (costs off)
51select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
52
53-- execute the same
54select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
55
56-- Also test an index-only knn-search
57explain (costs off)
58select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
59order by p <-> point(0.201, 0.201);
60
61select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
62order by p <-> point(0.201, 0.201);
63
64-- Check commuted case as well
65explain (costs off)
66select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
67order by point(0.101, 0.101) <-> p;
68
69select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
70order by point(0.101, 0.101) <-> p;
71
72-- Check case with multiple rescans (bug #14641)
73explain (costs off)
74select p from
75  (values (box(point(0,0), point(0.5,0.5))),
76          (box(point(0.5,0.5), point(0.75,0.75))),
77          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
78cross join lateral
79  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
80
81select p from
82  (values (box(point(0,0), point(0.5,0.5))),
83          (box(point(0.5,0.5), point(0.75,0.75))),
84          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
85cross join lateral
86  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
87
88drop index gist_tbl_point_index;
89
90-- Test index-only scan with box opclass
91create index gist_tbl_box_index on gist_tbl using gist (b);
92
93-- check that the planner chooses an index-only scan
94explain (costs off)
95select b from gist_tbl where b <@ box(point(5,5), point(6,6));
96
97-- execute the same
98select b from gist_tbl where b <@ box(point(5,5), point(6,6));
99
100drop index gist_tbl_box_index;
101
102-- Test that an index-only scan is not chosen, when the query involves the
103-- circle column (the circle opclass does not support index-only scans).
104create index gist_tbl_multi_index on gist_tbl using gist (p, c);
105
106explain (costs off)
107select p, c from gist_tbl
108where p <@ box(point(5,5), point(6, 6));
109
110-- execute the same
111select b, p from gist_tbl
112where b <@ box(point(4.5, 4.5), point(5.5, 5.5))
113and p <@ box(point(5,5), point(6, 6));
114
115drop index gist_tbl_multi_index;
116
117-- Clean up
118reset enable_seqscan;
119reset enable_bitmapscan;
120reset enable_indexonlyscan;
121
122drop table gist_tbl;
123