diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/spgist.out | 96 |
1 files changed, 96 insertions, 0 deletions
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out new file mode 100644 index 0000000..2e91128 --- /dev/null +++ b/src/test/regress/expected/spgist.out @@ -0,0 +1,96 @@ +-- +-- Test SP-GiST indexes. +-- +-- There are other tests to test different SP-GiST opclasses. This is for +-- testing SP-GiST code itself. +create table spgist_point_tbl(id int4, p point); +create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75); +-- Test vacuum-root operation. It gets invoked when the root is also a leaf, +-- i.e. the index is very small. +insert into spgist_point_tbl (id, p) +select g, point(g*10, g*10) from generate_series(1, 10) g; +delete from spgist_point_tbl where id < 5; +vacuum spgist_point_tbl; +-- Insert more data, to make the index a few levels deep. +insert into spgist_point_tbl (id, p) +select g, point(g*10, g*10) from generate_series(1, 10000) g; +insert into spgist_point_tbl (id, p) +select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g; +-- To test vacuum, delete some entries from all over the index. +delete from spgist_point_tbl where id % 2 = 1; +-- And also delete some concentration of values. (SP-GiST doesn't currently +-- attempt to delete pages even when they become empty, but if it did, this +-- would exercise it) +delete from spgist_point_tbl where id < 10000; +vacuum spgist_point_tbl; +-- Test rescan paths (cf. bug #15378) +-- use box and && rather than point, so that rescan happens when the +-- traverse stack is non-empty +create table spgist_box_tbl(id serial, b box); +insert into spgist_box_tbl(b) +select box(point(i,j),point(i+s,j+s)) + from generate_series(1,100,5) i, + generate_series(1,100,5) j, + generate_series(1,10) s; +create index spgist_box_idx on spgist_box_tbl using spgist (b); +select count(*) + from (values (point(5,5)),(point(8,8)),(point(12,12))) v(p) + where exists(select * from spgist_box_tbl b where b.b && box(v.p,v.p)); + count +------- + 3 +(1 row) + +-- The point opclass's choose method only uses the spgMatchNode action, +-- so the other actions are not tested by the above. Create an index using +-- text opclass, which uses the others actions. +create table spgist_text_tbl(id int4, t text); +create index spgist_text_idx on spgist_text_tbl using spgist(t); +insert into spgist_text_tbl (id, t) +select g, 'f' || repeat('o', 100) || g from generate_series(1, 10000) g +union all +select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g; +-- Do a lot of insertions that have to split an existing node. Hopefully +-- one of these will cause the page to run out of space, causing the inner +-- tuple to be moved to another page. +insert into spgist_text_tbl (id, t) +select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g; +-- Test out-of-range fillfactor values +create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9); +ERROR: value 9 out of bounds for option "fillfactor" +DETAIL: Valid values are between "10" and "100". +create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101); +ERROR: value 101 out of bounds for option "fillfactor" +DETAIL: Valid values are between "10" and "100". +-- Modify fillfactor in existing index +alter index spgist_point_idx set (fillfactor = 90); +reindex index spgist_point_idx; +-- Test index over a domain +create domain spgist_text as varchar; +create table spgist_domain_tbl (f1 spgist_text); +create index spgist_domain_idx on spgist_domain_tbl using spgist(f1); +insert into spgist_domain_tbl values('fee'), ('fi'), ('fo'), ('fum'); +explain (costs off) +select * from spgist_domain_tbl where f1 = 'fo'; + QUERY PLAN +----------------------------------------------- + Bitmap Heap Scan on spgist_domain_tbl + Recheck Cond: ((f1)::text = 'fo'::text) + -> Bitmap Index Scan on spgist_domain_idx + Index Cond: ((f1)::text = 'fo'::text) +(4 rows) + +select * from spgist_domain_tbl where f1 = 'fo'; + f1 +---- + fo +(1 row) + +-- test an unlogged table, mostly to get coverage of spgistbuildempty +create unlogged table spgist_unlogged_tbl(id serial, b box); +create index spgist_unlogged_idx on spgist_unlogged_tbl using spgist (b); +insert into spgist_unlogged_tbl(b) +select box(point(i,j)) + from generate_series(1,100,5) i, + generate_series(1,10,5) j; +-- leave this table around, to help in testing dump/restore |