src/test/regress/expected/gin.out

   1 --
   2 -- Test GIN indexes.
   3 --
   4 -- There are other tests to test different GIN opclasses. This is for testing
   5 -- GIN itself.
   6 -- Create and populate a test table with a GIN index.
   7 create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off);
   8 create index gin_test_idx on gin_test_tbl using gin (i)
   9   with (fastupdate = on, gin_pending_list_limit = 4096);
  10 insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
  11 insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
  12 select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
  13  many
  14 ------
  15  t
  16 (1 row)
  17
  18 insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
  19 vacuum gin_test_tbl; -- flush the fastupdate buffers
  20 select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
  21  gin_clean_pending_list
  22 ------------------------
  23                       0
  24 (1 row)
  25
  26 -- Test vacuuming
  27 delete from gin_test_tbl where i @> array[2];
  28 vacuum gin_test_tbl;
  29 -- Disable fastupdate, and do more insertions. With fastupdate enabled, most
  30 -- insertions (by flushing the list pages) cause page splits. Without
  31 -- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
  32 -- recompression codepaths.
  33 alter index gin_test_idx set (fastupdate = off);
  34 insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g;
  35 insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
  36 delete from gin_test_tbl where i @> array[2];
  37 vacuum gin_test_tbl;
  38 -- Test for "rare && frequent" searches
  39 explain (costs off)
  40 select count(*) from gin_test_tbl where i @> array[1, 999];
  41                       QUERY PLAN
  42 -------------------------------------------------------
  43  Aggregate
  44    ->  Bitmap Heap Scan on gin_test_tbl
  45          Recheck Cond: (i @> '{1,999}'::integer[])
  46          ->  Bitmap Index Scan on gin_test_idx
  47                Index Cond: (i @> '{1,999}'::integer[])
  48 (5 rows)
  49
  50 select count(*) from gin_test_tbl where i @> array[1, 999];
  51  count
  52 -------
  53      3
  54 (1 row)
  55
  56 -- Very weak test for gin_fuzzy_search_limit
  57 set gin_fuzzy_search_limit = 1000;
  58 explain (costs off)
  59 select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
  60                     QUERY PLAN
  61 ---------------------------------------------------
  62  Aggregate
  63    ->  Bitmap Heap Scan on gin_test_tbl
  64          Recheck Cond: (i @> '{1}'::integer[])
  65          ->  Bitmap Index Scan on gin_test_idx
  66                Index Cond: (i @> '{1}'::integer[])
  67 (5 rows)
  68
  69 select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
  70  ok
  71 ----
  72  t
  73 (1 row)
  74
  75 reset gin_fuzzy_search_limit;
  76 -- Test optimization of empty queries
  77 create temp table t_gin_test_tbl(i int4[], j int4[]);
  78 create index on t_gin_test_tbl using gin (i, j);
  79 insert into t_gin_test_tbl
  80 values
  81   (null,    null),
  82   ('{}',    null),
  83   ('{1}',   null),
  84   ('{1,2}', null),
  85   (null,    '{}'),
  86   (null,    '{10}'),
  87   ('{1,2}', '{10}'),
  88   ('{2}',   '{10}'),
  89   ('{1,3}', '{}'),
  90   ('{1,1}', '{10}');
  91 set enable_seqscan = off;
  92 explain (costs off)
  93 select * from t_gin_test_tbl where array[0] <@ i;
  94                     QUERY PLAN
  95 ---------------------------------------------------
  96  Bitmap Heap Scan on t_gin_test_tbl
  97    Recheck Cond: ('{0}'::integer[] <@ i)
  98    ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
  99          Index Cond: (i @> '{0}'::integer[])
 100 (4 rows)
 101
 102 select * from t_gin_test_tbl where array[0] <@ i;
 103  i | j
 104 ---+---
 105 (0 rows)
 106
 107 select * from t_gin_test_tbl where array[0] <@ i and '{}'::int4[] <@ j;
 108  i | j
 109 ---+---
 110 (0 rows)
 111
 112 explain (costs off)
 113 select * from t_gin_test_tbl where i @> '{}';
 114                     QUERY PLAN
 115 ---------------------------------------------------
 116  Bitmap Heap Scan on t_gin_test_tbl
 117    Recheck Cond: (i @> '{}'::integer[])
 118    ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
 119          Index Cond: (i @> '{}'::integer[])
 120 (4 rows)
 121
 122 select * from t_gin_test_tbl where i @> '{}';
 123    i   |  j
 124 -------+------
 125  {}    |
 126  {1}   |
 127  {1,2} |
 128  {1,2} | {10}
 129  {2}   | {10}
 130  {1,3} | {}
 131  {1,1} | {10}
 132 (7 rows)
 133
 134 create function explain_query_json(query_sql text)
 135 returns table (explain_line json)
 136 language plpgsql as
 137 $$
 138 begin
 139   set enable_seqscan = off;
 140   set enable_bitmapscan = on;
 141   return query execute 'EXPLAIN (ANALYZE, FORMAT json) ' || query_sql;
 142 end;
 143 $$;
 144 create function execute_text_query_index(query_sql text)
 145 returns setof text
 146 language plpgsql
 147 as
 148 $$
 149 begin
 150   set enable_seqscan = off;
 151   set enable_bitmapscan = on;
 152   return query execute query_sql;
 153 end;
 154 $$;
 155 create function execute_text_query_heap(query_sql text)
 156 returns setof text
 157 language plpgsql
 158 as
 159 $$
 160 begin
 161   set enable_seqscan = on;
 162   set enable_bitmapscan = off;
 163   return query execute query_sql;
 164 end;
 165 $$;
 166 -- check number of rows returned by index and removed by recheck
 167 select
 168   query,
 169   js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index",
 170   js->0->'Plan'->'Rows Removed by Index Recheck' as "removed by recheck",
 171   (res_index = res_heap) as "match"
 172 from
 173   (values
 174     ($$ i @> '{}' $$),
 175     ($$ j @> '{}' $$),
 176     ($$ i @> '{}' and j @> '{}' $$),
 177     ($$ i @> '{1}' $$),
 178     ($$ i @> '{1}' and j @> '{}' $$),
 179     ($$ i @> '{1}' and i @> '{}' and j @> '{}' $$),
 180     ($$ j @> '{10}' $$),
 181     ($$ j @> '{10}' and i @> '{}' $$),
 182     ($$ j @> '{10}' and j @> '{}' and i @> '{}' $$),
 183     ($$ i @> '{1}' and j @> '{10}' $$)
 184   ) q(query),
 185   lateral explain_query_json($$select * from t_gin_test_tbl where $$ || query) js,
 186   lateral execute_text_query_index($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_index,
 187   lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_heap;
 188                    query                   | return by index | removed by recheck | match
 189 -------------------------------------------+-----------------+--------------------+-------
 190   i @> '{}'                                | 7               | 0                  | t
 191   j @> '{}'                                | 6               | 0                  | t
 192   i @> '{}' and j @> '{}'                  | 4               | 0                  | t
 193   i @> '{1}'                               | 5               | 0                  | t
 194   i @> '{1}' and j @> '{}'                 | 3               | 0                  | t
 195   i @> '{1}' and i @> '{}' and j @> '{}'   | 3               | 0                  | t
 196   j @> '{10}'                              | 4               | 0                  | t
 197   j @> '{10}' and i @> '{}'                | 3               | 0                  | t
 198   j @> '{10}' and j @> '{}' and i @> '{}'  | 3               | 0                  | t
 199   i @> '{1}' and j @> '{10}'               | 2               | 0                  | t
 200 (10 rows)
 201
 202 reset enable_seqscan;
 203 reset enable_bitmapscan;
 204 -- re-purpose t_gin_test_tbl to test scans involving posting trees
 205 insert into t_gin_test_tbl select array[1, g, g/10], array[2, g, g/10]
 206   from generate_series(1, 20000) g;
 207 select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') is not null;
 208  ?column?
 209 ----------
 210  t
 211 (1 row)
 212
 213 analyze t_gin_test_tbl;
 214 set enable_seqscan = off;
 215 set enable_bitmapscan = on;
 216 explain (costs off)
 217 select count(*) from t_gin_test_tbl where j @> array[50];
 218                        QUERY PLAN
 219 ---------------------------------------------------------
 220  Aggregate
 221    ->  Bitmap Heap Scan on t_gin_test_tbl
 222          Recheck Cond: (j @> '{50}'::integer[])
 223          ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
 224                Index Cond: (j @> '{50}'::integer[])
 225 (5 rows)
 226
 227 select count(*) from t_gin_test_tbl where j @> array[50];
 228  count
 229 -------
 230     11
 231 (1 row)
 232
 233 explain (costs off)
 234 select count(*) from t_gin_test_tbl where j @> array[2];
 235                        QUERY PLAN
 236 ---------------------------------------------------------
 237  Aggregate
 238    ->  Bitmap Heap Scan on t_gin_test_tbl
 239          Recheck Cond: (j @> '{2}'::integer[])
 240          ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
 241                Index Cond: (j @> '{2}'::integer[])
 242 (5 rows)
 243
 244 select count(*) from t_gin_test_tbl where j @> array[2];
 245  count
 246 -------
 247  20000
 248 (1 row)
 249
 250 explain (costs off)
 251 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
 252                        QUERY PLAN
 253 ---------------------------------------------------------
 254  Aggregate
 255    ->  Bitmap Heap Scan on t_gin_test_tbl
 256          Recheck Cond: (j @> '{}'::integer[])
 257          ->  Bitmap Index Scan on t_gin_test_tbl_i_j_idx
 258                Index Cond: (j @> '{}'::integer[])
 259 (5 rows)
 260
 261 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
 262  count
 263 -------
 264  20006
 265 (1 row)
 266
 267 -- test vacuuming of posting trees
 268 delete from t_gin_test_tbl where j @> array[2];
 269 vacuum t_gin_test_tbl;
 270 select count(*) from t_gin_test_tbl where j @> array[50];
 271  count
 272 -------
 273      0
 274 (1 row)
 275
 276 select count(*) from t_gin_test_tbl where j @> array[2];
 277  count
 278 -------
 279      0
 280 (1 row)
 281
 282 select count(*) from t_gin_test_tbl where j @> '{}'::int[];
 283  count
 284 -------
 285      6
 286 (1 row)
 287
 288 reset enable_seqscan;
 289 reset enable_bitmapscan;
 290 drop table t_gin_test_tbl;
 291 -- test an unlogged table, mostly to get coverage of ginbuildempty
 292 create unlogged table t_gin_test_tbl(i int4[], j int4[]);
 293 create index on t_gin_test_tbl using gin (i, j);
 294 insert into t_gin_test_tbl
 295 values
 296   (null,    null),
 297   ('{}',    null),
 298   ('{1}',   '{2,3}');
 299 drop table t_gin_test_tbl;