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