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. 6create table gist_point_tbl(id int4, p point); 7create index gist_pointidx on gist_point_tbl using gist(p); 8-- Insert enough data to create a tree that's a couple of levels deep. 9insert into gist_point_tbl (id, p) 10select g, point(g*10, g*10) from generate_series(1, 10000) g; 11insert into gist_point_tbl (id, p) 12select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g; 13-- To test vacuum, delete some entries from all over the index. 14delete from gist_point_tbl where id % 2 = 1; 15-- And also delete some concentration of values. (GiST doesn't currently 16-- attempt to delete pages even when they become empty, but if it did, this 17-- would exercise it) 18delete from gist_point_tbl where id < 10000; 19vacuum analyze gist_point_tbl; 20-- 21-- Test Index-only plans on GiST indexes 22-- 23create table gist_tbl (b box, p point, c circle); 24insert into gist_tbl 25select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)), 26 point(0.05*i, 0.05*i), 27 circle(point(0.05*i, 0.05*i), 1.0) 28from generate_series(0,10000) as i; 29vacuum analyze gist_tbl; 30set enable_seqscan=off; 31set enable_bitmapscan=off; 32set enable_indexonlyscan=on; 33-- Test index-only scan with point opclass 34create index gist_tbl_point_index on gist_tbl using gist (p); 35-- check that the planner chooses an index-only scan 36explain (costs off) 37select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); 38 QUERY PLAN 39-------------------------------------------------------- 40 Index Only Scan using gist_tbl_point_index on gist_tbl 41 Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) 42(2 rows) 43 44-- execute the same 45select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)); 46 p 47------------- 48 (0,0) 49 (0.05,0.05) 50 (0.1,0.1) 51 (0.15,0.15) 52 (0.2,0.2) 53 (0.25,0.25) 54 (0.3,0.3) 55 (0.35,0.35) 56 (0.4,0.4) 57 (0.45,0.45) 58 (0.5,0.5) 59(11 rows) 60 61-- Also test an index-only knn-search 62explain (costs off) 63select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) 64order by p <-> point(0.201, 0.201); 65 QUERY PLAN 66-------------------------------------------------------- 67 Index Only Scan using gist_tbl_point_index on gist_tbl 68 Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) 69 Order By: (p <-> '(0.201,0.201)'::point) 70(3 rows) 71 72select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) 73order by p <-> point(0.201, 0.201); 74 p 75------------- 76 (0.2,0.2) 77 (0.25,0.25) 78 (0.15,0.15) 79 (0.3,0.3) 80 (0.1,0.1) 81 (0.35,0.35) 82 (0.05,0.05) 83 (0.4,0.4) 84 (0,0) 85 (0.45,0.45) 86 (0.5,0.5) 87(11 rows) 88 89-- Check commuted case as well 90explain (costs off) 91select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) 92order by point(0.101, 0.101) <-> p; 93 QUERY PLAN 94-------------------------------------------------------- 95 Index Only Scan using gist_tbl_point_index on gist_tbl 96 Index Cond: (p <@ '(0.5,0.5),(0,0)'::box) 97 Order By: (p <-> '(0.101,0.101)'::point) 98(3 rows) 99 100select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5)) 101order by point(0.101, 0.101) <-> p; 102 p 103------------- 104 (0.1,0.1) 105 (0.15,0.15) 106 (0.05,0.05) 107 (0.2,0.2) 108 (0,0) 109 (0.25,0.25) 110 (0.3,0.3) 111 (0.35,0.35) 112 (0.4,0.4) 113 (0.45,0.45) 114 (0.5,0.5) 115(11 rows) 116 117-- Check case with multiple rescans (bug #14641) 118explain (costs off) 119select p from 120 (values (box(point(0,0), point(0.5,0.5))), 121 (box(point(0.5,0.5), point(0.75,0.75))), 122 (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb) 123cross join lateral 124 (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss; 125 QUERY PLAN 126-------------------------------------------------------------------- 127 Nested Loop 128 -> Values Scan on "*VALUES*" 129 -> Limit 130 -> Index Only Scan using gist_tbl_point_index on gist_tbl 131 Index Cond: (p <@ "*VALUES*".column1) 132 Order By: (p <-> ("*VALUES*".column1)[0]) 133(6 rows) 134 135select p from 136 (values (box(point(0,0), point(0.5,0.5))), 137 (box(point(0.5,0.5), point(0.75,0.75))), 138 (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb) 139cross join lateral 140 (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss; 141 p 142------------- 143 (0.5,0.5) 144 (0.45,0.45) 145 (0.75,0.75) 146 (0.7,0.7) 147 (1,1) 148 (0.95,0.95) 149(6 rows) 150 151drop index gist_tbl_point_index; 152-- Test index-only scan with box opclass 153create index gist_tbl_box_index on gist_tbl using gist (b); 154-- check that the planner chooses an index-only scan 155explain (costs off) 156select b from gist_tbl where b <@ box(point(5,5), point(6,6)); 157 QUERY PLAN 158------------------------------------------------------ 159 Index Only Scan using gist_tbl_box_index on gist_tbl 160 Index Cond: (b <@ '(6,6),(5,5)'::box) 161(2 rows) 162 163-- execute the same 164select b from gist_tbl where b <@ box(point(5,5), point(6,6)); 165 b 166------------------------- 167 (5,5),(5,5) 168 (5.05,5.05),(5.05,5.05) 169 (5.1,5.1),(5.1,5.1) 170 (5.15,5.15),(5.15,5.15) 171 (5.2,5.2),(5.2,5.2) 172 (5.25,5.25),(5.25,5.25) 173 (5.3,5.3),(5.3,5.3) 174 (5.35,5.35),(5.35,5.35) 175 (5.4,5.4),(5.4,5.4) 176 (5.45,5.45),(5.45,5.45) 177 (5.5,5.5),(5.5,5.5) 178 (5.55,5.55),(5.55,5.55) 179 (5.6,5.6),(5.6,5.6) 180 (5.65,5.65),(5.65,5.65) 181 (5.7,5.7),(5.7,5.7) 182 (5.75,5.75),(5.75,5.75) 183 (5.8,5.8),(5.8,5.8) 184 (5.85,5.85),(5.85,5.85) 185 (5.9,5.9),(5.9,5.9) 186 (5.95,5.95),(5.95,5.95) 187 (6,6),(6,6) 188(21 rows) 189 190drop index gist_tbl_box_index; 191-- Test that an index-only scan is not chosen, when the query involves the 192-- circle column (the circle opclass does not support index-only scans). 193create index gist_tbl_multi_index on gist_tbl using gist (p, c); 194explain (costs off) 195select p, c from gist_tbl 196where p <@ box(point(5,5), point(6, 6)); 197 QUERY PLAN 198--------------------------------------------------- 199 Index Scan using gist_tbl_multi_index on gist_tbl 200 Index Cond: (p <@ '(6,6),(5,5)'::box) 201(2 rows) 202 203-- execute the same 204select b, p from gist_tbl 205where b <@ box(point(4.5, 4.5), point(5.5, 5.5)) 206and p <@ box(point(5,5), point(6, 6)); 207 b | p 208-------------------------+------------- 209 (5,5),(5,5) | (5,5) 210 (5.05,5.05),(5.05,5.05) | (5.05,5.05) 211 (5.1,5.1),(5.1,5.1) | (5.1,5.1) 212 (5.15,5.15),(5.15,5.15) | (5.15,5.15) 213 (5.2,5.2),(5.2,5.2) | (5.2,5.2) 214 (5.25,5.25),(5.25,5.25) | (5.25,5.25) 215 (5.3,5.3),(5.3,5.3) | (5.3,5.3) 216 (5.35,5.35),(5.35,5.35) | (5.35,5.35) 217 (5.4,5.4),(5.4,5.4) | (5.4,5.4) 218 (5.45,5.45),(5.45,5.45) | (5.45,5.45) 219 (5.5,5.5),(5.5,5.5) | (5.5,5.5) 220(11 rows) 221 222drop index gist_tbl_multi_index; 223-- Clean up 224reset enable_seqscan; 225reset enable_bitmapscan; 226reset enable_indexonlyscan; 227drop table gist_tbl; 228