summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/gist.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/gist.out')
-rw-r--r--src/test/regress/expected/gist.out403
1 files changed, 403 insertions, 0 deletions
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
new file mode 100644
index 0000000..c75bbb2
--- /dev/null
+++ b/src/test/regress/expected/gist.out
@@ -0,0 +1,403 @@
+--
+-- Test GiST indexes.
+--
+-- There are other tests to test different GiST opclasses. This is for
+-- testing GiST code itself. Vacuuming in particular.
+create table gist_point_tbl(id int4, p point);
+create index gist_pointidx on gist_point_tbl using gist(p);
+-- Verify the fillfactor and buffering options
+create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
+drop index gist_pointidx2, gist_pointidx3, gist_pointidx4;
+-- Make sure bad values are refused
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
+ERROR: invalid value for enum option "buffering": invalid_value
+DETAIL: Valid values are "on", "off", and "auto".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- Insert enough data to create a tree that's a couple of levels deep.
+insert into gist_point_tbl (id, p)
+select g, point(g*10, g*10) from generate_series(1, 10000) g;
+insert into gist_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 gist_point_tbl where id % 2 = 1;
+-- And also delete some concentration of values.
+delete from gist_point_tbl where id > 5000;
+vacuum analyze gist_point_tbl;
+-- rebuild the index with a different fillfactor
+alter index gist_pointidx SET (fillfactor = 40);
+reindex index gist_pointidx;
+--
+-- Test Index-only plans on GiST indexes
+--
+create table gist_tbl (b box, p point, c circle);
+insert into gist_tbl
+select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
+ point(0.05*i, 0.05*i),
+ circle(point(0.05*i, 0.05*i), 1.0)
+from generate_series(0,10000) as i;
+vacuum analyze gist_tbl;
+set enable_seqscan=off;
+set enable_bitmapscan=off;
+set enable_indexonlyscan=on;
+-- Test index-only scan with point opclass
+create index gist_tbl_point_index on gist_tbl using gist (p);
+-- check that the planner chooses an index-only scan
+explain (costs off)
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
+ QUERY PLAN
+--------------------------------------------------------
+ Index Only Scan using gist_tbl_point_index on gist_tbl
+ Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
+(2 rows)
+
+-- execute the same
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
+ p
+-------------
+ (0,0)
+ (0.05,0.05)
+ (0.1,0.1)
+ (0.15,0.15)
+ (0.2,0.2)
+ (0.25,0.25)
+ (0.3,0.3)
+ (0.35,0.35)
+ (0.4,0.4)
+ (0.45,0.45)
+ (0.5,0.5)
+(11 rows)
+
+-- Also test an index-only knn-search
+explain (costs off)
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
+order by p <-> point(0.201, 0.201);
+ QUERY PLAN
+--------------------------------------------------------
+ Index Only Scan using gist_tbl_point_index on gist_tbl
+ Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
+ Order By: (p <-> '(0.201,0.201)'::point)
+(3 rows)
+
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
+order by p <-> point(0.201, 0.201);
+ p
+-------------
+ (0.2,0.2)
+ (0.25,0.25)
+ (0.15,0.15)
+ (0.3,0.3)
+ (0.1,0.1)
+ (0.35,0.35)
+ (0.05,0.05)
+ (0.4,0.4)
+ (0,0)
+ (0.45,0.45)
+ (0.5,0.5)
+(11 rows)
+
+-- Check commuted case as well
+explain (costs off)
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
+order by point(0.101, 0.101) <-> p;
+ QUERY PLAN
+--------------------------------------------------------
+ Index Only Scan using gist_tbl_point_index on gist_tbl
+ Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
+ Order By: (p <-> '(0.101,0.101)'::point)
+(3 rows)
+
+select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
+order by point(0.101, 0.101) <-> p;
+ p
+-------------
+ (0.1,0.1)
+ (0.15,0.15)
+ (0.05,0.05)
+ (0.2,0.2)
+ (0,0)
+ (0.25,0.25)
+ (0.3,0.3)
+ (0.35,0.35)
+ (0.4,0.4)
+ (0.45,0.45)
+ (0.5,0.5)
+(11 rows)
+
+-- Check case with multiple rescans (bug #14641)
+explain (costs off)
+select p from
+ (values (box(point(0,0), point(0.5,0.5))),
+ (box(point(0.5,0.5), point(0.75,0.75))),
+ (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
+cross join lateral
+ (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Nested Loop
+ -> Values Scan on "*VALUES*"
+ -> Limit
+ -> Index Only Scan using gist_tbl_point_index on gist_tbl
+ Index Cond: (p <@ "*VALUES*".column1)
+ Order By: (p <-> ("*VALUES*".column1)[0])
+(6 rows)
+
+select p from
+ (values (box(point(0,0), point(0.5,0.5))),
+ (box(point(0.5,0.5), point(0.75,0.75))),
+ (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
+cross join lateral
+ (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
+ p
+-------------
+ (0.5,0.5)
+ (0.45,0.45)
+ (0.75,0.75)
+ (0.7,0.7)
+ (1,1)
+ (0.95,0.95)
+(6 rows)
+
+drop index gist_tbl_point_index;
+-- Test index-only scan with box opclass
+create index gist_tbl_box_index on gist_tbl using gist (b);
+-- check that the planner chooses an index-only scan
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6));
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+ Index Cond: (b <@ '(6,6),(5,5)'::box)
+(2 rows)
+
+-- execute the same
+select b from gist_tbl where b <@ box(point(5,5), point(6,6));
+ b
+-------------------------
+ (5,5),(5,5)
+ (5.05,5.05),(5.05,5.05)
+ (5.1,5.1),(5.1,5.1)
+ (5.15,5.15),(5.15,5.15)
+ (5.2,5.2),(5.2,5.2)
+ (5.25,5.25),(5.25,5.25)
+ (5.3,5.3),(5.3,5.3)
+ (5.35,5.35),(5.35,5.35)
+ (5.4,5.4),(5.4,5.4)
+ (5.45,5.45),(5.45,5.45)
+ (5.5,5.5),(5.5,5.5)
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.65,5.65),(5.65,5.65)
+ (5.7,5.7),(5.7,5.7)
+ (5.75,5.75),(5.75,5.75)
+ (5.8,5.8),(5.8,5.8)
+ (5.85,5.85),(5.85,5.85)
+ (5.9,5.9),(5.9,5.9)
+ (5.95,5.95),(5.95,5.95)
+ (6,6),(6,6)
+(21 rows)
+
+-- Also test an index-only knn-search
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+ Index Cond: (b <@ '(6,6),(5,5)'::box)
+ Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+ b
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
+-- Check commuted case as well
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+ Index Cond: (b <@ '(6,6),(5,5)'::box)
+ Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+ b
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
+drop index gist_tbl_box_index;
+-- Test that an index-only scan is not chosen, when the query involves the
+-- circle column (the circle opclass does not support index-only scans).
+create index gist_tbl_multi_index on gist_tbl using gist (p, c);
+explain (costs off)
+select p, c from gist_tbl
+where p <@ box(point(5,5), point(6, 6));
+ QUERY PLAN
+---------------------------------------------------
+ Index Scan using gist_tbl_multi_index on gist_tbl
+ Index Cond: (p <@ '(6,6),(5,5)'::box)
+(2 rows)
+
+-- execute the same
+select b, p from gist_tbl
+where b <@ box(point(4.5, 4.5), point(5.5, 5.5))
+and p <@ box(point(5,5), point(6, 6));
+ b | p
+-------------------------+-------------
+ (5,5),(5,5) | (5,5)
+ (5.05,5.05),(5.05,5.05) | (5.05,5.05)
+ (5.1,5.1),(5.1,5.1) | (5.1,5.1)
+ (5.15,5.15),(5.15,5.15) | (5.15,5.15)
+ (5.2,5.2),(5.2,5.2) | (5.2,5.2)
+ (5.25,5.25),(5.25,5.25) | (5.25,5.25)
+ (5.3,5.3),(5.3,5.3) | (5.3,5.3)
+ (5.35,5.35),(5.35,5.35) | (5.35,5.35)
+ (5.4,5.4),(5.4,5.4) | (5.4,5.4)
+ (5.45,5.45),(5.45,5.45) | (5.45,5.45)
+ (5.5,5.5),(5.5,5.5) | (5.5,5.5)
+(11 rows)
+
+drop index gist_tbl_multi_index;
+-- Test that we don't try to return the value of a non-returnable
+-- column in an index-only scan. (This isn't GIST-specific, but
+-- it only applies to index AMs that can return some columns and not
+-- others, so GIST with appropriate opclasses is a convenient test case.)
+create index gist_tbl_multi_index on gist_tbl using gist (circle(p,1), p);
+explain (verbose, costs off)
+select circle(p,1) from gist_tbl
+where p <@ box(point(5, 5), point(5.3, 5.3));
+ QUERY PLAN
+---------------------------------------------------------------
+ Index Only Scan using gist_tbl_multi_index on public.gist_tbl
+ Output: circle(p, '1'::double precision)
+ Index Cond: (gist_tbl.p <@ '(5.3,5.3),(5,5)'::box)
+(3 rows)
+
+select circle(p,1) from gist_tbl
+where p <@ box(point(5, 5), point(5.3, 5.3));
+ circle
+-----------------
+ <(5,5),1>
+ <(5.05,5.05),1>
+ <(5.1,5.1),1>
+ <(5.15,5.15),1>
+ <(5.2,5.2),1>
+ <(5.25,5.25),1>
+ <(5.3,5.3),1>
+(7 rows)
+
+-- Similarly, test that index rechecks involving a non-returnable column
+-- are done correctly.
+explain (verbose, costs off)
+select p from gist_tbl where circle(p,1) @> circle(point(0,0),0.95);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Index Only Scan using gist_tbl_multi_index on public.gist_tbl
+ Output: p
+ Index Cond: ((circle(gist_tbl.p, '1'::double precision)) @> '<(0,0),0.95>'::circle)
+(3 rows)
+
+select p from gist_tbl where circle(p,1) @> circle(point(0,0),0.95);
+ p
+-------
+ (0,0)
+(1 row)
+
+-- Also check that use_physical_tlist doesn't trigger in such cases.
+explain (verbose, costs off)
+select count(*) from gist_tbl;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ Output: count(*)
+ -> Index Only Scan using gist_tbl_multi_index on public.gist_tbl
+(3 rows)
+
+select count(*) from gist_tbl;
+ count
+-------
+ 10001
+(1 row)
+
+-- This case isn't supported, but it should at least EXPLAIN correctly.
+explain (verbose, costs off)
+select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Limit
+ Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point))
+ -> Index Only Scan using gist_tbl_multi_index on public.gist_tbl
+ Output: p, (circle(p, '1'::double precision) <-> '(0,0)'::point)
+ Order By: ((circle(gist_tbl.p, '1'::double precision)) <-> '(0,0)'::point)
+(5 rows)
+
+select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1;
+ERROR: lossy distance functions are not supported in index-only scans
+-- Force an index build using buffering.
+create index gist_tbl_box_index_forcing_buffering on gist_tbl using gist (p)
+ with (buffering=on, fillfactor=50);
+-- Clean up
+reset enable_seqscan;
+reset enable_bitmapscan;
+reset enable_indexonlyscan;
+drop table gist_tbl;
+-- test an unlogged table, mostly to get coverage of gistbuildempty
+create unlogged table gist_tbl (b box);
+create index gist_tbl_box_index on gist_tbl using gist (b);
+insert into gist_tbl
+ select box(point(0.05*i, 0.05*i)) from generate_series(0,10) as i;
+drop table gist_tbl;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-11 19:58:11 +0000