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