path: root/src/test/regress/expected/gin.out

--
-- 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;
select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
 many 
------
 t
(1 row)

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
(1 row)

-- 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                       
-------------------------------------------------------
 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[])
(5 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                     
---------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on gin_test_tbl
         Recheck Cond: (i @> '{1}'::integer[])
         ->  Bitmap Index Scan on gin_test_idx
               Index Cond: (i @> '{1}'::integer[])
(5 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                     
---------------------------------------------------
 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[])
(4 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                     
---------------------------------------------------
 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[])
(4 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
select
  query,
  js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index",
  js->0->'Plan'->'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 t_gin_test_tbl where $$ || query) res_index,
  lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_heap;
                   query                   | return by index | removed by recheck | match 
-------------------------------------------+-----------------+--------------------+-------
  i @> '{}'                                | 7               | 0                  | t
  j @> '{}'                                | 6               | 0                  | t
  i @> '{}' and j @> '{}'                  | 4               | 0                  | t
  i @> '{1}'                               | 5               | 0                  | t
  i @> '{1}' and j @> '{}'                 | 3               | 0                  | t
  i @> '{1}' and i @> '{}' and j @> '{}'   | 3               | 0                  | t
  j @> '{10}'                              | 4               | 0                  | t
  j @> '{10}' and i @> '{}'                | 3               | 0                  | t
  j @> '{10}' and j @> '{}' and i @> '{}'  | 3               | 0                  | t
  i @> '{1}' and j @> '{10}'               | 2               | 0                  | t
(10 rows)

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
(1 row)

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                        
---------------------------------------------------------
 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[])
(5 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                        
---------------------------------------------------------
 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[])
(5 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                        
---------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on t_gin_test_tbl
         Recheck Cond: (j @> '{}'::integer[])
         ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
               Index Cond: (j @> '{}'::integer[])
(5 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;