Kouhei Sutou
null+****@clear*****
Mon Apr 11 00:12:55 JST 2016
Kouhei Sutou 2016-04-11 00:12:55 +0900 (Mon, 11 Apr 2016) New Revision: 20fb5f4fc50abad8c4f5553e214924d51b1dd547 https://github.com/pgroonga/pgroonga/commit/20fb5f4fc50abad8c4f5553e214924d51b1dd547 Message: test: fix test cases to use indexes 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 -13) =================================================================== --- expected/term-search/text/combined/bitmapscan.out 2016-04-11 00:07:43 +0900 (4ecf392) +++ expected/term-search/text/combined/bitmapscan.out 2016-04-11 00:12:55 +0900 (606238c) @@ -32,19 +32,19 @@ CREATE INDEX pgrn_readings_index ON readings 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) +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 + WHERE katakana &^~ 'posu'; + name | score +------------+------- + PostgreSQL | 1 + Groonga | 1 +(2 rows) DROP TABLE tags_readings; DROP TABLE readings; Modified: expected/term-search/text/combined/indexscan.out (+13 -13) =================================================================== --- expected/term-search/text/combined/indexscan.out 2016-04-11 00:07:43 +0900 (b2a402b) +++ expected/term-search/text/combined/indexscan.out 2016-04-11 00:12:55 +0900 (d7ddb6b) @@ -32,19 +32,19 @@ CREATE INDEX pgrn_readings_index ON readings 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) +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 + WHERE katakana &^~ 'posu'; + name | score +------------+------- + PostgreSQL | 1 + Groonga | 1 +(2 rows) DROP TABLE tags_readings; DROP TABLE readings; Modified: expected/term-search/text/combined/seqscan.out (+13 -13) =================================================================== --- expected/term-search/text/combined/seqscan.out 2016-04-11 00:07:43 +0900 (cfc9680) +++ expected/term-search/text/combined/seqscan.out 2016-04-11 00:12:55 +0900 (7271da2) @@ -28,19 +28,19 @@ 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) +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 + WHERE katakana &^~ 'posu'; + name | score +------------+------- + Groonga | 0 + PostgreSQL | 0 +(2 rows) DROP TABLE tags_readings; DROP TABLE readings; Modified: sql/term-search/text/combined/bitmapscan.sql (+8 -7) =================================================================== --- sql/term-search/text/combined/bitmapscan.sql 2016-04-11 00:07:43 +0900 (a950a8a) +++ sql/term-search/text/combined/bitmapscan.sql 2016-04-11 00:12:55 +0900 (3e1f0b8) @@ -40,13 +40,14 @@ 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'; +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 + WHERE katakana &^~ 'posu'; DROP TABLE tags_readings; DROP TABLE readings; Modified: sql/term-search/text/combined/indexscan.sql (+8 -7) =================================================================== --- sql/term-search/text/combined/indexscan.sql 2016-04-11 00:07:43 +0900 (00cd236) +++ sql/term-search/text/combined/indexscan.sql 2016-04-11 00:12:55 +0900 (610e61a) @@ -40,13 +40,14 @@ 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'; +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 + WHERE katakana &^~ 'posu'; DROP TABLE tags_readings; DROP TABLE readings; Modified: sql/term-search/text/combined/seqscan.sql (+8 -7) =================================================================== --- sql/term-search/text/combined/seqscan.sql 2016-04-11 00:07:43 +0900 (c98ee34) +++ sql/term-search/text/combined/seqscan.sql 2016-04-11 00:12:55 +0900 (7eff6c3) @@ -35,13 +35,14 @@ 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'; +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 + WHERE katakana &^~ 'posu'; DROP TABLE tags_readings; DROP TABLE readings; -------------- next part -------------- HTML����������������������������...下載
