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