diff options
Diffstat (limited to 'src/test/regress/expected/gin.out')
| -rw-r--r-- | src/test/regress/expected/gin.out | 299 |
1 files changed, 299 insertions, 0 deletions
diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out new file mode 100644 index 0000000..0af4643 --- /dev/null +++ b/src/test/regress/expected/gin.out @@ -0,0 +1,299 @@ +-- +-- Test GIN indexes. +-- +-- There are other tests to test different GIN opclasses. This is for testing +-- GIN itself. +-- Create and populate a test table with a GIN index. +create table gin_test_tbl(i int4[]) with (autovacuum_enabled = off); +create index gin_test_idx on gin_test_tbl using gin (i) + with (fastupdate = on, gin_pending_list_limit = 4096); +insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g; +insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g; +select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers + many +------ + t +(1 row) + +insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g; +vacuum gin_test_tbl; -- flush the fastupdate buffers +select gin_clean_pending_list('gin_test_idx'); -- nothing to flush + gin_clean_pending_list +------------------------ + 0 +(1 row) + +-- Test vacuuming +delete from gin_test_tbl where i @> array[2]; +vacuum gin_test_tbl; +-- Disable fastupdate, and do more insertions. With fastupdate enabled, most +-- insertions (by flushing the list pages) cause page splits. Without +-- fastupdate, we get more churn in the GIN data leaf pages, and exercise the +-- recompression codepaths. +alter index gin_test_idx set (fastupdate = off); +insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g; +insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g; +delete from gin_test_tbl where i @> array[2]; +vacuum gin_test_tbl; +-- Test for "rare && frequent" searches +explain (costs off) +select count(*) from gin_test_tbl where i @> array[1, 999]; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on gin_test_tbl + Recheck Cond: (i @> '{1,999}'::integer[]) + -> Bitmap Index Scan on gin_test_idx + Index Cond: (i @> '{1,999}'::integer[]) +(5 rows) + +select count(*) from gin_test_tbl where i @> array[1, 999]; + count +------- + 3 +(1 row) + +-- Very weak test for gin_fuzzy_search_limit +set gin_fuzzy_search_limit = 1000; +explain (costs off) +select count(*) > 0 as ok from gin_test_tbl where i @> array[1]; + QUERY PLAN +--------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on gin_test_tbl + Recheck Cond: (i @> '{1}'::integer[]) + -> Bitmap Index Scan on gin_test_idx + Index Cond: (i @> '{1}'::integer[]) +(5 rows) + +select count(*) > 0 as ok from gin_test_tbl where i @> array[1]; + ok +---- + t +(1 row) + +reset gin_fuzzy_search_limit; +-- Test optimization of empty queries +create temp table t_gin_test_tbl(i int4[], j int4[]); +create index on t_gin_test_tbl using gin (i, j); +insert into t_gin_test_tbl +values + (null, null), + ('{}', null), + ('{1}', null), + ('{1,2}', null), + (null, '{}'), + (null, '{10}'), + ('{1,2}', '{10}'), + ('{2}', '{10}'), + ('{1,3}', '{}'), + ('{1,1}', '{10}'); +set enable_seqscan = off; +explain (costs off) +select * from t_gin_test_tbl where array[0] <@ i; + QUERY PLAN +--------------------------------------------------- + Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: ('{0}'::integer[] <@ i) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: (i @> '{0}'::integer[]) +(4 rows) + +select * from t_gin_test_tbl where array[0] <@ i; + i | j +---+--- +(0 rows) + +select * from t_gin_test_tbl where array[0] <@ i and '{}'::int4[] <@ j; + i | j +---+--- +(0 rows) + +explain (costs off) +select * from t_gin_test_tbl where i @> '{}'; + QUERY PLAN +--------------------------------------------------- + Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: (i @> '{}'::integer[]) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: (i @> '{}'::integer[]) +(4 rows) + +select * from t_gin_test_tbl where i @> '{}'; + i | j +-------+------ + {} | + {1} | + {1,2} | + {1,2} | {10} + {2} | {10} + {1,3} | {} + {1,1} | {10} +(7 rows) + +create function explain_query_json(query_sql text) +returns table (explain_line json) +language plpgsql as +$$ +begin + set enable_seqscan = off; + set enable_bitmapscan = on; + return query execute 'EXPLAIN (ANALYZE, FORMAT json) ' || query_sql; +end; +$$; +create function execute_text_query_index(query_sql text) +returns setof text +language plpgsql +as +$$ +begin + set enable_seqscan = off; + set enable_bitmapscan = on; + return query execute query_sql; +end; +$$; +create function execute_text_query_heap(query_sql text) +returns setof text +language plpgsql +as +$$ +begin + set enable_seqscan = on; + set enable_bitmapscan = off; + return query execute query_sql; +end; +$$; +-- check number of rows returned by index and removed by recheck +select + query, + js->0->'Plan'->'Plans'->0->'Actual Rows' as "return by index", + js->0->'Plan'->'Rows Removed by Index Recheck' as "removed by recheck", + (res_index = res_heap) as "match" +from + (values + ($$ i @> '{}' $$), + ($$ j @> '{}' $$), + ($$ i @> '{}' and j @> '{}' $$), + ($$ i @> '{1}' $$), + ($$ i @> '{1}' and j @> '{}' $$), + ($$ i @> '{1}' and i @> '{}' and j @> '{}' $$), + ($$ j @> '{10}' $$), + ($$ j @> '{10}' and i @> '{}' $$), + ($$ j @> '{10}' and j @> '{}' and i @> '{}' $$), + ($$ i @> '{1}' and j @> '{10}' $$) + ) q(query), + lateral explain_query_json($$select * from t_gin_test_tbl where $$ || query) js, + lateral execute_text_query_index($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_index, + lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from t_gin_test_tbl where $$ || query) res_heap; + query | return by index | removed by recheck | match +-------------------------------------------+-----------------+--------------------+------- + i @> '{}' | 7 | 0 | t + j @> '{}' | 6 | 0 | t + i @> '{}' and j @> '{}' | 4 | 0 | t + i @> '{1}' | 5 | 0 | t + i @> '{1}' and j @> '{}' | 3 | 0 | t + i @> '{1}' and i @> '{}' and j @> '{}' | 3 | 0 | t + j @> '{10}' | 4 | 0 | t + j @> '{10}' and i @> '{}' | 3 | 0 | t + j @> '{10}' and j @> '{}' and i @> '{}' | 3 | 0 | t + i @> '{1}' and j @> '{10}' | 2 | 0 | t +(10 rows) + +reset enable_seqscan; +reset enable_bitmapscan; +-- re-purpose t_gin_test_tbl to test scans involving posting trees +insert into t_gin_test_tbl select array[1, g, g/10], array[2, g, g/10] + from generate_series(1, 20000) g; +select gin_clean_pending_list('t_gin_test_tbl_i_j_idx') is not null; + ?column? +---------- + t +(1 row) + +analyze t_gin_test_tbl; +set enable_seqscan = off; +set enable_bitmapscan = on; +explain (costs off) +select count(*) from t_gin_test_tbl where j @> array[50]; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: (j @> '{50}'::integer[]) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: (j @> '{50}'::integer[]) +(5 rows) + +select count(*) from t_gin_test_tbl where j @> array[50]; + count +------- + 11 +(1 row) + +explain (costs off) +select count(*) from t_gin_test_tbl where j @> array[2]; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: (j @> '{2}'::integer[]) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: (j @> '{2}'::integer[]) +(5 rows) + +select count(*) from t_gin_test_tbl where j @> array[2]; + count +------- + 20000 +(1 row) + +explain (costs off) +select count(*) from t_gin_test_tbl where j @> '{}'::int[]; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on t_gin_test_tbl + Recheck Cond: (j @> '{}'::integer[]) + -> Bitmap Index Scan on t_gin_test_tbl_i_j_idx + Index Cond: (j @> '{}'::integer[]) +(5 rows) + +select count(*) from t_gin_test_tbl where j @> '{}'::int[]; + count +------- + 20006 +(1 row) + +-- test vacuuming of posting trees +delete from t_gin_test_tbl where j @> array[2]; +vacuum t_gin_test_tbl; +select count(*) from t_gin_test_tbl where j @> array[50]; + count +------- + 0 +(1 row) + +select count(*) from t_gin_test_tbl where j @> array[2]; + count +------- + 0 +(1 row) + +select count(*) from t_gin_test_tbl where j @> '{}'::int[]; + count +------- + 6 +(1 row) + +reset enable_seqscan; +reset enable_bitmapscan; +drop table t_gin_test_tbl; +-- test an unlogged table, mostly to get coverage of ginbuildempty +create unlogged table t_gin_test_tbl(i int4[], j int4[]); +create index on t_gin_test_tbl using gin (i, j); +insert into t_gin_test_tbl +values + (null, null), + ('{}', null), + ('{1}', '{2,3}'); +drop table t_gin_test_tbl; |