src/test/regress/expected/gist.out - cloudberry - Git at Google

 --
 -- 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);
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table.
 HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 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);
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
 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;
 -- this query doesn't work with index scan on Cloudberry, check it before setting GUCs
 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)

 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
 --------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Index Only Scan using gist_tbl_point_index on gist_tbl
          Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
  Optimizer: Postgres query optimizer
 (4 rows)

 -- execute the same
 select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
       p
 -------------
  (0.35,0.35)
  (0.4,0.4)
  (0.15,0.15)
  (0.2,0.2)
  (0.3,0.3)
  (0.45,0.45)
  (0,0)
  (0.05,0.05)
  (0.1,0.1)
  (0.25,0.25)
  (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
 --------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Merge Key: ((p <-> '(0.201,0.201)'::point))
    ->  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)
  Optimizer: Postgres query optimizer
 (6 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
 --------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Merge Key: (('(0.101,0.101)'::point <-> p))
    ->  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)
  Optimizer: Postgres query optimizer
 (6 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*"
    ->  Materialize
          ->  Limit
                ->  Sort
                      Sort Key: ((gist_tbl.p <-> ("*VALUES*".column1)[0]))
                      ->  Result
                            Filter: (gist_tbl.p <@ "*VALUES*".column1)
                            ->  Materialize
                                  ->  Gather Motion 3:1  (slice1; segments: 3)
                                        ->  Seq Scan on gist_tbl
  Optimizer: Postgres query optimizer
 (12 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
 ------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Index Only Scan using gist_tbl_box_index on gist_tbl
          Index Cond: (b <@ '(6,6),(5,5)'::box)
  Optimizer: Postgres query optimizer
 (4 rows)

 -- execute the same
 select b from gist_tbl where b <@ box(point(5,5), point(6,6));
             b
 -------------------------
  (5.05,5.05),(5.05,5.05)
  (5.1,5.1),(5.1,5.1)
  (5.2,5.2),(5.2,5.2)
  (5.35,5.35),(5.35,5.35)
  (5.4,5.4),(5.4,5.4)
  (5.7,5.7),(5.7,5.7)
  (5.75,5.75),(5.75,5.75)
  (6,6),(6,6)
  (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.8,5.8),(5.8,5.8)
  (5.9,5.9),(5.9,5.9)
  (5.15,5.15),(5.15,5.15)
  (5.25,5.25),(5.25,5.25)
  (5.3,5.3),(5.3,5.3)
  (5.45,5.45),(5.45,5.45)
  (5.5,5.5),(5.5,5.5)
  (5.85,5.85),(5.85,5.85)
  (5.95,5.95),(5.95,5.95)
 (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
 ------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Merge Key: ((b <-> '(5.2,5.91)'::point))
    ->  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)
  Optimizer: Postgres query optimizer
 (6 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
 ------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Merge Key: (('(5.2,5.91)'::point <-> b))
    ->  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)
  Optimizer: Postgres query optimizer
 (6 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
 ---------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Index Scan using gist_tbl_multi_index on gist_tbl
          Index Cond: (p <@ '(6,6),(5,5)'::box)
  Optimizer: Postgres query optimizer
 (4 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.15,5.15),(5.15,5.15) | (5.15,5.15)
  (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.45,5.45),(5.45,5.45) | (5.45,5.45)
  (5.5,5.5),(5.5,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.2,5.2),(5.2,5.2)     | (5.2,5.2)
  (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,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
 ------------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Output: (circle(p, '1'::double precision))
    ->  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)
  Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
  Optimizer: Postgres query optimizer
 (7 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
 ---------------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    Output: p
    ->  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)
  Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
  Optimizer: Postgres query optimizer
 (7 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
 ------------------------------------------------------------------------------------------
  Finalize Aggregate
    Output: count(*)
    ->  Gather Motion 3:1  (slice1; segments: 3)
          Output: (PARTIAL count(*))
          ->  Partial Aggregate
                Output: PARTIAL count(*)
                ->  Index Only Scan using gist_tbl_multi_index on public.gist_tbl
  Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
  Optimizer: Postgres query optimizer
 (9 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), p <-> point(0,0) limit 15;
                                                               QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------------------------
  Limit
    Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
    ->  Gather Motion 3:1  (slice1; segments: 3)
          Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
          Merge Key: ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
          ->  Limit
                Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(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), (p <-> '(0,0)'::point)
                      Order By: (((circle(gist_tbl.p, '1'::double precision)) <-> '(0,0)'::point) AND (gist_tbl.p <-> '(0,0)'::point))
  Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off', optimizer = 'off'
  Optimizer: Postgres query optimizer
 (12 rows)

 select p from gist_tbl order by circle(p,1) <-> point(0,0), p <-> point(0,0) limit 15;
 ERROR:  lossy distance functions are not supported in index-only scans  (seg0 slice1 127.0.1.1:7002 pid=1227707)
 -- 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;
	--
	-- 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);
	NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table.
	HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
	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(g10, g10) from generate_series(1, 10000) g;
	insert into gist_point_tbl (id, p)
	select g+100000, point(g10+1, g10+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);
	NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
	insert into gist_tbl
	select box(point(0.05i, 0.05i), point(0.05i, 0.05i)),
	point(0.05i, 0.05i),
	circle(point(0.05i, 0.05i), 1.0)
	from generate_series(0,10000) as i;
	vacuum analyze gist_tbl;
	-- this query doesn't work with index scan on Cloudberry, check it before setting GUCs
	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)

	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
	--------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Index Only Scan using gist_tbl_point_index on gist_tbl
	Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
	Optimizer: Postgres query optimizer
	(4 rows)

	-- execute the same
	select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
	p
	-------------
	(0.35,0.35)
	(0.4,0.4)
	(0.15,0.15)
	(0.2,0.2)
	(0.3,0.3)
	(0.45,0.45)
	(0,0)
	(0.05,0.05)
	(0.1,0.1)
	(0.25,0.25)
	(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
	--------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Merge Key: ((p <-> '(0.201,0.201)'::point))
	-> 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)
	Optimizer: Postgres query optimizer
	(6 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
	--------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Merge Key: (('(0.101,0.101)'::point <-> p))
	-> 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)
	Optimizer: Postgres query optimizer
	(6 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"
	-> Materialize
	-> Limit
	-> Sort
	Sort Key: ((gist_tbl.p <-> ("VALUES".column1)[0]))
	-> Result
	Filter: (gist_tbl.p <@ "VALUES".column1)
	-> Materialize
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Seq Scan on gist_tbl
	Optimizer: Postgres query optimizer
	(12 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
	------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Index Only Scan using gist_tbl_box_index on gist_tbl
	Index Cond: (b <@ '(6,6),(5,5)'::box)
	Optimizer: Postgres query optimizer
	(4 rows)

	-- execute the same
	select b from gist_tbl where b <@ box(point(5,5), point(6,6));
	b
	-------------------------
	(5.05,5.05),(5.05,5.05)
	(5.1,5.1),(5.1,5.1)
	(5.2,5.2),(5.2,5.2)
	(5.35,5.35),(5.35,5.35)
	(5.4,5.4),(5.4,5.4)
	(5.7,5.7),(5.7,5.7)
	(5.75,5.75),(5.75,5.75)
	(6,6),(6,6)
	(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.8,5.8),(5.8,5.8)
	(5.9,5.9),(5.9,5.9)
	(5.15,5.15),(5.15,5.15)
	(5.25,5.25),(5.25,5.25)
	(5.3,5.3),(5.3,5.3)
	(5.45,5.45),(5.45,5.45)
	(5.5,5.5),(5.5,5.5)
	(5.85,5.85),(5.85,5.85)
	(5.95,5.95),(5.95,5.95)
	(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
	------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Merge Key: ((b <-> '(5.2,5.91)'::point))
	-> 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)
	Optimizer: Postgres query optimizer
	(6 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
	------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Merge Key: (('(5.2,5.91)'::point <-> b))
	-> 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)
	Optimizer: Postgres query optimizer
	(6 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
	---------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Index Scan using gist_tbl_multi_index on gist_tbl
	Index Cond: (p <@ '(6,6),(5,5)'::box)
	Optimizer: Postgres query optimizer
	(4 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.15,5.15),(5.15,5.15) \| (5.15,5.15)
	(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.45,5.45),(5.45,5.45) \| (5.45,5.45)
	(5.5,5.5),(5.5,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.2,5.2),(5.2,5.2) \| (5.2,5.2)
	(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,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
	------------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Output: (circle(p, '1'::double precision))
	-> 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)
	Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
	Optimizer: Postgres query optimizer
	(7 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
	---------------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	Output: p
	-> 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)
	Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
	Optimizer: Postgres query optimizer
	(7 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
	------------------------------------------------------------------------------------------
	Finalize Aggregate
	Output: count(*)
	-> Gather Motion 3:1 (slice1; segments: 3)
	Output: (PARTIAL count(*))
	-> Partial Aggregate
	Output: PARTIAL count(*)
	-> Index Only Scan using gist_tbl_multi_index on public.gist_tbl
	Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off'
	Optimizer: Postgres query optimizer
	(9 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), p <-> point(0,0) limit 15;
	QUERY PLAN
	--------------------------------------------------------------------------------------------------------------------------------------
	Limit
	Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
	-> Gather Motion 3:1 (slice1; segments: 3)
	Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
	Merge Key: ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(0,0)'::point))
	-> Limit
	Output: p, ((circle(p, '1'::double precision) <-> '(0,0)'::point)), ((p <-> '(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), (p <-> '(0,0)'::point)
	Order By: (((circle(gist_tbl.p, '1'::double precision)) <-> '(0,0)'::point) AND (gist_tbl.p <-> '(0,0)'::point))
	Settings: enable_bitmapscan = 'off', enable_indexonlyscan = 'on', enable_seqscan = 'off', optimizer = 'off'
	Optimizer: Postgres query optimizer
	(12 rows)

	select p from gist_tbl order by circle(p,1) <-> point(0,0), p <-> point(0,0) limit 15;
	ERROR: lossy distance functions are not supported in index-only scans (seg0 slice1 127.0.1.1:7002 pid=1227707)
	-- 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.05i, 0.05i)) from generate_series(0,10) as i;
	drop table gist_tbl;