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