From 46651ce6fe013220ed397add242004d764fc0153 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:15:05 +0200 Subject: Adding upstream version 14.5. Signed-off-by: Daniel Baumann --- src/test/regress/expected/gist.out | 394 +++++++++++++++++++++++++++++++++++++ 1 file changed, 394 insertions(+) create mode 100644 src/test/regress/expected/gist.out (limited to 'src/test/regress/expected/gist.out') diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out new file mode 100644 index 0000000..2393132 --- /dev/null +++ b/src/test/regress/expected/gist.out @@ -0,0 +1,394 @@ +-- +-- 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 +-- Clean up +reset enable_seqscan; +reset enable_bitmapscan; +reset enable_indexonlyscan; +drop table gist_tbl; -- cgit v1.2.3