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

 --
 -- Test GIN indexes.
 --
 -- There are other tests to test different GIN opclasses. This is for testing
 -- GIN itself.
 -- Create and populate a test table with a GIN index.
 create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off);
 create index gin_test_idx on gin_test_tbl using gin (i)
   with (fastupdate = on, gin_pending_list_limit = 4096);
 insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
 insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
 analyze gin_test_tbl;
 select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
  many
 ------
  t
  t
  t
 (3 rows)

 insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
 vacuum gin_test_tbl; -- flush the fastupdate buffers
 select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
  gin_clean_pending_list
 ------------------------
                       0
                       0
                       0
 (3 rows)

 -- Test vacuuming
 delete from gin_test_tbl where i @> array[2];
 vacuum gin_test_tbl;
 -- Disable fastupdate, and do more insertions. With fastupdate enabled, most
 -- insertions (by flushing the list pages) cause page splits. Without
 -- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
 -- recompression codepaths.
 alter index gin_test_idx set (fastupdate = off);
 insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g;
 insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
 delete from gin_test_tbl where i @> array[2];
 vacuum gin_test_tbl;
 -- Test for "rare && frequent" searches
 explain (costs off)
 select count(*) from gin_test_tbl where i @> array[1, 999];
                       QUERY PLAN
 -------------------------------------------------------
  Finalize Aggregate
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Partial Aggregate
                ->  Bitmap Heap Scan on gin_test_tbl
                      Recheck Cond: (i @> '{1,999}'::integer[])
                      ->  Bitmap Index Scan on gin_test_idx
                            Index Cond: (i @> '{1,999}'::integer[])
  Optimizer: Postgres query optimizer
 (8 rows)

 select count(*) from gin_test_tbl where i @> array[1, 999];
  count
 -------
      3
 (1 row)

 -- Very weak test for gin_fuzzy_search_limit
 set gin_fuzzy_search_limit = 1000;
 explain (costs off)
 select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
                     QUERY PLAN
 ---------------------------------------------------
  Finalize Aggregate
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Partial Aggregate
                ->  Seq Scan on gin_test_tbl
                      Filter: (i @> '{1}'::integer[])
  Optimizer: Postgres query optimizer
 (6 rows)

 select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
  ok
 ----
  t
 (1 row)

 reset gin_fuzzy_search_limit;
 -- Test optimization of empty queries
 create temp table t_gin_test_tbl(i int4[], j int4[]);
 create index on t_gin_test_tbl using gin (i, j);
 insert into t_gin_test_tbl
 values
   (null,    null),
   ('{}',    null),
   ('{1}',   null),
   ('{1,2}', null),
   (null,    '{}'),
   (null,    '{10}'),
   ('{1,2}', '{10}'),
   ('{2}',   '{10}'),
   ('{1,3}', '{}'),
   ('{1,1}', '{10}');
 set enable_seqscan = off;
 explain (costs off)
 select * from t_gin_test_tbl where array[0] <@ i;
                     QUERY PLAN
 ---------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Bitmap Heap Scan on t_gin_test_tbl
          Recheck Cond: ('{0}'::integer[] <@ i)
          ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
                Index Cond: (i @> '{0}'::integer[])
  Optimizer: Postgres query optimizer
 (6 rows)

 select * from t_gin_test_tbl where array[0] <@ i;
  i | j
 ---+---
 (0 rows)

 select * from t_gin_test_tbl where array[0] <@ i and '{}'::int4[] <@ j;
  i | j
 ---+---
 (0 rows)

 explain (costs off)
 select * from t_gin_test_tbl where i @> '{}';
                     QUERY PLAN
 ---------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)
    ->  Bitmap Heap Scan on t_gin_test_tbl
          Recheck Cond: (i @> '{}'::integer[])
          ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
                Index Cond: (i @> '{}'::integer[])
  Optimizer: Postgres query optimizer
 (6 rows)

 select * from t_gin_test_tbl where i @> '{}';
    i   |  j
 -------+------
  {}    |
  {1}   |
  {1,2} |
  {1,2} | {10}
  {2}   | {10}
  {1,3} | {}
  {1,1} | {10}
 (7 rows)

 create function explain_query_json(query_sql text)
 returns table (explain_line json)
 language plpgsql as
 $$
 begin
   set enable_seqscan = off;
   set enable_bitmapscan = on;
   return query execute 'EXPLAIN (ANALYZE, FORMAT json) ' || query_sql;
 end;
 $$;
 create function execute_text_query_index(query_sql text)
 returns setof text
 language plpgsql
 as
 $$
 begin
   set enable_seqscan = off;
   set enable_bitmapscan = on;
   return query execute query_sql;
 end;
 $$;
 create function execute_text_query_heap(query_sql text)
 returns setof text
 language plpgsql
 as
 $$
 begin
   set enable_seqscan = on;
   set enable_bitmapscan = off;
   return query execute query_sql;
 end;
 $$;
 -- check number of rows returned by index and removed by recheck
 -- start_ignore
 select
   query,
   js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index",
   js->0->'Plan'->'Plans'->0->'Rows Removed by Index Recheck' as "removed by recheck",
   (res_index = res_heap) as "match"
 from
   (values
     ($$ i @> '{}' $$),
     ($$ j @> '{}' $$),
     ($$ i @> '{}' and j @> '{}' $$),
     ($$ i @> '{1}' $$),
     ($$ i @> '{1}' and j @> '{}' $$),
     ($$ i @> '{1}' and i @> '{}' and j @> '{}' $$),
     ($$ j @> '{10}' $$),
     ($$ j @> '{10}' and i @> '{}' $$),
     ($$ j @> '{10}' and j @> '{}' and i @> '{}' $$),
     ($$ i @> '{1}' and j @> '{10}' $$)
   ) q(query),
   lateral explain_query_json($$select * from t_gin_test_tbl where $$ || query) js,
   lateral execute_text_query_index($$select string_agg((i, j)::text, ' ') from ( select * from t_gin_test_tbl where $$ || query || $$ order by i ) a$$ ) res_index,
   lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from ( select * from t_gin_test_tbl where $$ || query || $$ order by i ) a $$ ) res_heap;
                    query                   | return by index | removed by recheck | match
 -------------------------------------------+-----------------+--------------------+-------
   i @> '{}'                                | 4               | 0                  | t
   j @> '{}'                                | 5               | 0                  | f
   i @> '{}' and j @> '{}'                  | 3               | 0                  | t
   i @> '{1}'                               | 2               | 0                  | t
   i @> '{1}' and j @> '{}'                 | 2               | 0                  | t
   i @> '{1}' and i @> '{}' and j @> '{}'   | 2               | 0                  | t
   j @> '{10}'                              | 3               | 0                  | f
   j @> '{10}' and i @> '{}'                | 2               | 0                  | f
   j @> '{10}' and j @> '{}' and i @> '{}'  | 2               | 0                  | t
   i @> '{1}' and j @> '{10}'               | 1               | 0                  | t
 (10 rows)

 -- end_ignore
 reset enable_seqscan;
 reset enable_bitmapscan;
 -- re-purpose t_gin_test_tbl to test scans involving posting trees
 insert into t_gin_test_tbl select array[1, g, g/10], array[2, g, g/10]
   from generate_series(1, 20000) g;
 select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') is not null;
  ?column?
 ----------
  t
  t
  t
 (3 rows)

 analyze t_gin_test_tbl;
 set enable_seqscan = off;
 set enable_bitmapscan = on;
 explain (costs off)
 select count(*) from t_gin_test_tbl where j @> array[50];
                              QUERY PLAN
 ---------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Partial Aggregate
                ->  Bitmap Heap Scan on t_gin_test_tbl
                      Recheck Cond: (j @> '{50}'::integer[])
                      ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
                            Index Cond: (j @> '{50}'::integer[])
  Optimizer: Postgres query optimizer
 (8 rows)

 select count(*) from t_gin_test_tbl where j @> array[50];
  count
 -------
     11
 (1 row)

 explain (costs off)
 select count(*) from t_gin_test_tbl where j @> array[2];
                              QUERY PLAN
 ---------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Partial Aggregate
                ->  Bitmap Heap Scan on t_gin_test_tbl
                      Recheck Cond: (j @> '{2}'::integer[])
                      ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
                            Index Cond: (j @> '{2}'::integer[])
  Optimizer: Postgres query optimizer
 (8 rows)

 select count(*) from t_gin_test_tbl where j @> array[2];
  count
 -------
  20000
 (1 row)

 explain (costs off)
 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
                                     QUERY PLAN
 ----------------------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather Motion 3:1  (slice1; segments: 3)
          ->  Partial Aggregate
                ->  Bitmap Heap Scan on t_gin_test_tbl
                      Recheck Cond: (j OPERATOR(pg_catalog.@>) '{}'::anyarray)
                      ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
                            Index Cond: (j OPERATOR(pg_catalog.@>) '{}'::anyarray)
  Optimizer: Postgres query optimizer
 (8 rows)

 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
  count
 -------
  20006
 (1 row)

 -- test vacuuming of posting trees
 delete from t_gin_test_tbl where j @> array[2];
 vacuum t_gin_test_tbl;
 select count(*) from t_gin_test_tbl where j @> array[50];
  count
 -------
      0
 (1 row)

 select count(*) from t_gin_test_tbl where j @> array[2];
  count
 -------
      0
 (1 row)

 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
  count
 -------
      6
 (1 row)

 reset enable_seqscan;
 reset enable_bitmapscan;
 drop table t_gin_test_tbl;
 -- test an unlogged table, mostly to get coverage of ginbuildempty
 create unlogged table t_gin_test_tbl(i int4[], j int4[]);
 create index on t_gin_test_tbl using gin (i, j);
 insert into t_gin_test_tbl
 values
   (null,    null),
   ('{}',    null),
   ('{1}',   '{2,3}');
 drop table t_gin_test_tbl;
	--
	-- Test GIN indexes.
	--
	-- There are other tests to test different GIN opclasses. This is for testing
	-- GIN itself.
	-- Create and populate a test table with a GIN index.
	create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off);
	create index gin_test_idx on gin_test_tbl using gin (i)
	with (fastupdate = on, gin_pending_list_limit = 4096);
	insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
	insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
	analyze gin_test_tbl;
	select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
	many
	------
	t
	t
	t
	(3 rows)

	insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
	vacuum gin_test_tbl; -- flush the fastupdate buffers
	select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
	gin_clean_pending_list
	------------------------
	0
	0
	0
	(3 rows)

	-- Test vacuuming
	delete from gin_test_tbl where i @> array[2];
	vacuum gin_test_tbl;
	-- Disable fastupdate, and do more insertions. With fastupdate enabled, most
	-- insertions (by flushing the list pages) cause page splits. Without
	-- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
	-- recompression codepaths.
	alter index gin_test_idx set (fastupdate = off);
	insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g;
	insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
	delete from gin_test_tbl where i @> array[2];
	vacuum gin_test_tbl;
	-- Test for "rare && frequent" searches
	explain (costs off)
	select count(*) from gin_test_tbl where i @> array[1, 999];
	QUERY PLAN
	-------------------------------------------------------
	Finalize Aggregate
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Partial Aggregate
	-> Bitmap Heap Scan on gin_test_tbl
	Recheck Cond: (i @> '{1,999}'::integer[])
	-> Bitmap Index Scan on gin_test_idx
	Index Cond: (i @> '{1,999}'::integer[])
	Optimizer: Postgres query optimizer
	(8 rows)

	select count(*) from gin_test_tbl where i @> array[1, 999];
	count
	-------
	3
	(1 row)

	-- Very weak test for gin_fuzzy_search_limit
	set gin_fuzzy_search_limit = 1000;
	explain (costs off)
	select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
	QUERY PLAN
	---------------------------------------------------
	Finalize Aggregate
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Partial Aggregate
	-> Seq Scan on gin_test_tbl
	Filter: (i @> '{1}'::integer[])
	Optimizer: Postgres query optimizer
	(6 rows)

	select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
	ok
	----
	t
	(1 row)

	reset gin_fuzzy_search_limit;
	-- Test optimization of empty queries
	create temp table t_gin_test_tbl(i int4[], j int4[]);
	create index on t_gin_test_tbl using gin (i, j);
	insert into t_gin_test_tbl
	values
	(null, null),
	('{}', null),
	('{1}', null),
	('{1,2}', null),
	(null, '{}'),
	(null, '{10}'),
	('{1,2}', '{10}'),
	('{2}', '{10}'),
	('{1,3}', '{}'),
	('{1,1}', '{10}');
	set enable_seqscan = off;
	explain (costs off)
	select * from t_gin_test_tbl where array[0] <@ i;
	QUERY PLAN
	---------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Bitmap Heap Scan on t_gin_test_tbl
	Recheck Cond: ('{0}'::integer[] <@ i)
	-> Bitmap Index Scan on t_gin_test_tbl_i_j_idx
	Index Cond: (i @> '{0}'::integer[])
	Optimizer: Postgres query optimizer
	(6 rows)

	select * from t_gin_test_tbl where array[0] <@ i;
	i \| j
	---+---
	(0 rows)

	select * from t_gin_test_tbl where array[0] <@ i and '{}'::int4[] <@ j;
	i \| j
	---+---
	(0 rows)

	explain (costs off)
	select * from t_gin_test_tbl where i @> '{}';
	QUERY PLAN
	---------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3)
	-> Bitmap Heap Scan on t_gin_test_tbl
	Recheck Cond: (i @> '{}'::integer[])
	-> Bitmap Index Scan on t_gin_test_tbl_i_j_idx
	Index Cond: (i @> '{}'::integer[])
	Optimizer: Postgres query optimizer
	(6 rows)

	select * from t_gin_test_tbl where i @> '{}';
	i \| j
	-------+------
	{} \|
	{1} \|
	{1,2} \|
	{1,2} \| {10}
	{2} \| {10}
	{1,3} \| {}
	{1,1} \| {10}
	(7 rows)

	create function explain_query_json(query_sql text)
	returns table (explain_line json)
	language plpgsql as
	$$
	begin
	set enable_seqscan = off;
	set enable_bitmapscan = on;
	return query execute 'EXPLAIN (ANALYZE, FORMAT json) ' \|\| query_sql;
	end;
	$$;
	create function execute_text_query_index(query_sql text)
	returns setof text
	language plpgsql
	as
	$$
	begin
	set enable_seqscan = off;
	set enable_bitmapscan = on;
	return query execute query_sql;
	end;
	$$;
	create function execute_text_query_heap(query_sql text)
	returns setof text
	language plpgsql
	as
	$$
	begin
	set enable_seqscan = on;
	set enable_bitmapscan = off;
	return query execute query_sql;
	end;
	$$;
	-- check number of rows returned by index and removed by recheck
	-- start_ignore
	select
	query,
	js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index",
	js->0->'Plan'->'Plans'->0->'Rows Removed by Index Recheck' as "removed by recheck",
	(res_index = res_heap) as "match"
	from
	(values
	($$ i @> '{}' $$),
	($$ j @> '{}' $$),
	($$ i @> '{}' and j @> '{}' $$),
	($$ i @> '{1}' $$),
	($$ i @> '{1}' and j @> '{}' $$),
	($$ i @> '{1}' and i @> '{}' and j @> '{}' $$),
	($$ j @> '{10}' $$),
	($$ j @> '{10}' and i @> '{}' $$),
	($$ j @> '{10}' and j @> '{}' and i @> '{}' $$),
	($$ i @> '{1}' and j @> '{10}' $$)
	) q(query),
	lateral explain_query_json($$select * from t_gin_test_tbl where $$ \|\| query) js,
	lateral execute_text_query_index($$select string_agg((i, j)::text, ' ') from ( select * from t_gin_test_tbl where $$ \|\| query \|\| $$ order by i ) a$$ ) res_index,
	lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from ( select * from t_gin_test_tbl where $$ \|\| query \|\| $$ order by i ) a $$ ) res_heap;
	query \| return by index \| removed by recheck \| match
	-------------------------------------------+-----------------+--------------------+-------
	i @> '{}' \| 4 \| 0 \| t
	j @> '{}' \| 5 \| 0 \| f
	i @> '{}' and j @> '{}' \| 3 \| 0 \| t
	i @> '{1}' \| 2 \| 0 \| t
	i @> '{1}' and j @> '{}' \| 2 \| 0 \| t
	i @> '{1}' and i @> '{}' and j @> '{}' \| 2 \| 0 \| t
	j @> '{10}' \| 3 \| 0 \| f
	j @> '{10}' and i @> '{}' \| 2 \| 0 \| f
	j @> '{10}' and j @> '{}' and i @> '{}' \| 2 \| 0 \| t
	i @> '{1}' and j @> '{10}' \| 1 \| 0 \| t
	(10 rows)

	-- end_ignore
	reset enable_seqscan;
	reset enable_bitmapscan;
	-- re-purpose t_gin_test_tbl to test scans involving posting trees
	insert into t_gin_test_tbl select array[1, g, g/10], array[2, g, g/10]
	from generate_series(1, 20000) g;
	select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') is not null;
	?column?
	----------
	t
	t
	t
	(3 rows)

	analyze t_gin_test_tbl;
	set enable_seqscan = off;
	set enable_bitmapscan = on;
	explain (costs off)
	select count(*) from t_gin_test_tbl where j @> array[50];
	QUERY PLAN
	---------------------------------------------------------------------
	Finalize Aggregate
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Partial Aggregate
	-> Bitmap Heap Scan on t_gin_test_tbl
	Recheck Cond: (j @> '{50}'::integer[])
	-> Bitmap Index Scan on t_gin_test_tbl_i_j_idx
	Index Cond: (j @> '{50}'::integer[])
	Optimizer: Postgres query optimizer
	(8 rows)

	select count(*) from t_gin_test_tbl where j @> array[50];
	count
	-------
	11
	(1 row)

	explain (costs off)
	select count(*) from t_gin_test_tbl where j @> array[2];
	QUERY PLAN
	---------------------------------------------------------------------
	Finalize Aggregate
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Partial Aggregate
	-> Bitmap Heap Scan on t_gin_test_tbl
	Recheck Cond: (j @> '{2}'::integer[])
	-> Bitmap Index Scan on t_gin_test_tbl_i_j_idx
	Index Cond: (j @> '{2}'::integer[])
	Optimizer: Postgres query optimizer
	(8 rows)

	select count(*) from t_gin_test_tbl where j @> array[2];
	count
	-------
	20000
	(1 row)

	explain (costs off)
	select count(*) from t_gin_test_tbl where j @> '{}'::int[];
	QUERY PLAN
	----------------------------------------------------------------------------------
	Finalize Aggregate
	-> Gather Motion 3:1 (slice1; segments: 3)
	-> Partial Aggregate
	-> Bitmap Heap Scan on t_gin_test_tbl
	Recheck Cond: (j OPERATOR(pg_catalog.@>) '{}'::anyarray)
	-> Bitmap Index Scan on t_gin_test_tbl_i_j_idx
	Index Cond: (j OPERATOR(pg_catalog.@>) '{}'::anyarray)
	Optimizer: Postgres query optimizer
	(8 rows)

	select count(*) from t_gin_test_tbl where j @> '{}'::int[];
	count
	-------
	20006
	(1 row)

	-- test vacuuming of posting trees
	delete from t_gin_test_tbl where j @> array[2];
	vacuum t_gin_test_tbl;
	select count(*) from t_gin_test_tbl where j @> array[50];
	count
	-------
	0
	(1 row)

	select count(*) from t_gin_test_tbl where j @> array[2];
	count
	-------
	0
	(1 row)

	select count(*) from t_gin_test_tbl where j @> '{}'::int[];
	count
	-------
	6
	(1 row)

	reset enable_seqscan;
	reset enable_bitmapscan;
	drop table t_gin_test_tbl;
	-- test an unlogged table, mostly to get coverage of ginbuildempty
	create unlogged table t_gin_test_tbl(i int4[], j int4[]);
	create index on t_gin_test_tbl using gin (i, j);
	insert into t_gin_test_tbl
	values
	(null, null),
	('{}', null),
	('{1}', '{2,3}');
	drop table t_gin_test_tbl;