Add the regression test of text similarity search.

diff --git a/expected/pg_bigm.out b/expected/pg_bigm.out
index af4121b..84c4ab5 100644 (file)
--- a/expected/pg_bigm.out
+++ b/expected/pg_bigm.out
@@ -5,6 +5,7 @@ SET escape_string_warning = off;
 SET enable_seqscan = off;
 SET pg_bigm.enable_recheck = on;
 SET pg_bigm.gin_key_limit = 0;
+SET pg_bigm.similarity_limit = 0.02;
 -- tests for pg_bigm.last_update
 SHOW pg_bigm.last_update;
  pg_bigm.last_update 
@@ -518,6 +519,163 @@ SELECT bigm_similarity('---', '####---');
              0.4
 (1 row)
 
+-- tests for text similarity serach
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'a';
+                QUERY PLAN                
+------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% 'a'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% 'a'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'am';
+                QUERY PLAN                
+------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% 'am'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% 'am'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'XML';
+                QUERY PLAN                 
+-------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% 'XML'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% 'XML'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'bigm';
+                 QUERY PLAN                 
+--------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% 'bigm'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% 'bigm'::text)
+(4 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% NULL;
+ col1 
+------
+(0 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '';
+ col1 
+------
+(0 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '%';
+                          col1                          
+--------------------------------------------------------
+ Sets the similarity threshold used by the =% operator.
+(1 row)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '\\';
+                   col1                    
+-------------------------------------------
+ \dx displays list of installed extensions
+(1 row)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '_';
+ col1 
+------
+(0 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '\\dx';
+                             col1                             
+--------------------------------------------------------------
+ Shows the maximum number of index keys.
+ Recheck that heap tuples fetched from index match the query.
+ \dx displays list of installed extensions
+(3 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '200%';
+                             col1                              
+---------------------------------------------------------------
+ Sets the similarity threshold used by the =% operator.
+ pg_bigm has improved the full text search performance by 200%
+(2 rows)
+
+SELECT col1 FROM test_bigm WHERE col1 =% '  ';
+ col1 
+------
+(0 rows)
+
+SELECT count(*), min(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE col1 =% 'Y';
+ count |    min    
+-------+-----------
+     1 | 0.0212766
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE NOT col1 =% 'Y';
+ count |    max    
+-------+-----------
+   248 | 0.0192308
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE col1 =% 'pi';
+ count | min  
+-------+------
+    52 | 0.02
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE NOT col1 =% 'pi';
+ count |    max    
+-------+-----------
+   197 | 0.0196078
+(1 row)
+
+SET pg_bigm.similarity_limit = 0.06;
+SELECT count(*), min(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE col1 =% 'GIN';
+ count |    min    
+-------+-----------
+     1 | 0.0769231
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE NOT col1 =% 'GIN';
+ count |    max    
+-------+-----------
+   248 | 0.0571429
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE col1 =% 'gin';
+ count |    min    
+-------+-----------
+     5 | 0.0606061
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE NOT col1 =% 'gin';
+ count |    max    
+-------+-----------
+   244 | 0.0588235
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE col1 =% 'Tool';
+ count |    min    
+-------+-----------
+     3 | 0.0645161
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE NOT col1 =% 'Tool';
+ count |    max    
+-------+-----------
+   246 | 0.0555556
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE col1 =% 'performance';
+ count | min  
+-------+------
+   153 | 0.06
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE NOT col1 =% 'performance';
+ count |    max    
+-------+-----------
+    96 | 0.0588235
+(1 row)
+
 -- tests for drop of pg_bigm
 DROP EXTENSION pg_bigm CASCADE;
 NOTICE:  drop cascades to index test_bigm_idx

diff --git a/expected/pg_bigm_ja.out b/expected/pg_bigm_ja.out
index 52f9222..59b3dbe 100644 (file)
--- a/expected/pg_bigm_ja.out
+++ b/expected/pg_bigm_ja.out
@@ -5,6 +5,7 @@ SET escape_string_warning = off;
 SET enable_seqscan = off;
 SET pg_bigm.enable_recheck = on;
 SET pg_bigm.gin_key_limit = 0;
+SET pg_bigm.similarity_limit = 0.02;
 -- tests for likequery
 SELECT likequery('ポスグレの全文検索');
       likequery       
@@ -175,3 +176,101 @@ SELECT bigm_similarity('東京と京都', '東京都');
         0.666667
 (1 row)
 
+SET pg_bigm.enable_recheck = on;
+-- tests for text similarity search
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '値';
+                QUERY PLAN                
+------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% '値'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% '値'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '最大';
+                 QUERY PLAN                 
+--------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% '最大'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% '最大'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% 'ツール';
+                  QUERY PLAN                  
+----------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% 'ツール'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% 'ツール'::text)
+(4 rows)
+
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '全文検索';
+                   QUERY PLAN                   
+------------------------------------------------
+ Bitmap Heap Scan on test_bigm
+   Recheck Cond: (col1 =% '全文検索'::text)
+   ->  Bitmap Index Scan on test_bigm_idx
+         Index Cond: (col1 =% '全文検索'::text)
+(4 rows)
+
+SELECT count(*), min(bigm_similarity(col1, '値')) FROM test_bigm WHERE col1 =% '値';
+ count |  min   
+-------+--------
+     0 | (null)
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, '値')) FROM test_bigm WHERE NOT col1 =% '値';
+ count | max 
+-------+-----
+   278 |   0
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, '最大')) FROM test_bigm WHERE col1 =% '最大';
+ count |    min    
+-------+-----------
+     2 | 0.0434783
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, '最大')) FROM test_bigm WHERE NOT col1 =% '最大';
+ count | max 
+-------+-----
+   276 |   0
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'ツール')) FROM test_bigm WHERE col1 =% 'ツール';
+ count |    min    
+-------+-----------
+     2 | 0.0681818
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'ツール')) FROM test_bigm WHERE NOT col1 =% 'ツール';
+ count | max 
+-------+-----
+   276 |   0
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, 'インデックスを作成')) FROM test_bigm WHERE col1 =% 'インデックスを作成';
+ count |  min  
+-------+-------
+     9 | 0.125
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, 'インデックスを作成')) FROM test_bigm WHERE NOT col1 =% 'インデックスを作成';
+ count | max 
+-------+-----
+   269 |   0
+(1 row)
+
+SELECT count(*), min(bigm_similarity(col1, '3-gramの全文検索')) FROM test_bigm WHERE col1 =% '3-gramの全文検索';
+ count | min  
+-------+------
+    75 | 0.02
+(1 row)
+
+SELECT count(*), max(bigm_similarity(col1, '3-gramの全文検索')) FROM test_bigm WHERE NOT col1 =% '3-gramの全文検索';
+ count |    max    
+-------+-----------
+   203 | 0.0196078
+(1 row)
+

diff --git a/sql/pg_bigm.sql b/sql/pg_bigm.sql
index b030100..0fa397a 100644 (file)
--- a/sql/pg_bigm.sql
+++ b/sql/pg_bigm.sql
@@ -7,6 +7,7 @@ SET escape_string_warning = off;
 SET enable_seqscan = off;
 SET pg_bigm.enable_recheck = on;
 SET pg_bigm.gin_key_limit = 0;
+SET pg_bigm.similarity_limit = 0.02;
 
 -- tests for pg_bigm.last_update
 SHOW pg_bigm.last_update;
@@ -131,6 +132,36 @@ SELECT bigm_similarity('wow', ' wow ');
 
 SELECT bigm_similarity('---', '####---');
 
+-- tests for text similarity serach
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'a';
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'am';
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'XML';
+EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'bigm';
+
+SELECT col1 FROM test_bigm WHERE col1 =% NULL;
+SELECT col1 FROM test_bigm WHERE col1 =% '';
+
+SELECT col1 FROM test_bigm WHERE col1 =% '%';
+SELECT col1 FROM test_bigm WHERE col1 =% '\\';
+SELECT col1 FROM test_bigm WHERE col1 =% '_';
+SELECT col1 FROM test_bigm WHERE col1 =% '\\dx';
+SELECT col1 FROM test_bigm WHERE col1 =% '200%';
+SELECT col1 FROM test_bigm WHERE col1 =% '  ';
+
+SELECT count(*), min(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE col1 =% 'Y';
+SELECT count(*), max(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE NOT col1 =% 'Y';
+SELECT count(*), min(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE col1 =% 'pi';
+SELECT count(*), max(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE NOT col1 =% 'pi';
+SET pg_bigm.similarity_limit = 0.06;
+SELECT count(*), min(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE col1 =% 'GIN';
+SELECT count(*), max(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE NOT col1 =% 'GIN';
+SELECT count(*), min(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE col1 =% 'gin';
+SELECT count(*), max(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE NOT col1 =% 'gin';
+SELECT count(*), min(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE col1 =% 'Tool';
+SELECT count(*), max(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE NOT col1 =% 'Tool';
+SELECT count(*), min(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE col1 =% 'performance';
+SELECT count(*), max(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE NOT col1 =% 'performance';
+
 -- tests for drop of pg_bigm
 DROP EXTENSION pg_bigm CASCADE;
 SELECT likequery('test');

diff --git a/sql/pg_bigm_ja.sql b/sql/pg_bigm_ja.sql
index aebf020..097c92a 100644 (file)
--- a/sql/pg_bigm_ja.sql
+++ b/sql/pg_bigm_ja.sql
@@ -7,6 +7,7 @@ SET escape_string_warning = off;
 SET enable_seqscan = off;
 SET pg_bigm.enable_recheck = on;
 SET pg_bigm.gin_key_limit = 0;
+SET pg_bigm.similarity_limit = 0.02;
 
 -- tests for likequery
 SELECT likequery('ポスグレの全文検索');
@@ -51,3 +52,21 @@ SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('東京都');
 SELECT bigm_similarity('東京都', ' 東京都 ');
 SELECT bigm_similarity('東京都', '東京と京都');
 SELECT bigm_similarity('東京と京都', '東京都');
+SET pg_bigm.enable_recheck = on;
+
+-- tests for text similarity search
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '値';
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '最大';
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% 'ツール';
+EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 =% '全文検索';
+
+SELECT count(*), min(bigm_similarity(col1, '値')) FROM test_bigm WHERE col1 =% '値';
+SELECT count(*), max(bigm_similarity(col1, '値')) FROM test_bigm WHERE NOT col1 =% '値';
+SELECT count(*), min(bigm_similarity(col1, '最大')) FROM test_bigm WHERE col1 =% '最大';
+SELECT count(*), max(bigm_similarity(col1, '最大')) FROM test_bigm WHERE NOT col1 =% '最大';
+SELECT count(*), min(bigm_similarity(col1, 'ツール')) FROM test_bigm WHERE col1 =% 'ツール';
+SELECT count(*), max(bigm_similarity(col1, 'ツール')) FROM test_bigm WHERE NOT col1 =% 'ツール';
+SELECT count(*), min(bigm_similarity(col1, 'インデックスを作成')) FROM test_bigm WHERE col1 =% 'インデックスを作成';
+SELECT count(*), max(bigm_similarity(col1, 'インデックスを作成')) FROM test_bigm WHERE NOT col1 =% 'インデックスを作成';
+SELECT count(*), min(bigm_similarity(col1, '3-gramの全文検索')) FROM test_bigm WHERE col1 =% '3-gramの全文検索';
+SELECT count(*), max(bigm_similarity(col1, '3-gramの全文検索')) FROM test_bigm WHERE NOT col1 =% '3-gramの全文検索';