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