Groonga - fulltext search engine.

[Groonga-commit] pgroonga/pgroonga at 9fa6baf [master] test: simplify

Kouhei Sutou null+****@clear*****
Fri Apr 15 10:43:50 JST 2016

• Previous message: [Groonga-commit] Broken: groonga/groonga#7366 (master - e6afb37)
• Next message: [Groonga-commit] pgroonga/pgroonga.github.io at c9100be [master] Use "ed" style
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Kouhei Sutou	2016-04-15 10:43:50 +0900 (Fri, 15 Apr 2016)

  New Revision: 9fa6baf1218631ba90816518c9088da4548f3674
  https://github.com/pgroonga/pgroonga/commit/9fa6baf1218631ba90816518c9088da4548f3674

  Message:
    test: simplify

  Modified files:
    expected/term-search/text/combined/bitmapscan.out
    expected/term-search/text/combined/indexscan.out
    expected/term-search/text/combined/seqscan.out
    sql/term-search/text/combined/bitmapscan.sql
    sql/term-search/text/combined/indexscan.sql
    sql/term-search/text/combined/seqscan.sql

  Modified: expected/term-search/text/combined/bitmapscan.out (+13 -24)
===================================================================
--- expected/term-search/text/combined/bitmapscan.out    2016-04-11 00:12:55 +0900 (606238c)
+++ expected/term-search/text/combined/bitmapscan.out    2016-04-15 10:43:50 +0900 (c543fb6)
@@ -1,34 +1,25 @@
 CREATE TABLE tags (
   name text PRIMARY KEY
 );
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 INSERT INTO tags VALUES ('PostgreSQL');
 INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 CREATE INDEX pgrn_tags_index ON tags
   USING pgroonga (name pgroonga.text_term_search_ops_v2);
-CREATE INDEX pgrn_readings_index ON readings
-  USING pgroonga (katakana pgroonga.text_term_search_ops_v2);
+CREATE INDEX pgrn_tag_readings_index ON tag_readings
+  USING pgroonga (tag_name, katakana pgroonga.text_term_search_ops_v2);
 SET enable_seqscan = off;
 SET enable_indexscan = off;
 SET enable_bitmapscan = on;
@@ -36,9 +27,8 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
     name    | score 
 ------------+-------
@@ -46,6 +36,5 @@ SELECT tag_name, pgroonga.score(readings)
  Groonga    |     1
 (2 rows)
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;

  Modified: expected/term-search/text/combined/indexscan.out (+13 -24)
===================================================================
--- expected/term-search/text/combined/indexscan.out    2016-04-11 00:12:55 +0900 (d7ddb6b)
+++ expected/term-search/text/combined/indexscan.out    2016-04-15 10:43:50 +0900 (4661b2f)
@@ -1,34 +1,25 @@
 CREATE TABLE tags (
   name text PRIMARY KEY
 );
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 INSERT INTO tags VALUES ('PostgreSQL');
 INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 CREATE INDEX pgrn_tags_index ON tags
   USING pgroonga (name pgroonga.text_term_search_ops_v2);
-CREATE INDEX pgrn_readings_index ON readings
-  USING pgroonga (katakana pgroonga.text_term_search_ops_v2);
+CREATE INDEX pgrn_tag_readings_index ON tag_readings
+  USING pgroonga (tag_name, katakana pgroonga.text_term_search_ops_v2);
 SET enable_seqscan = off;
 SET enable_indexscan = on;
 SET enable_bitmapscan = off;
@@ -36,9 +27,8 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
     name    | score 
 ------------+-------
@@ -46,6 +36,5 @@ SELECT tag_name, pgroonga.score(readings)
  Groonga    |     1
 (2 rows)
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;

  Modified: expected/term-search/text/combined/seqscan.out (+11 -22)
===================================================================
--- expected/term-search/text/combined/seqscan.out    2016-04-11 00:12:55 +0900 (7271da2)
+++ expected/term-search/text/combined/seqscan.out    2016-04-15 10:43:50 +0900 (8893a96)
@@ -1,30 +1,21 @@
 CREATE TABLE tags (
   name text PRIMARY KEY
 );
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 INSERT INTO tags VALUES ('PostgreSQL');
 INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 SET enable_seqscan = on;
 SET enable_indexscan = off;
 SET enable_bitmapscan = off;
@@ -32,9 +23,8 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
     name    | score 
 ------------+-------
@@ -42,6 +32,5 @@ SELECT tag_name, pgroonga.score(readings)
  PostgreSQL |     0
 (2 rows)
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;

  Modified: sql/term-search/text/combined/bitmapscan.sql (+13 -26)
===================================================================
--- sql/term-search/text/combined/bitmapscan.sql    2016-04-11 00:12:55 +0900 (3e1f0b8)
+++ sql/term-search/text/combined/bitmapscan.sql    2016-04-15 10:43:50 +0900 (9293214)
@@ -2,16 +2,11 @@ CREATE TABLE tags (
   name text PRIMARY KEY
 );
 
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 
 INSERT INTO tags VALUES ('PostgreSQL');
@@ -19,22 +14,16 @@ INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
 
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 
 CREATE INDEX pgrn_tags_index ON tags
   USING pgroonga (name pgroonga.text_term_search_ops_v2);
-CREATE INDEX pgrn_readings_index ON readings
-  USING pgroonga (katakana pgroonga.text_term_search_ops_v2);
+CREATE INDEX pgrn_tag_readings_index ON tag_readings
+  USING pgroonga (tag_name, katakana pgroonga.text_term_search_ops_v2);
 
 SET enable_seqscan = off;
 SET enable_indexscan = off;
@@ -44,11 +33,9 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;

  Modified: sql/term-search/text/combined/indexscan.sql (+13 -26)
===================================================================
--- sql/term-search/text/combined/indexscan.sql    2016-04-11 00:12:55 +0900 (610e61a)
+++ sql/term-search/text/combined/indexscan.sql    2016-04-15 10:43:50 +0900 (e11d092)
@@ -2,16 +2,11 @@ CREATE TABLE tags (
   name text PRIMARY KEY
 );
 
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 
 INSERT INTO tags VALUES ('PostgreSQL');
@@ -19,22 +14,16 @@ INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
 
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 
 CREATE INDEX pgrn_tags_index ON tags
   USING pgroonga (name pgroonga.text_term_search_ops_v2);
-CREATE INDEX pgrn_readings_index ON readings
-  USING pgroonga (katakana pgroonga.text_term_search_ops_v2);
+CREATE INDEX pgrn_tag_readings_index ON tag_readings
+  USING pgroonga (tag_name, katakana pgroonga.text_term_search_ops_v2);
 
 SET enable_seqscan = off;
 SET enable_indexscan = on;
@@ -44,11 +33,9 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;

  Modified: sql/term-search/text/combined/seqscan.sql (+11 -24)
===================================================================
--- sql/term-search/text/combined/seqscan.sql    2016-04-11 00:12:55 +0900 (7eff6c3)
+++ sql/term-search/text/combined/seqscan.sql    2016-04-15 10:43:50 +0900 (4a53a57)
@@ -2,16 +2,11 @@ CREATE TABLE tags (
   name text PRIMARY KEY
 );
 
-CREATE TABLE readings (
-  katakana text PRIMARY KEY
-);
-
-CREATE TABLE tags_readings (
+CREATE TABLE tag_readings (
   tag_name text
     REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
-  reading_katakana text
-    REFERENCES readings ON DELETE CASCADE ON UPDATE CASCADE,
-  PRIMARY KEY (tag_name, reading_katakana)
+  katakana text,
+  PRIMARY KEY (tag_name, katakana)
 );
 
 INSERT INTO tags VALUES ('PostgreSQL');
@@ -19,17 +14,11 @@ INSERT INTO tags VALUES ('Groonga');
 INSERT INTO tags VALUES ('PGroonga');
 INSERT INTO tags VALUES ('pglogical');
 
-INSERT INTO readings VALUES ('ポストグレスキューエル');
-INSERT INTO readings VALUES ('ポスグレ');
-INSERT INTO readings VALUES ('グルンガ');
-INSERT INTO readings VALUES ('ピージールンガ');
-INSERT INTO readings VALUES ('ピージーロジカル');
-
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
-INSERT INTO tags_readings VALUES ('PostgreSQL', 'ポスグレ');
-INSERT INTO tags_readings VALUES ('Groonga', 'グルンガ');
-INSERT INTO tags_readings VALUES ('PGroonga', 'ピージールンガ');
-INSERT INTO tags_readings VALUES ('pglogical', 'ピージーロジカル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポストグレスキューエル');
+INSERT INTO tag_readings VALUES ('PostgreSQL', 'ポスグレ');
+INSERT INTO tag_readings VALUES ('Groonga', 'グルンガ');
+INSERT INTO tag_readings VALUES ('PGroonga', 'ピージールンガ');
+INSERT INTO tag_readings VALUES ('pglogical', 'ピージーロジカル');
 
 SET enable_seqscan = on;
 SET enable_indexscan = off;
@@ -39,11 +28,9 @@ SELECT name, pgroonga.score(tags)
   FROM tags
   WHERE name &^ 'Groon'
 UNION
-SELECT tag_name, pgroonga.score(readings)
-  FROM readings INNER JOIN tags_readings
-                  ON readings.katakana = tags_readings.reading_katakana
+SELECT tag_name, pgroonga.score(tag_readings)
+  FROM tag_readings
   WHERE katakana &^~ 'posu';
 
-DROP TABLE tags_readings;
-DROP TABLE readings;
+DROP TABLE tag_readings;
 DROP TABLE tags;
-------------- next part --------------
HTML����������������������������...
다운로드 

• Previous message: [Groonga-commit] Broken: groonga/groonga#7366 (master - e6afb37)
• Next message: [Groonga-commit] pgroonga/pgroonga.github.io at c9100be [master] Use "ed" style
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]