CREATE DATABASE db_gin_utf8_col TEMPLATE template0 encoding 'UTF8';
\c db_gin_utf8_col
SET ENABLE_SEQSCAN=OFF;
SET ENABLE_INDEXSCAN=OFF;
SET ENABLE_BITMAPSCAN=ON;
--- import data
create table import_data_col(id int, strType varchar(10), numType varchar(20), textType text);
copy import_data_col from '@abs_srcdir@/data/gin_data_1000.txt';
--- import data then create index
create table gin_test_data_col(id int, strType varchar(10), numType varchar(20), textType text) with(orientation=column);
insert into gin_test_data_col select 1, strType, numType, textType from import_data_col;
--- 6 times
insert into gin_test_data_col select * from gin_test_data_col;
insert into gin_test_data_col select * from gin_test_data_col;
insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--- create index
create index strType_idx_col on gin_test_data_col using gin(to_tsvector('ngram', strType)) with (fastupdate=on);
create index numType_idx_col on gin_test_data_col using gin(to_tsvector('ngram', numType)) with (fastupdate=on);
create index textType_idx_col on gin_test_data_col using gin(to_tsvector('ngram', textType)) with (fastupdate=on);
--- select
select count(*) from gin_test_data_col where to_tsvector('ngram', strType) @@ to_tsquery('ngram', 'BRM');
count
-------
16
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', strType) @@ to_tsquery('ngram', 'tz');
count
-------
72
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', textType) @@ to_tsquery('ngram', '曹瞒');
count
-------
40
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '58');
count
-------
704
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '!58');
count
-------
7296
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '5:*');
count
-------
4488
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '711658601');
count
-------
8
(1 row)
--- gin data page split
create table gin_test_data_col_1(id int, numType varchar(20)) with(orientation=column);
insert into gin_test_data_col_1 select id, numType from gin_test_data_col;
--- 7 times
insert into gin_test_data_col_1 select * from gin_test_data_col_1;
insert into gin_test_data_col_1 select * from gin_test_data_col_1;
insert into gin_test_data_col_1 select * from gin_test_data_col_1;
--insert into gin_test_data_col_1 select * from gin_test_data_col_1;
--insert into gin_test_data_col_1 select * from gin_test_data_col_1;
--insert into gin_test_data_col_1 select * from gin_test_data_col_1;
--insert into gin_test_data_col_1 select * from gin_test_data_col_1;
vacuum freeze gin_test_data_col_1;
create index numType_idx_col_1 on gin_test_data_col_1 using gin(to_tsvector('ngram', numType)) with (fastupdate=on);
delete from gin_test_data_col_1;
vacuum gin_test_data_col_1;
--- import data with index
truncate gin_test_data_col;
vacuum full gin_test_data_col;
insert into gin_test_data_col select 1, strType, numType, textType from import_data_col;
--- 6 times
insert into gin_test_data_col select * from gin_test_data_col;
insert into gin_test_data_col select * from gin_test_data_col;
insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--insert into gin_test_data_col select * from gin_test_data_col;
--- select
select count(*) from gin_test_data_col where to_tsvector('ngram', strType) @@ to_tsquery('ngram', 'BRM');
count
-------
16
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', strType) @@ to_tsquery('ngram', 'tz');
count
-------
72
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', textType) @@ to_tsquery('ngram', '曹瞒');
count
-------
40
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '58');
count
-------
704
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '!58');
count
-------
7296
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '5:*');
count
-------
4488
(1 row)
select count(*) from gin_test_data_col where to_tsvector('ngram', numType) @@ to_tsquery('ngram', '711658601');
count
-------
8
(1 row)
--- vacuum
vacuum gin_test_data_col;
delete from gin_test_data_col;
vacuum gin_test_data_col;
truncate gin_test_data_col;
vacuum full gin_test_data_col;