概要
PGroongaの実力を確認するために「PostgreSQL全文検索のざっくり性能比較 - pg_bigmとpg_trgmとPGroonga（Wikipediaのタイトルを検索する）」という記事を書きました。
ただ、pg_bigm、pg_trgm、PGroongaそれぞれが本気を出せていなかったので、改めて検証します。
本気を出せていなかったポイント
-
work_memが不足していた（特にpg_bigmとpg_trgm）- 参考: https://qiita.com/abetomo/items/8c61b1fdc2522f8f642d
- =>
work_memを増やして再検証
- 初期化処理分も加味されている
- ミリ秒の世界だと初期化処理の占める割合が多くなる
- （1セッションで1selectだと初期化処理の割合が多くなる）
- => 同じセッション内で10回selectしてその合計で再検証
- ミリ秒の世界だと初期化処理の占める割合が多くなる
-
select * from ...だと検索結果の出力に時間がかかっているかも？- つまり純粋な検索処理の時間計測になってないかも？
- =>
select count(*) from ...で再検証
-
maintenance_work_memが少なかった- これが足りないと
create indexに時間がかかる - =>
maintenance_work_memを増やして再検証
- これが足りないと
ということで検証していきます。
さっそく結論: 測定結果
インデックスの作成時間とサイズ
| 名前 | create index | index size |
|---|---|---|
| PGroonga | 6480.292 ms | 約600MB |
| pg_bigm | 14792.718 ms | 155 MB |
| pg_trgm | 16767.878 ms | 272 MB |
PGroongaはインデックスの作成がはやいけど、サイズはでかい。
日本語の検索
10回合計版
10回同じselectを実行した合計の時間。1回あたりは1/10する。
| 名前 | 象 | 東京 | 技術者 | ロケット | データベース |
|---|---|---|---|---|---|
| PGroonga | 120.535 ms | 145.716 ms | 109.797 ms | 117.317 ms | 109.385 ms |
| pg_bigm | 24.221 ms | 37.683 ms | 5.667 ms | 15.293 ms | 9.977 ms |
| pg_trgm | 2495.44 ms | 2393.92 ms | 4.45 ms | 10.081 ms | 4.335 ms |
PGroongaも頑張ったけど、めちゃくちゃはやくはなかった…。
どのクエリでも1回あったり10msくらいなので、安定感はある。
中央値版（外れ値を除いて確認できる）:
| 名前 | 象 | 東京 | 技術者 | ロケット | データベース |
|---|---|---|---|---|---|
| PGroonga | 9.4165 ms | 11.1305 ms | 8.7225 ms | 9.4385 ms | 8.6185 ms |
| pg_bigm | 1.2835 ms | 2.4025 ms | 0.2715 ms | 1.025 ms | 0.5925 ms |
| pg_trgm | 250.3285 ms | 238.02 ms | 0.215 ms | 0.565 ms | 0.23 ms |
アルファベットで検索
10回合計版
10回同じselectを実行した合計の時間。1回あたりは1/10する。
| 名前 | DB | test | PostgreSQL |
|---|---|---|---|
| PGroonga | 101.925 ms | 99.958 ms | 101.327 ms |
| pg_bigm | 8.955 ms | 44.524 ms | 5.214 ms |
| pg_trgm | 2265.42 ms | 19.647 ms | 4.632 ms |
PGroongaも頑張ったけど、めちゃくちゃはやくはなかった…。
どのクエリでも1回あったり10msくらいなので、安定感はある。
中央値版（外れ値を除いて確認できる）:
| 名前 | DB | test | PostgreSQL |
|---|---|---|---|
| PGroonga | 8.0795 ms | 8.14 ms | 7.964 ms |
| pg_bigm | 0.4455 ms | 3.7199 ms | 0.418 ms |
| pg_trgm | 225.432 ms | 1.221 ms | 0.3705 ms |
補足
Wikipediaのタイトルでの検証でしたが、長めのテキストであるWikipediaの本文でも検証したのでご覧ください。
以降にどのように確認したか掲載します。
環境と準備
ざっくり測定
PGroonga
#!/usr/bin/bash
function run_select() {
query=$1
echo "= ${query} ===============================================" 1>&2
explain_commands=()
for i in {1..10}; do
explain_commands+=("--command" "explain analyze select count(*) from wikipedia_title where page_title &@ '${query}';")
done
result=$(sudo -u postgres psql -d test \
--command "set max_parallel_workers_per_gather = 0" \
--command "set work_mem = '16MB'" \
--command "\pset pager off" \
--command "\timing" \
"${explain_commands[@]}")
echo "${result}" 1>&2
total=$(echo "${result}" | grep '^Time:' | awk '{ total += $2 } END { print total }')
echo "Query: ${query}, Total ${total} ms"
}
sudo systemctl restart postgresql
sudo -u postgres psql \
--command 'drop database if exists test' \
--command 'create database test'
sudo -u postgres psql -d test \
--command "create table wikipedia_title (page_namespace int, page_title text);" \
--command "copy wikipedia_title FROM '/tmp/jawiki-latest-all-titles' with header;" \
--command "create extension if not exists pgroonga;" 1>&2
sudo -u postgres psql -d test \
--command "set maintenance_work_mem = '8GB'" \
--command "\timing" \
--command "create index wikipedia_title_index on wikipedia_title using pgroonga (page_title);"
run_select "象"
run_select "東京"
run_select "技術者"
run_select "ロケット"
run_select "データベース"
run_select "DB"
run_select "test"
run_select "PostgreSQL"
sudo -u postgres psql -d test --command "select pg_size_pretty(pg_relation_size('wikipedia_title_index'));"
db_oid=$(sudo -u postgres psql -d test \
--no-align \
--tuples-only \
--command "SELECT oid FROM pg_database where datname = 'test';")
sudo -u postgres psql -d test_articles --command "vacuum;"
ls -l /var/lib/postgresql/17/main/base/${db_oid}/pgrn* | awk '{ size += $5 } END { print size }'
$ ./title/run-pgroonga.sh 2> log
DROP DATABASE
CREATE DATABASE
SET
Timing is on.
CREATE INDEX
Time: 6480.292 ms (00:06.480)
Query: 象, Total 120.535 ms
Query: 東京, Total 145.716 ms
Query: 技術者, Total 109.797 ms
Query: ロケット, Total 117.317 ms
Query: データベース, Total 109.385 ms
Query: DB, Total 101.925 ms
Query: test, Total 99.958 ms
Query: PostgreSQL, Total 101.327 ms
pg_size_pretty
----------------
0 bytes
(1 row)
VACUUM
630841344
log
CREATE TABLE
COPY 4304153
CREATE EXTENSION
= 象 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=16.957..16.957 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=9.726..16.865 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 17.478 ms
Execution Time: 17.707 ms
(5 rows)
Time: 35.704 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=9.532..9.532 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=8.529..9.476 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.183 ms
Execution Time: 10.163 ms
(5 rows)
Time: 10.416 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.906..8.907 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.976..8.848 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.168 ms
Execution Time: 9.518 ms
(5 rows)
Time: 9.832 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.491..8.491 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.617..8.436 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.258 ms
Execution Time: 9.097 ms
(5 rows)
Time: 9.538 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.626..8.626 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.812..8.568 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.200 ms
Execution Time: 9.213 ms
(5 rows)
Time: 9.489 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.233..8.234 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.415..8.174 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.225 ms
Execution Time: 8.808 ms
(5 rows)
Time: 9.103 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.478..8.478 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.685..8.422 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.214 ms
Execution Time: 9.055 ms
(5 rows)
Time: 9.344 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.341..8.341 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.491..8.285 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.208 ms
Execution Time: 8.906 ms
(5 rows)
Time: 9.188 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=7.908..7.908 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.132..7.853 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.185 ms
Execution Time: 8.479 ms
(5 rows)
Time: 8.762 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=54.87..54.88 rows=1 width=8) (actual time=8.308..8.308 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..44.11 rows=4304 width=0) (actual time=7.528..8.252 rows=2676 loops=1)
Index Cond: (page_title &@ '象'::text)
Planning Time: 0.176 ms
Execution Time: 8.886 ms
(5 rows)
Time: 9.159 ms
= 東京 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=22.511..22.512 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=12.763..22.146 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=12.538..12.538 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 16.275 ms
Execution Time: 23.636 ms
(8 rows)
Time: 40.284 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=12.006..12.007 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=10.011..11.702 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=9.832..9.832 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.199 ms
Execution Time: 14.913 ms
(8 rows)
Time: 15.331 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=11.506..11.506 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=9.764..11.216 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=9.589..9.589 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.163 ms
Execution Time: 12.119 ms
(8 rows)
Time: 12.514 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=10.874..10.875 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=9.102..10.587 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.929..8.929 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.196 ms
Execution Time: 11.510 ms
(8 rows)
Time: 11.825 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=10.233..10.234 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.642..9.958 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.480..8.480 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.170 ms
Execution Time: 10.814 ms
(8 rows)
Time: 11.127 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=9.969..9.970 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.291..9.693 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.128..8.128 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.159 ms
Execution Time: 10.558 ms
(8 rows)
Time: 10.849 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=9.859..9.860 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.212..9.584 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.046..8.046 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.156 ms
Execution Time: 10.472 ms
(8 rows)
Time: 10.766 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=10.019..10.020 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.402..9.751 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.238..8.238 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.150 ms
Execution Time: 10.610 ms
(8 rows)
Time: 10.904 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=10.034..10.034 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.385..9.761 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.208..8.208 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.172 ms
Execution Time: 10.660 ms
(8 rows)
Time: 10.982 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37930.80..37930.81 rows=1 width=8) (actual time=10.268..10.269 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1.08..37920.04 rows=4304 width=0) (actual time=8.649..9.986 rows=15514 loops=1)
Recheck Cond: (page_title &@ '東京'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..0.00 rows=15516 width=0) (actual time=8.486..8.486 rows=15514 loops=1)
Index Cond: (page_title &@ '東京'::text)
Planning Time: 0.164 ms
Execution Time: 10.842 ms
(8 rows)
Time: 11.134 ms
= 技術者 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=11.807..11.807 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=9.864..11.784 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 17.826 ms
Execution Time: 12.582 ms
(5 rows)
Time: 30.889 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=9.315..9.315 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=9.062..9.303 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.160 ms
Execution Time: 10.027 ms
(5 rows)
Time: 10.361 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=8.001..8.002 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.770..7.988 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.116 ms
Execution Time: 8.640 ms
(5 rows)
Time: 8.989 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.866..7.866 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.660..7.854 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.361 ms
Execution Time: 8.472 ms
(5 rows)
Time: 9.102 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.599..7.599 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.409..7.587 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.132 ms
Execution Time: 8.173 ms
(5 rows)
Time: 8.456 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.236..7.236 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.052..7.224 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.145 ms
Execution Time: 7.819 ms
(5 rows)
Time: 8.178 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.282..7.283 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.105..7.270 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.135 ms
Execution Time: 7.853 ms
(5 rows)
Time: 8.164 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.243..7.243 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.062..7.227 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.124 ms
Execution Time: 7.838 ms
(5 rows)
Time: 8.080 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=7.565..7.565 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.384..7.552 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.120 ms
Execution Time: 8.134 ms
(5 rows)
Time: 8.379 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1747.12..1747.13 rows=1 width=8) (actual time=8.056..8.057 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1736.36 rows=4304 width=0) (actual time=7.730..8.035 rows=483 loops=1)
Index Cond: (page_title &@ '技術者'::text)
Planning Time: 0.130 ms
Execution Time: 8.874 ms
(5 rows)
Time: 9.199 ms
= ロケット ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=13.587..13.587 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=9.983..13.546 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 17.798 ms
Execution Time: 14.612 ms
(5 rows)
Time: 32.733 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=9.628..9.628 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=9.155..9.598 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.221 ms
Execution Time: 10.380 ms
(5 rows)
Time: 10.701 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=9.208..9.209 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=8.772..9.178 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.199 ms
Execution Time: 9.908 ms
(5 rows)
Time: 10.191 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=9.133..9.134 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=8.737..9.105 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.181 ms
Execution Time: 9.861 ms
(5 rows)
Time: 10.128 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=9.006..9.006 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=8.609..8.979 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.215 ms
Execution Time: 9.635 ms
(5 rows)
Time: 10.085 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=7.750..7.751 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=7.343..7.724 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.204 ms
Execution Time: 8.407 ms
(5 rows)
Time: 8.792 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=7.825..7.825 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=7.475..7.799 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.182 ms
Execution Time: 8.392 ms
(5 rows)
Time: 8.772 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=7.690..7.690 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=7.293..7.664 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.174 ms
Execution Time: 8.348 ms
(5 rows)
Time: 8.599 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=7.693..7.693 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=7.339..7.666 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.198 ms
Execution Time: 8.272 ms
(5 rows)
Time: 8.542 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5179.92..5179.93 rows=1 width=8) (actual time=7.934..7.934 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..5169.16 rows=4304 width=0) (actual time=7.572..7.909 rows=1145 loops=1)
Index Cond: (page_title &@ 'ロケット'::text)
Planning Time: 0.181 ms
Execution Time: 8.519 ms
(5 rows)
Time: 8.774 ms
= データベース ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=11.288..11.288 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=10.051..11.274 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 18.111 ms
Execution Time: 12.135 ms
(5 rows)
Time: 30.637 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=9.107..9.108 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=8.955..9.097 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.299 ms
Execution Time: 9.728 ms
(5 rows)
Time: 10.255 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.607..7.608 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.480..7.598 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.296 ms
Execution Time: 8.209 ms
(5 rows)
Time: 8.581 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.694..7.694 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.572..7.684 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.278 ms
Execution Time: 8.272 ms
(5 rows)
Time: 8.712 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.620..7.621 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.479..7.611 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.274 ms
Execution Time: 8.223 ms
(5 rows)
Time: 8.642 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.638..7.638 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.515..7.628 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.289 ms
Execution Time: 8.206 ms
(5 rows)
Time: 8.561 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.685..7.685 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.552..7.675 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.250 ms
Execution Time: 8.319 ms
(5 rows)
Time: 8.640 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.639..7.639 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.521..7.629 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.320 ms
Execution Time: 8.199 ms
(5 rows)
Time: 8.597 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.457..7.458 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.334..7.448 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.245 ms
Execution Time: 8.085 ms
(5 rows)
Time: 8.396 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1530.57..1530.58 rows=1 width=8) (actual time=7.386..7.387 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..1519.81 rows=4304 width=0) (actual time=7.272..7.378 rows=360 loops=1)
Index Cond: (page_title &@ 'データベース'::text)
Planning Time: 0.244 ms
Execution Time: 8.047 ms
(5 rows)
Time: 8.364 ms
= DB ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=10.541..10.542 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=9.034..10.511 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 16.284 ms
Execution Time: 11.300 ms
(5 rows)
Time: 27.870 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=8.149..8.149 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.859..8.128 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.130 ms
Execution Time: 8.944 ms
(5 rows)
Time: 9.151 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.980..7.981 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.740..7.961 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.083 ms
Execution Time: 8.602 ms
(5 rows)
Time: 8.753 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.225..7.225 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.007..7.206 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.110 ms
Execution Time: 7.817 ms
(5 rows)
Time: 8.000 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.342..7.342 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.117..7.318 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.110 ms
Execution Time: 7.906 ms
(5 rows)
Time: 8.094 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.227..7.227 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.021..7.208 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.102 ms
Execution Time: 7.795 ms
(5 rows)
Time: 7.960 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.188..7.188 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=6.980..7.169 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.118 ms
Execution Time: 7.755 ms
(5 rows)
Time: 7.937 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.308..7.308 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.079..7.285 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.108 ms
Execution Time: 7.878 ms
(5 rows)
Time: 8.065 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.326..7.327 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=7.114..7.308 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.107 ms
Execution Time: 7.953 ms
(5 rows)
Time: 8.141 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3355.21..3355.22 rows=1 width=8) (actual time=7.193..7.193 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..3344.45 rows=4304 width=0) (actual time=6.986..7.175 rows=843 loops=1)
Index Cond: (page_title &@ 'DB'::text)
Planning Time: 0.128 ms
Execution Time: 7.761 ms
(5 rows)
Time: 7.954 ms
= test ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=10.133..10.133 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=8.405..10.097 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 15.230 ms
Execution Time: 10.946 ms
(5 rows)
Time: 26.445 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.992..7.992 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.700..7.967 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.129 ms
Execution Time: 8.661 ms
(5 rows)
Time: 8.891 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.435..7.436 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.177..7.410 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.140 ms
Execution Time: 8.018 ms
(5 rows)
Time: 8.240 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.267..7.268 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.025..7.243 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.112 ms
Execution Time: 7.834 ms
(5 rows)
Time: 8.016 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.323..7.324 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.084..7.298 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.104 ms
Execution Time: 7.921 ms
(5 rows)
Time: 8.095 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.274..7.275 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.036..7.250 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.137 ms
Execution Time: 7.833 ms
(5 rows)
Time: 8.050 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.420..7.420 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.168..7.393 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.104 ms
Execution Time: 8.021 ms
(5 rows)
Time: 8.191 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.207..7.208 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=6.962..7.183 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.109 ms
Execution Time: 7.776 ms
(5 rows)
Time: 7.953 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.449..7.449 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=7.202..7.424 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.105 ms
Execution Time: 8.013 ms
(5 rows)
Time: 8.185 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4602.42..4602.43 rows=1 width=8) (actual time=7.147..7.147 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..4591.66 rows=4304 width=0) (actual time=6.909..7.123 rows=1164 loops=1)
Index Cond: (page_title &@ 'test'::text)
Planning Time: 0.104 ms
Execution Time: 7.723 ms
(5 rows)
Time: 7.892 ms
= PostgreSQL ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=9.193..9.194 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=9.155..9.190 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 17.247 ms
Execution Time: 10.051 ms
(5 rows)
Time: 27.752 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=8.486..8.487 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=8.480..8.483 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.122 ms
Execution Time: 9.170 ms
(5 rows)
Time: 9.558 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.767..7.767 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.761..7.764 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.127 ms
Execution Time: 8.453 ms
(5 rows)
Time: 8.793 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.008..7.008 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.003..7.006 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.114 ms
Execution Time: 7.571 ms
(5 rows)
Time: 7.809 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.285..7.286 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.280..7.283 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.100 ms
Execution Time: 7.866 ms
(5 rows)
Time: 8.084 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.014..7.015 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.010..7.013 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.104 ms
Execution Time: 7.588 ms
(5 rows)
Time: 7.801 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.165..7.165 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.160..7.163 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.093 ms
Execution Time: 7.735 ms
(5 rows)
Time: 7.933 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.019..7.020 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.016..7.018 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.094 ms
Execution Time: 7.609 ms
(5 rows)
Time: 7.812 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.200..7.201 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.196..7.199 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.097 ms
Execution Time: 7.789 ms
(5 rows)
Time: 7.995 ms
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34.82..34.83 rows=1 width=8) (actual time=7.016..7.017 rows=1 loops=1)
-> Index Scan using wikipedia_title_index on wikipedia_title (cost=0.00..24.06 rows=4304 width=0) (actual time=7.012..7.015 rows=4 loops=1)
Index Cond: (page_title &@ 'PostgreSQL'::text)
Planning Time: 0.100 ms
Execution Time: 7.584 ms
(5 rows)
Time: 7.790 ms
pg_bigm
#!/usr/bin/bash
function run_select() {
query=$1
echo "= ${query} ===============================================" 1>&2
explain_commands=()
for i in {1..10}; do
explain_commands+=("--command" "explain analyze select count(*) from wikipedia_title where page_title like '%${query}%';")
done
result=$(sudo -u postgres psql -d test \
--command "set max_parallel_workers_per_gather = 0" \
--command "set work_mem = '16MB'" \
--command "\pset pager off" \
--command "\timing" \
"${explain_commands[@]}")
echo "${result}" 1>&2
total=$(echo "${result}" | grep '^Time:' | awk '{ total += $2 } END { print total }')
echo "Query: ${query}, Total ${total} ms"
}
sudo systemctl restart postgresql
sudo -u postgres psql \
--command 'drop database if exists test' \
--command 'create database test'
sudo -u postgres psql -d test \
--command "create table wikipedia_title (page_namespace int, page_title text);" \
--command "copy wikipedia_title FROM '/tmp/jawiki-latest-all-titles' with header;" \
--command "create extension if not exists pg_bigm;" 1>&2
sudo -u postgres psql -d test \
--command "set maintenance_work_mem = '8GB'" \
--command "\timing" \
--command "create index wikipedia_title_index on wikipedia_title using gin (page_title gin_bigm_ops);"
run_select "象"
run_select "東京"
run_select "技術者"
run_select "ロケット"
run_select "データベース"
run_select "DB"
run_select "test"
run_select "PostgreSQL"
sudo -u postgres psql -d test --command "select pg_size_pretty(pg_relation_size('wikipedia_title_index'));"
$ ./title/run-pg_bigm.sh 2> log
DROP DATABASE
CREATE DATABASE
SET
Timing is on.
CREATE INDEX
Time: 14792.718 ms (00:14.793)
Query: 象, Total 24.221 ms
Query: 東京, Total 37.683 ms
Query: 技術者, Total 5.667 ms
Query: ロケット, Total 15.293 ms
Query: データベース, Total 9.977 ms
Query: DB, Total 8.955 ms
Query: test, Total 44.524 ms
Query: PostgreSQL, Total 5.214 ms
pg_size_pretty
----------------
155 MB
(1 row)
log
CREATE TABLE
COPY 4304153
CREATE EXTENSION
= 象 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=10.597..10.598 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.932..10.475 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.760..0.760 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.561 ms
Execution Time: 10.737 ms
(8 rows)
Time: 12.048 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.808..1.808 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.705..1.741 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.568..0.568 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.064 ms
Execution Time: 1.836 ms
(8 rows)
Time: 1.979 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.290..1.290 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.598..1.229 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.469..0.469 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.017 ms
Execution Time: 1.311 ms
(8 rows)
Time: 1.378 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.233..1.233 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.604..1.170 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.483..0.483 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.013 ms
Execution Time: 1.256 ms
(8 rows)
Time: 1.317 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.275..1.276 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.594..1.209 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.473..0.473 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.027 ms
Execution Time: 1.299 ms
(8 rows)
Time: 1.376 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.144..1.145 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.587..1.085 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.469..0.469 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.016 ms
Execution Time: 1.162 ms
(8 rows)
Time: 1.219 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.127..1.127 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.575..1.067 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.458..0.458 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.012 ms
Execution Time: 1.144 ms
(8 rows)
Time: 1.191 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.145..1.146 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.557..1.086 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.443..0.443 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.017 ms
Execution Time: 1.169 ms
(8 rows)
Time: 1.228 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.157..1.158 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.576..1.097 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.455..0.455 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.027 ms
Execution Time: 1.178 ms
(8 rows)
Time: 1.250 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34872.34..34872.35 rows=1 width=8) (actual time=1.168..1.168 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=1201.85..34441.92 rows=172166 width=0) (actual time=0.566..1.108 rows=2676 loops=1)
Recheck Cond: (page_title ~~ '%象%'::text)
Heap Blocks: exact=1672
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..1158.81 rows=172166 width=0) (actual time=0.446..0.446 rows=2676 loops=1)
Index Cond: (page_title ~~ '%象%'::text)
Planning Time: 0.011 ms
Execution Time: 1.186 ms
(8 rows)
Time: 1.235 ms
= 東京 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=14.859..14.859 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=1.536..14.445 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=1.261..1.261 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.282 ms
Execution Time: 14.972 ms
(8 rows)
Time: 15.570 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.827..2.828 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.833..2.511 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.646..0.647 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.038 ms
Execution Time: 2.857 ms
(8 rows)
Time: 2.984 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.491..2.492 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.818..2.175 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.628..0.628 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.021 ms
Execution Time: 2.516 ms
(8 rows)
Time: 2.592 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.305..2.305 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.773..1.997 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.591..0.591 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.024 ms
Execution Time: 2.326 ms
(8 rows)
Time: 2.396 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.278..2.278 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.785..1.967 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.594..0.594 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.017 ms
Execution Time: 2.296 ms
(8 rows)
Time: 2.353 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.251..2.251 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.759..1.938 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.581..0.581 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.016 ms
Execution Time: 2.266 ms
(8 rows)
Time: 2.319 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.220..2.220 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.763..1.911 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.579..0.579 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.010 ms
Execution Time: 2.236 ms
(8 rows)
Time: 2.279 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.389..2.389 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.808..2.060 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.618..0.618 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.011 ms
Execution Time: 2.410 ms
(8 rows)
Time: 2.457 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.342..2.343 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.815..2.022 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.617..0.617 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.012 ms
Execution Time: 2.359 ms
(8 rows)
Time: 2.409 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4599.15..4599.16 rows=1 width=8) (actual time=2.259..2.259 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=28.58..4595.70 rows=1377 width=0) (actual time=0.796..1.947 rows=15514 loops=1)
Recheck Cond: (page_title ~~ '%東京%'::text)
Heap Blocks: exact=2548
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..28.24 rows=1377 width=0) (actual time=0.616..0.616 rows=15514 loops=1)
Index Cond: (page_title ~~ '%東京%'::text)
Planning Time: 0.011 ms
Execution Time: 2.280 ms
(8 rows)
Time: 2.324 ms
= 技術者 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=2.186..2.186 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.247..2.162 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.201..0.202 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.339 ms
Execution Time: 2.284 ms
(8 rows)
Time: 3.019 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.334..0.334 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.095..0.322 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.068..0.068 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.033 ms
Execution Time: 0.344 ms
(8 rows)
Time: 0.435 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.259..0.259 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.090..0.247 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.062..0.063 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.013 ms
Execution Time: 0.265 ms
(8 rows)
Time: 0.319 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.237..0.238 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.078..0.226 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.055..0.055 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.008 ms
Execution Time: 0.241 ms
(8 rows)
Time: 0.277 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.230..0.230 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.074..0.219 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.053..0.053 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.006 ms
Execution Time: 0.234 ms
(8 rows)
Time: 0.265 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.234..0.234 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.074..0.222 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.052..0.052 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.006 ms
Execution Time: 0.237 ms
(8 rows)
Time: 0.266 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.224..0.224 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.070..0.213 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.050..0.050 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.006 ms
Execution Time: 0.227 ms
(8 rows)
Time: 0.255 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.224..0.224 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.069..0.213 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.049..0.049 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.006 ms
Execution Time: 0.227 ms
(8 rows)
Time: 0.255 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.230..0.230 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.068..0.218 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.049..0.049 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.233 ms
(8 rows)
Time: 0.261 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1608.63..1608.64 rows=1 width=8) (actual time=0.279..0.279 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=40.84..1607.56 rows=430 width=0) (actual time=0.074..0.266 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..40.73 rows=430 width=0) (actual time=0.053..0.053 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.006 ms
Execution Time: 0.282 ms
(8 rows)
Time: 0.315 ms
= ロケット ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=5.100..5.102 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.925..5.054 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.850..0.850 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.288 ms
Execution Time: 5.180 ms
(9 rows)
Time: 5.784 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=1.216..1.216 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.614..1.180 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.551..0.552 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.045 ms
Execution Time: 1.237 ms
(9 rows)
Time: 1.376 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=1.019..1.019 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.580..0.985 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.522..0.522 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.021 ms
Execution Time: 1.028 ms
(9 rows)
Time: 1.107 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.997..0.997 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.580..0.962 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.519..0.519 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.017 ms
Execution Time: 1.004 ms
(9 rows)
Time: 1.068 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.992..0.992 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.581..0.958 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.519..0.519 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.014 ms
Execution Time: 0.998 ms
(9 rows)
Time: 1.055 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.940..0.940 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.548..0.906 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.493..0.493 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.012 ms
Execution Time: 0.945 ms
(9 rows)
Time: 0.995 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.931..0.932 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.543..0.898 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.489..0.489 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.011 ms
Execution Time: 0.936 ms
(9 rows)
Time: 0.984 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.929..0.930 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.539..0.897 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.486..0.486 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.010 ms
Execution Time: 0.934 ms
(9 rows)
Time: 0.981 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.925..0.926 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.542..0.893 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.486..0.486 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.010 ms
Execution Time: 0.930 ms
(9 rows)
Time: 0.976 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1625.86..1625.87 rows=1 width=8) (actual time=0.917..0.918 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=58.07..1624.78 rows=430 width=0) (actual time=0.536..0.886 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=652
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..57.96 rows=430 width=0) (actual time=0.482..0.482 rows=1146 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.010 ms
Execution Time: 0.922 ms
(9 rows)
Time: 0.967 ms
= データベース ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=3.021..3.022 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=1.403..2.995 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=1.371..1.372 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.399 ms
Execution Time: 3.136 ms
(8 rows)
Time: 4.080 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.804..0.805 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.661..0.792 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.639..0.639 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.045 ms
Execution Time: 0.818 ms
(8 rows)
Time: 0.967 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.643..0.643 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.491..0.632 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.475..0.476 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.038 ms
Execution Time: 0.655 ms
(8 rows)
Time: 0.767 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.565..0.565 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.446..0.555 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.433..0.433 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.015 ms
Execution Time: 0.571 ms
(8 rows)
Time: 0.626 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.540..0.540 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.431..0.530 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.419..0.419 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.011 ms
Execution Time: 0.545 ms
(8 rows)
Time: 0.590 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.545..0.545 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.433..0.536 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.420..0.420 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.009 ms
Execution Time: 0.550 ms
(8 rows)
Time: 0.589 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.547..0.547 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.435..0.538 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.422..0.422 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.009 ms
Execution Time: 0.551 ms
(8 rows)
Time: 0.591 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.549..0.550 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.442..0.540 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.429..0.429 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.009 ms
Execution Time: 0.554 ms
(8 rows)
Time: 0.594 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.540..0.540 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.428..0.528 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.416..0.416 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.009 ms
Execution Time: 0.544 ms
(8 rows)
Time: 0.583 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.31..1660.32 rows=1 width=8) (actual time=0.546..0.546 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.52..1659.24 rows=430 width=0) (actual time=0.435..0.537 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.41 rows=430 width=0) (actual time=0.423..0.423 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.008 ms
Execution Time: 0.550 ms
(8 rows)
Time: 0.590 ms
= DB ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=4.097..4.097 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.265..4.014 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.186..0.186 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.264 ms
Execution Time: 4.204 ms
(8 rows)
Time: 4.780 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.479..0.479 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.149..0.437 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.104..0.104 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.042 ms
Execution Time: 0.490 ms
(8 rows)
Time: 0.614 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.410..0.410 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.128..0.368 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.088..0.088 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.021 ms
Execution Time: 0.416 ms
(8 rows)
Time: 0.507 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.380..0.380 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.115..0.337 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.080..0.081 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.009 ms
Execution Time: 0.384 ms
(8 rows)
Time: 0.448 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.379..0.379 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.119..0.339 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.084..0.084 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.008 ms
Execution Time: 0.383 ms
(8 rows)
Time: 0.424 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.403..0.403 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.135..0.360 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.097..0.097 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.007 ms
Execution Time: 0.406 ms
(8 rows)
Time: 0.441 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.379..0.379 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.114..0.338 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.080..0.080 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.007 ms
Execution Time: 0.383 ms
(8 rows)
Time: 0.416 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.406..0.406 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.112..0.361 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.080..0.080 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.006 ms
Execution Time: 0.409 ms
(8 rows)
Time: 0.443 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.378..0.379 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.114..0.338 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.081..0.081 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.007 ms
Execution Time: 0.382 ms
(8 rows)
Time: 0.418 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49840.55..49840.56 rows=1 width=8) (actual time=0.423..0.424 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=5840.09..47688.48 rows=860831 width=0) (actual time=0.112..0.380 rows=1689 loops=1)
Recheck Cond: (page_title ~~ '%DB%'::text)
Heap Blocks: exact=483
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..5624.88 rows=860831 width=0) (actual time=0.078..0.078 rows=1689 loops=1)
Index Cond: (page_title ~~ '%DB%'::text)
Planning Time: 0.007 ms
Execution Time: 0.427 ms
(8 rows)
Time: 0.464 ms
= test ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=10.159..10.160 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.839..10.046 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.648..2.648 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.216 ms
Execution Time: 10.240 ms
(9 rows)
Time: 10.720 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.930..3.931 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.551..3.849 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.427..2.428 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.034 ms
Execution Time: 3.957 ms
(9 rows)
Time: 4.086 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.977..3.977 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.705..3.893 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.582..2.583 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.047 ms
Execution Time: 3.998 ms
(9 rows)
Time: 4.118 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.793..3.793 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.620..3.713 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.492..2.492 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.015 ms
Execution Time: 3.812 ms
(9 rows)
Time: 3.889 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.578..3.578 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.499..3.499 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.378..2.378 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.029 ms
Execution Time: 3.601 ms
(9 rows)
Time: 3.698 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.586..3.586 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.513..3.505 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.393..2.393 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.034 ms
Execution Time: 3.604 ms
(9 rows)
Time: 3.701 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.659..3.659 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.585..3.581 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.462..2.462 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.015 ms
Execution Time: 3.673 ms
(9 rows)
Time: 3.739 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.543..3.543 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.477..3.466 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.356..2.356 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.013 ms
Execution Time: 3.558 ms
(9 rows)
Time: 3.621 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.516..3.517 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.417..3.442 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.302..2.302 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.013 ms
Execution Time: 3.531 ms
(9 rows)
Time: 3.595 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33314.79..33314.80 rows=1 width=8) (actual time=3.294..3.294 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=286.08..33228.70 rows=34433 width=0) (actual time=2.338..3.226 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 1215
Heap Blocks: exact=1426
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..277.47 rows=34433 width=0) (actual time=2.227..2.227 rows=4325 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.013 ms
Execution Time: 3.305 ms
(9 rows)
Time: 3.357 ms
= PostgreSQL ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.736..0.737 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.699..0.733 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.680..0.681 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.274 ms
Execution Time: 0.839 ms
(8 rows)
Time: 1.403 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.414..0.415 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.411..0.413 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.409..0.409 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.039 ms
Execution Time: 0.424 ms
(8 rows)
Time: 0.521 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.395..0.395 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.393..0.394 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.391..0.391 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.014 ms
Execution Time: 0.401 ms
(8 rows)
Time: 0.449 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.382..0.382 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.380..0.381 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.378..0.378 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.010 ms
Execution Time: 0.387 ms
(8 rows)
Time: 0.427 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.372..0.372 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.370..0.372 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.368..0.368 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.009 ms
Execution Time: 0.377 ms
(8 rows)
Time: 0.415 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.381..0.381 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.379..0.380 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.377..0.377 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.008 ms
Execution Time: 0.385 ms
(8 rows)
Time: 0.421 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.373..0.373 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.371..0.372 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.369..0.369 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.008 ms
Execution Time: 0.377 ms
(8 rows)
Time: 0.410 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.355..0.355 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.353..0.354 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.351..0.351 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.007 ms
Execution Time: 0.358 ms
(8 rows)
Time: 0.390 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.346..0.346 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.344..0.346 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.342..0.343 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.007 ms
Execution Time: 0.350 ms
(8 rows)
Time: 0.383 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1729.22..1729.23 rows=1 width=8) (actual time=0.354..0.354 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=161.43..1728.15 rows=430 width=0) (actual time=0.351..0.353 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..161.32 rows=430 width=0) (actual time=0.350..0.350 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.008 ms
Execution Time: 0.358 ms
(8 rows)
Time: 0.395 ms
pg_trgm
#!/usr/bin/bash
function run_select() {
query=$1
echo "= ${query} ===============================================" 1>&2
explain_commands=()
for i in {1..10}; do
explain_commands+=("--command" "explain analyze select count(*) from wikipedia_title where page_title like '%${query}%';")
done
result=$(sudo -u postgres psql -d test \
--command "set max_parallel_workers_per_gather = 0" \
--command "set work_mem = '16MB'" \
--command "\pset pager off" \
--command "\timing" \
"${explain_commands[@]}")
echo "${result}" 1>&2
total=$(echo "${result}" | grep '^Time:' | awk '{ total += $2 } END { print total }')
echo "Query: ${query}, Total ${total} ms"
}
sudo systemctl restart postgresql
sudo -u postgres psql \
--command 'drop database if exists test' \
--command 'create database test'
sudo -u postgres psql -d test \
--command "create table wikipedia_title (page_namespace int, page_title text);" \
--command "copy wikipedia_title FROM '/tmp/jawiki-latest-all-titles' with header;" \
--command "create extension if not exists pg_trgm;" 1>&2
sudo -u postgres psql -d test \
--command "set maintenance_work_mem = '8GB'" \
--command "\timing" \
--command "create index wikipedia_title_index on wikipedia_title using gin (page_title gin_trgm_ops);"
run_select "象"
run_select "東京"
run_select "技術者"
run_select "ロケット"
run_select "データベース"
run_select "DB"
run_select "test"
run_select "PostgreSQL"
sudo -u postgres psql -d test --command "select pg_size_pretty(pg_relation_size('wikipedia_title_index'));"
$ ./title/run-pg_trgm.sh 2> log
DROP DATABASE
CREATE DATABASE
SET
Timing is on.
CREATE INDEX
Time: 16767.878 ms (00:16.768)
Query: 象, Total 2495.44 ms
Query: 東京, Total 2393.92 ms
Query: 技術者, Total 4.45 ms
Query: ロケット, Total 10.081 ms
Query: データベース, Total 4.335 ms
Query: DB, Total 2265.42 ms
Query: test, Total 19.647 ms
Query: PostgreSQL, Total 4.632 ms
pg_size_pretty
----------------
272 MB
(1 row)
log
CREATE TABLE
COPY 4304153
CREATE EXTENSION
= 象 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=251.384..251.385 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.549..251.305 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.493 ms
Execution Time: 251.416 ms
(6 rows)
Time: 252.555 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=241.894..241.895 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.396..241.814 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.060 ms
Execution Time: 241.906 ms
(6 rows)
Time: 242.182 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=261.017..261.018 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.364..260.908 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.039 ms
Execution Time: 261.033 ms
(6 rows)
Time: 261.224 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=256.524..256.524 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.466..256.437 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.057 ms
Execution Time: 256.536 ms
(6 rows)
Time: 256.835 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=260.510..260.511 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.440..260.420 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.042 ms
Execution Time: 260.522 ms
(6 rows)
Time: 260.888 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=247.847..247.848 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.389..247.751 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.041 ms
Execution Time: 247.858 ms
(6 rows)
Time: 248.102 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=244.923..244.925 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.506..244.844 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.059 ms
Execution Time: 244.939 ms
(6 rows)
Time: 245.129 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=234.294..234.294 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.401..234.214 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.053 ms
Execution Time: 234.306 ms
(6 rows)
Time: 234.475 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=235.016..235.017 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.376..234.940 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.041 ms
Execution Time: 235.028 ms
(6 rows)
Time: 235.184 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=85320.33..85320.34 rows=1 width=8) (actual time=258.713..258.713 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=172166 width=0) (actual time=0.436..258.600 rows=2676 loops=1)
Filter: (page_title ~~ '%象%'::text)
Rows Removed by Filter: 4301477
Planning Time: 0.042 ms
Execution Time: 258.723 ms
(6 rows)
Time: 258.863 ms
= 東京 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=255.213..255.214 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.042..254.843 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.416 ms
Execution Time: 255.272 ms
(6 rows)
Time: 256.153 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=237.891..237.892 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.026..237.580 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.039 ms
Execution Time: 237.902 ms
(6 rows)
Time: 238.121 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=237.063..237.064 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.027..236.742 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.039 ms
Execution Time: 237.077 ms
(6 rows)
Time: 237.300 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=238.723..238.724 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.028..238.413 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.039 ms
Execution Time: 238.734 ms
(6 rows)
Time: 239.036 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=232.425..232.425 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.038..232.118 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.052 ms
Execution Time: 232.437 ms
(6 rows)
Time: 232.795 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=236.929..236.930 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.051..236.601 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.063 ms
Execution Time: 236.942 ms
(6 rows)
Time: 237.285 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=240.312..240.312 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.030..239.997 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.039 ms
Execution Time: 240.322 ms
(6 rows)
Time: 240.522 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=237.661..237.662 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.027..237.343 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.038 ms
Execution Time: 237.671 ms
(6 rows)
Time: 237.919 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=233.095..233.096 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.029..232.791 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.037 ms
Execution Time: 233.106 ms
(6 rows)
Time: 233.327 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=84893.36..84893.37 rows=1 width=8) (actual time=241.265..241.266 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=1377 width=0) (actual time=0.032..240.947 rows=15514 loops=1)
Filter: (page_title ~~ '%東京%'::text)
Rows Removed by Filter: 4288639
Planning Time: 0.059 ms
Execution Time: 241.276 ms
(6 rows)
Time: 241.458 ms
= 技術者 ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=1.532..1.532 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.108..1.507 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.071..0.072 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.507 ms
Execution Time: 1.614 ms
(8 rows)
Time: 2.404 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.247..0.247 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.059..0.236 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.035..0.035 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.051 ms
Execution Time: 0.259 ms
(8 rows)
Time: 0.392 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.180..0.180 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.044..0.170 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.024..0.024 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.011 ms
Execution Time: 0.184 ms
(8 rows)
Time: 0.225 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.180..0.180 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.045..0.170 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.027..0.027 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.007 ms
Execution Time: 0.183 ms
(8 rows)
Time: 0.213 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.188..0.189 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.042..0.178 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.023..0.023 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.191 ms
(8 rows)
Time: 0.217 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.168..0.169 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.037..0.159 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.021..0.021 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.171 ms
(8 rows)
Time: 0.195 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.166..0.166 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.037..0.156 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.021..0.021 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.168 ms
(8 rows)
Time: 0.190 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.167..0.167 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.036..0.158 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.021..0.021 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.004 ms
Execution Time: 0.169 ms
(8 rows)
Time: 0.192 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.169..0.169 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.036..0.160 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.021..0.021 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.171 ms
(8 rows)
Time: 0.218 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.66..1595.67 rows=1 width=8) (actual time=0.177..0.178 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=27.87..1594.59 rows=430 width=0) (actual time=0.039..0.168 rows=483 loops=1)
Recheck Cond: (page_title ~~ '%技術者%'::text)
Heap Blocks: exact=375
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..27.76 rows=430 width=0) (actual time=0.023..0.023 rows=483 loops=1)
Index Cond: (page_title ~~ '%技術者%'::text)
Planning Time: 0.005 ms
Execution Time: 0.180 ms
(8 rows)
Time: 0.204 ms
= ロケット ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=3.831..3.832 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.409..3.777 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.339..0.339 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.582 ms
Execution Time: 3.920 ms
(8 rows)
Time: 4.812 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.669..0.669 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.211..0.640 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.161..0.161 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.052 ms
Execution Time: 0.684 ms
(8 rows)
Time: 0.812 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.524..0.525 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.186..0.497 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.140..0.140 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.017 ms
Execution Time: 0.531 ms
(8 rows)
Time: 0.591 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.518..0.519 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.185..0.491 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.137..0.137 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.012 ms
Execution Time: 0.523 ms
(8 rows)
Time: 0.570 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.508..0.508 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.177..0.481 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.134..0.134 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.009 ms
Execution Time: 0.513 ms
(8 rows)
Time: 0.559 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.516..0.517 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.170..0.489 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.127..0.127 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.018 ms
Execution Time: 0.523 ms
(8 rows)
Time: 0.580 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.511..0.511 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.175..0.483 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.130..0.130 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.011 ms
Execution Time: 0.516 ms
(8 rows)
Time: 0.560 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.481..0.481 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.163..0.454 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.121..0.121 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.008 ms
Execution Time: 0.484 ms
(8 rows)
Time: 0.520 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.502..0.502 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.166..0.474 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.124..0.125 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.007 ms
Execution Time: 0.505 ms
(8 rows)
Time: 0.540 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1617.15..1617.16 rows=1 width=8) (actual time=0.496..0.497 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=49.36..1616.08 rows=430 width=0) (actual time=0.166..0.469 rows=1145 loops=1)
Recheck Cond: (page_title ~~ '%ロケット%'::text)
Heap Blocks: exact=651
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..49.25 rows=430 width=0) (actual time=0.123..0.123 rows=1145 loops=1)
Index Cond: (page_title ~~ '%ロケット%'::text)
Planning Time: 0.008 ms
Execution Time: 0.500 ms
(8 rows)
Time: 0.537 ms
= データベース ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=1.008..1.009 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.170..0.995 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.146..0.146 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.483 ms
Execution Time: 1.074 ms
(8 rows)
Time: 1.997 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.235..0.235 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.097..0.224 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.083..0.083 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.044 ms
Execution Time: 0.248 ms
(8 rows)
Time: 0.366 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.206..0.206 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.079..0.196 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.067..0.067 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.012 ms
Execution Time: 0.211 ms
(8 rows)
Time: 0.403 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.201..0.202 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.080..0.191 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.067..0.067 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.009 ms
Execution Time: 0.206 ms
(8 rows)
Time: 0.250 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.194..0.195 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.074..0.184 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.063..0.063 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.007 ms
Execution Time: 0.198 ms
(8 rows)
Time: 0.232 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.193..0.193 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.076..0.183 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.066..0.066 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.006 ms
Execution Time: 0.196 ms
(8 rows)
Time: 0.228 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.185..0.185 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.069..0.175 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.059..0.059 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.006 ms
Execution Time: 0.188 ms
(8 rows)
Time: 0.218 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.182..0.182 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.067..0.172 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.057..0.057 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.006 ms
Execution Time: 0.185 ms
(8 rows)
Time: 0.214 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.181..0.181 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.067..0.171 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.057..0.057 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.006 ms
Execution Time: 0.184 ms
(8 rows)
Time: 0.212 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1660.13..1660.14 rows=1 width=8) (actual time=0.183..0.183 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=92.33..1659.05 rows=430 width=0) (actual time=0.066..0.173 rows=360 loops=1)
Recheck Cond: (page_title ~~ '%データベース%'::text)
Heap Blocks: exact=203
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..92.23 rows=430 width=0) (actual time=0.057..0.057 rows=360 loops=1)
Index Cond: (page_title ~~ '%データベース%'::text)
Planning Time: 0.006 ms
Execution Time: 0.186 ms
(8 rows)
Time: 0.215 ms
= DB ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=237.977..237.977 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.722..237.926 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.444 ms
Execution Time: 238.014 ms
(6 rows)
Time: 238.950 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=227.111..227.112 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.365..227.069 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.051 ms
Execution Time: 227.124 ms
(6 rows)
Time: 227.332 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=224.634..224.635 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.273..224.594 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.041 ms
Execution Time: 224.646 ms
(6 rows)
Time: 224.888 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=225.677..225.678 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.310..225.636 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.042 ms
Execution Time: 225.688 ms
(6 rows)
Time: 226.002 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=223.807..223.808 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.284..223.766 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.041 ms
Execution Time: 223.819 ms
(6 rows)
Time: 224.047 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=227.378..227.379 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.262..227.339 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.043 ms
Execution Time: 227.390 ms
(6 rows)
Time: 227.643 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=224.285..224.286 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.275..224.244 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.069 ms
Execution Time: 224.295 ms
(6 rows)
Time: 224.615 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=225.660..225.661 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.266..225.620 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.043 ms
Execution Time: 225.671 ms
(6 rows)
Time: 225.976 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=222.386..222.387 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.272..222.347 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.063 ms
Execution Time: 222.397 ms
(6 rows)
Time: 222.668 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87041.99..87042.00 rows=1 width=8) (actual time=223.062..223.063 rows=1 loops=1)
-> Seq Scan on wikipedia_title (cost=0.00..84889.91 rows=860831 width=0) (actual time=1.295..223.022 rows=1689 loops=1)
Filter: (page_title ~~ '%DB%'::text)
Rows Removed by Filter: 4302464
Planning Time: 0.035 ms
Execution Time: 223.074 ms
(6 rows)
Time: 223.297 ms
= test ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=7.184..7.185 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.806..7.049 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.699..0.700 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.597 ms
Execution Time: 7.279 ms
(9 rows)
Time: 8.200 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.349..1.350 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.448..1.284 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.365..0.365 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.039 ms
Execution Time: 1.376 ms
(9 rows)
Time: 1.642 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.092..1.092 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.421..1.029 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.342..0.342 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.020 ms
Execution Time: 1.111 ms
(9 rows)
Time: 1.194 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.117..1.117 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.423..1.054 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.347..0.347 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.025 ms
Execution Time: 1.144 ms
(9 rows)
Time: 1.216 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.250..1.251 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.437..1.179 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.355..0.356 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.013 ms
Execution Time: 1.263 ms
(9 rows)
Time: 1.321 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.222..1.222 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.443..1.153 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.356..0.356 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.012 ms
Execution Time: 1.233 ms
(9 rows)
Time: 1.283 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.167..1.167 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.438..1.101 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.352..0.352 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.011 ms
Execution Time: 1.177 ms
(9 rows)
Time: 1.226 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.148..1.148 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.410..1.081 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.334..0.334 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.010 ms
Execution Time: 1.157 ms
(9 rows)
Time: 1.201 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.104..1.105 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.392..1.038 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.318..0.318 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.009 ms
Execution Time: 1.114 ms
(9 rows)
Time: 1.157 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33306.08..33306.09 rows=1 width=8) (actual time=1.145..1.145 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=277.37..33220.00 rows=34433 width=0) (actual time=0.410..1.079 rows=3110 loops=1)
Recheck Cond: (page_title ~~ '%test%'::text)
Rows Removed by Index Recheck: 634
Heap Blocks: exact=1068
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..268.77 rows=34433 width=0) (actual time=0.333..0.333 rows=3744 loops=1)
Index Cond: (page_title ~~ '%test%'::text)
Planning Time: 0.010 ms
Execution Time: 1.157 ms
(9 rows)
Time: 1.207 ms
= PostgreSQL ===============================================
SET
SET
Pager usage is off.
Timing is on.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.345..0.345 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.319..0.337 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.307..0.307 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.576 ms
Execution Time: 0.433 ms
(8 rows)
Time: 1.446 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.165..0.165 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.161..0.163 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.157..0.157 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.025 ms
Execution Time: 0.175 ms
(8 rows)
Time: 0.429 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.148..0.148 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.145..0.147 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.142..0.142 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.020 ms
Execution Time: 0.156 ms
(8 rows)
Time: 0.308 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.147..0.147 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.144..0.146 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.141..0.141 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.019 ms
Execution Time: 0.155 ms
(8 rows)
Time: 0.307 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.156..0.156 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.152..0.154 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.148..0.148 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.022 ms
Execution Time: 0.165 ms
(8 rows)
Time: 0.401 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.148..0.148 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.145..0.147 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.141..0.142 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.019 ms
Execution Time: 0.156 ms
(8 rows)
Time: 0.340 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.155..0.156 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.152..0.154 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.148..0.148 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.022 ms
Execution Time: 0.165 ms
(8 rows)
Time: 0.451 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.147..0.148 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.144..0.146 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.141..0.141 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.019 ms
Execution Time: 0.156 ms
(8 rows)
Time: 0.306 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.151..0.151 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.147..0.150 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.144..0.144 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.018 ms
Execution Time: 0.159 ms
(8 rows)
Time: 0.240 ms
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1746.08..1746.09 rows=1 width=8) (actual time=0.160..0.160 rows=1 loops=1)
-> Bitmap Heap Scan on wikipedia_title (cost=178.28..1745.00 rows=430 width=0) (actual time=0.156..0.158 rows=4 loops=1)
Recheck Cond: (page_title ~~ '%PostgreSQL%'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on wikipedia_title_index (cost=0.00..178.18 rows=430 width=0) (actual time=0.152..0.152 rows=4 loops=1)
Index Cond: (page_title ~~ '%PostgreSQL%'::text)
Planning Time: 0.023 ms
Execution Time: 0.170 ms
(8 rows)
Time: 0.404 ms