1--
2-- Test GIN indexes.
3--
4-- There are other tests to test different GIN opclasses. This is for testing
5-- GIN itself.
6
7-- Create and populate a test table with a GIN index.
8create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off);
9create index gin_test_idx on gin_test_tbl using gin (i)
10  with (fastupdate = on, gin_pending_list_limit = 4096);
11insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
12insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
13
14select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
15
16insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
17
18vacuum gin_test_tbl; -- flush the fastupdate buffers
19
20select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
21
22-- Test vacuuming
23delete from gin_test_tbl where i @> array[2];
24vacuum gin_test_tbl;
25
26-- Disable fastupdate, and do more insertions. With fastupdate enabled, most
27-- insertions (by flushing the list pages) cause page splits. Without
28-- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
29-- recompression codepaths.
30alter index gin_test_idx set (fastupdate = off);
31
32insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g;
33insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
34
35delete from gin_test_tbl where i @> array[2];
36vacuum gin_test_tbl;
37
38-- Test for "rare && frequent" searches
39explain (costs off)
40select count(*) from gin_test_tbl where i @> array[1, 999];
41
42select count(*) from gin_test_tbl where i @> array[1, 999];
43
44-- Very weak test for gin_fuzzy_search_limit
45set gin_fuzzy_search_limit = 1000;
46
47explain (costs off)
48select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
49
50select count(*) > 0 as ok from gin_test_tbl where i @> array[1];
51
52reset gin_fuzzy_search_limit;
53
54-- Test optimization of empty queries
55create temp table t_gin_test_tbl(i int4[], j int4[]);
56create index on t_gin_test_tbl using gin (i, j);
57insert into t_gin_test_tbl
58values
59  (null,    null),
60  ('{}',    null),
61  ('{1}',   null),
62  ('{1,2}', null),
63  (null,    '{}'),
64  (null,    '{10}'),
65  ('{1,2}', '{10}'),
66  ('{2}',   '{10}'),
67  ('{1,3}', '{}'),
68  ('{1,1}', '{10}');
69
70set enable_seqscan = off;
71explain (costs off)
72select * from t_gin_test_tbl where array[0] <@ i;
73select * from t_gin_test_tbl where array[0] <@ i;
74select * from t_gin_test_tbl where array[0] <@ i and '{}'::int4[] <@ j;
75
76explain (costs off)
77select * from t_gin_test_tbl where i @> '{}';
78select * from t_gin_test_tbl where i @> '{}';
79
80create function explain_query_json(query_sql text)
81returns table (explain_line json)
82language plpgsql as
83$$
84begin
85  set enable_seqscan = off;
86  set enable_bitmapscan = on;
87  return query execute 'EXPLAIN (ANALYZE, FORMAT json) ' || query_sql;
88end;
89$$;
90
91create function execute_text_query_index(query_sql text)
92returns setof text
93language plpgsql
94as
95$$
96begin
97  set enable_seqscan = off;
98  set enable_bitmapscan = on;
99  return query execute query_sql;
100end;
101$$;
102
103create function execute_text_query_heap(query_sql text)
104returns setof text
105language plpgsql
106as
107$$
108begin
109  set enable_seqscan = on;
110  set enable_bitmapscan = off;
111  return query execute query_sql;
112end;
113$$;
114
115-- check number of rows returned by index and removed by recheck
116select
117  query,
118  js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index",
119  js->0->'Plan'->'Rows Removed by Index Recheck' as "removed by recheck",
120  (res_index = res_heap) as "match"
121from
122  (values
123    ($$ i @> '{}' $$),
124    ($$ j @> '{}' $$),
125    ($$ i @> '{}' and j @> '{}' $$),
126    ($$ i @> '{1}' $$),
127    ($$ i @> '{1}' and j @> '{}' $$),
128    ($$ i @> '{1}' and i @> '{}' and j @> '{}' $$),
129    ($$ j @> '{10}' $$),
130    ($$ j @> '{10}' and i @> '{}' $$),
131    ($$ j @> '{10}' and j @> '{}' and i @> '{}' $$),
132    ($$ i @> '{1}' and j @> '{10}' $$)
133  ) q(query),
134  lateral explain_query_json($$select * from t_gin_test_tbl where $$ || query) js,
135  lateral execute_text_query_index($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_index,
136  lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_heap;
137
138reset enable_seqscan;
139reset enable_bitmapscan;
140
141-- re-purpose t_gin_test_tbl to test scans involving posting trees
142insert into t_gin_test_tbl select array[1, g, g/10], array[2, g, g/10]
143  from generate_series(1, 20000) g;
144
145select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') is not null;
146
147analyze t_gin_test_tbl;
148
149set enable_seqscan = off;
150set enable_bitmapscan = on;
151
152explain (costs off)
153select count(*) from t_gin_test_tbl where j @> array[50];
154select count(*) from t_gin_test_tbl where j @> array[50];
155explain (costs off)
156select count(*) from t_gin_test_tbl where j @> array[2];
157select count(*) from t_gin_test_tbl where j @> array[2];
158explain (costs off)
159select count(*) from t_gin_test_tbl where j @> '{}'::int[];
160select count(*) from t_gin_test_tbl where j @> '{}'::int[];
161
162reset enable_seqscan;
163reset enable_bitmapscan;
164
165drop table t_gin_test_tbl;
166