1--
2-- Test SP-GiST indexes.
3--
4-- There are other tests to test different SP-GiST opclasses. This is for
5-- testing SP-GiST code itself.
6create table spgist_point_tbl(id int4, p point);
7create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
8-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
9-- i.e. the index is very small.
10insert into spgist_point_tbl (id, p)
11select g, point(g*10, g*10) from generate_series(1, 10) g;
12delete from spgist_point_tbl where id < 5;
13vacuum spgist_point_tbl;
14-- Insert more data, to make the index a few levels deep.
15insert into spgist_point_tbl (id, p)
16select g,      point(g*10, g*10) from generate_series(1, 10000) g;
17insert into spgist_point_tbl (id, p)
18select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
19-- To test vacuum, delete some entries from all over the index.
20delete from spgist_point_tbl where id % 2 = 1;
21-- And also delete some concentration of values. (SP-GiST doesn't currently
22-- attempt to delete pages even when they become empty, but if it did, this
23-- would exercise it)
24delete from spgist_point_tbl where id < 10000;
25vacuum spgist_point_tbl;
26-- Test rescan paths (cf. bug #15378)
27-- use box and && rather than point, so that rescan happens when the
28-- traverse stack is non-empty
29create table spgist_box_tbl(id serial, b box);
30insert into spgist_box_tbl(b)
31select box(point(i,j),point(i+s,j+s))
32  from generate_series(1,100,5) i,
33       generate_series(1,100,5) j,
34       generate_series(1,10) s;
35create index spgist_box_idx on spgist_box_tbl using spgist (b);
36select count(*)
37  from (values (point(5,5)),(point(8,8)),(point(12,12))) v(p)
38 where exists(select * from spgist_box_tbl b where b.b && box(v.p,v.p));
39 count
40-------
41     3
42(1 row)
43
44-- The point opclass's choose method only uses the spgMatchNode action,
45-- so the other actions are not tested by the above. Create an index using
46-- text opclass, which uses the others actions.
47create table spgist_text_tbl(id int4, t text);
48create index spgist_text_idx on spgist_text_tbl using spgist(t);
49insert into spgist_text_tbl (id, t)
50select g, 'f' || repeat('o', 100) || g from generate_series(1, 10000) g
51union all
52select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
53-- Do a lot of insertions that have to split an existing node. Hopefully
54-- one of these will cause the page to run out of space, causing the inner
55-- tuple to be moved to another page.
56insert into spgist_text_tbl (id, t)
57select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
58-- Test out-of-range fillfactor values
59create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
60ERROR:  value 9 out of bounds for option "fillfactor"
61DETAIL:  Valid values are between "10" and "100".
62create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
63ERROR:  value 101 out of bounds for option "fillfactor"
64DETAIL:  Valid values are between "10" and "100".
65-- Modify fillfactor in existing index
66alter index spgist_point_idx set (fillfactor = 90);
67reindex index spgist_point_idx;
68