1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
--
-- 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;
|
