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. 7create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off); 8create index gin_test_idx on gin_test_tbl using gin (i) 9 with (fastupdate = on, gin_pending_list_limit = 4096); 10insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g; 11insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g; 12select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers 13 many 14------ 15 t 16(1 row) 17 18insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g; 19vacuum gin_test_tbl; -- flush the fastupdate buffers 20select 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 27delete from gin_test_tbl where i @> array[2]; 28vacuum 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. 33alter index gin_test_idx set (fastupdate = off); 34insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g; 35insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g; 36delete from gin_test_tbl where i @> array[2]; 37vacuum gin_test_tbl; 38-- Test for "rare && frequent" searches 39explain (costs off) 40select 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 50select 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 57set gin_fuzzy_search_limit = 1000; 58explain (costs off) 59select 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 69select count(*) > 0 as ok from gin_test_tbl where i @> array[1]; 70 ok 71---- 72 t 73(1 row) 74 75reset gin_fuzzy_search_limit; 76