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