Kouhei Sutou
null+****@clear*****
Fri Apr 8 23:55:25 JST 2016
Kouhei Sutou 2016-04-08 23:55:25 +0900 (Fri, 08 Apr 2016) New Revision: 7e998a4a4b605be5a2b2de756bb0ad1705609969 https://github.com/pgroonga/pgroonga/commit/7e998a4a4b605be5a2b2de756bb0ad1705609969 Message: Add a test for combining prefix search and prefix RK search Added 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 Added: expected/term-search/text/combined/bitmapscan.out (+51 -0) 100644 =================================================================== --- /dev/null +++ expected/term-search/text/combined/bitmapscan.out 2016-04-08 23:55:25 +0900 (4ecf392) @@ -0,0 +1,51 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); +CREATE TABLE readings ( + katakana text PRIMARY KEY +); +CREATE TABLE tags_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) +); +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', 'ピージーロジカル'); +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); +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + name | katakana +------------+------------------------ + Groonga | グルンガ + PostgreSQL | ポストグレスキューエル + PostgreSQL | ポスグレ +(3 rows) + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; Added: expected/term-search/text/combined/indexscan.out (+51 -0) 100644 =================================================================== --- /dev/null +++ expected/term-search/text/combined/indexscan.out 2016-04-08 23:55:25 +0900 (b2a402b) @@ -0,0 +1,51 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); +CREATE TABLE readings ( + katakana text PRIMARY KEY +); +CREATE TABLE tags_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) +); +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', 'ピージーロジカル'); +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); +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + name | katakana +------------+------------------------ + Groonga | グルンガ + PostgreSQL | ポスグレ + PostgreSQL | ポストグレスキューエル +(3 rows) + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; Added: expected/term-search/text/combined/seqscan.out (+47 -0) 100644 =================================================================== --- /dev/null +++ expected/term-search/text/combined/seqscan.out 2016-04-08 23:55:25 +0900 (cfc9680) @@ -0,0 +1,47 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); +CREATE TABLE readings ( + katakana text PRIMARY KEY +); +CREATE TABLE tags_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) +); +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', 'ピージーロジカル'); +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + name | katakana +------------+------------------------ + PostgreSQL | ポストグレスキューエル + PostgreSQL | ポスグレ + Groonga | グルンガ +(3 rows) + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; Added: sql/term-search/text/combined/bitmapscan.sql (+53 -0) 100644 =================================================================== --- /dev/null +++ sql/term-search/text/combined/bitmapscan.sql 2016-04-08 23:55:25 +0900 (a950a8a) @@ -0,0 +1,53 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); + +CREATE TABLE readings ( + katakana text PRIMARY KEY +); + +CREATE TABLE tags_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) +); + +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', 'ピージーロジカル'); + +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); + +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_bitmapscan = on; + +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; Added: sql/term-search/text/combined/indexscan.sql (+53 -0) 100644 =================================================================== --- /dev/null +++ sql/term-search/text/combined/indexscan.sql 2016-04-08 23:55:25 +0900 (00cd236) @@ -0,0 +1,53 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); + +CREATE TABLE readings ( + katakana text PRIMARY KEY +); + +CREATE TABLE tags_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) +); + +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', 'ピージーロジカル'); + +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); + +SET enable_seqscan = off; +SET enable_indexscan = on; +SET enable_bitmapscan = off; + +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; Added: sql/term-search/text/combined/seqscan.sql (+48 -0) 100644 =================================================================== --- /dev/null +++ sql/term-search/text/combined/seqscan.sql 2016-04-08 23:55:25 +0900 (c98ee34) @@ -0,0 +1,48 @@ +CREATE TABLE tags ( + name text PRIMARY KEY +); + +CREATE TABLE readings ( + katakana text PRIMARY KEY +); + +CREATE TABLE tags_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) +); + +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', 'ピージーロジカル'); + +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_bitmapscan = off; + +SELECT name, katakana + FROM tags LEFT JOIN tags_readings + INNER JOIN readings + ON tags_readings.reading_katakana = readings.katakana + ON tags.name = tags_readings.tag_name + WHERE tags.name &^ 'Groon' OR + readings.katakana &^~ 'posu'; + +DROP TABLE tags_readings; +DROP TABLE readings; +DROP TABLE tags; -------------- next part -------------- HTML����������������������������...Télécharger
