toshio_uchiy****@mirro*****
toshio_uchiy****@mirro*****
2017年 9月 15日 (金) 09:38:12 JST
メーリングリストの皆様
お世話になります。内山です。
PGRonnga で作ったインデックスを使って検索してほしい
のにシーケンシャルスキャンがおこなわれる事例が PostgreSQL で
ありました。PGRonnga の問題か？ PostgreSQL の問題か?
はたまた、統計情報の問題か、PostgreSQL の設定の問題か?
分かりませんが、一応、ご報告します。
robot テーブルの
tags 列にTokenDelimitで
title 列に TokenMecab で、インデックスを作成してあります。
何もしないで検索すると
moovle=# explain ( analyze, buffers ) SELECT c_id, videoid, title, tags
FROM robot WHERE ( lower(tags) &@ lower('iphone') OR lower(title)
&@ lower('iphone') ) AND ( lower(tags) &@ lower('ipad') OR lower(title)
&@ lower('ipad') ) AND vc > 100000 order by vc DESC;
QUERY PLAN
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
-----------------
Sort (cost=952946.92..960247.09 rows=2920069 width=279) (actual time=46145.
433
..46147.329 rows=8713 loops=1)
Sort Key: vc DESC
Sort Method: quicksort Memory: 5879kB
Buffers: shared hit=248495
-> Seq Scan on robot (cost=0.00..417224.88 rows=2920069 width=279)
(actual
time=12.499..46138.911 rows=8713 loops=1)
Filter: ((vc > 100000) AND ((lower(tags) &@ 'iphone'::text) OR
(lower(t
itle) &@ 'iphone'::text)) AND ((lower(tags) &@ 'ipad'::text) OR
(lower(title) &@
'ipad'::text)))
Rows Removed by Filter: 5183037
Buffers: shared hit=248495
Planning time: 0.319 ms
Execution time: 46148.215 ms
(10 行)
とシーケンシャルスキャンします。set enable_seqscan to off; すると
moovle=# explain ( analyze, buffers ) SELECT c_id, videoid, title, tags
FROM robot WHERE ( lower(tags) &@ lower('iphone') OR lower(title)
&@ lower('iphone') ) AND ( lower(tags) &@ lower('ipad') OR lower(title)
&@ lower('ipad') ) AND vc > 100000 order by vc DESC;
QUERY
PLAN
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Sort (cost=954406.96..961707.13 rows=2920069 width=279) (actual
time=271.996..
272.466 rows=2842 loops=1)
Sort Key: vc DESC
Sort Method: quicksort Memory: 1916kB
Buffers: shared hit=14499
-> Bitmap Heap Scan on robot (cost=1460.03..418684.91 rows=2920069
width=27
9) (actual time=44.007..269.844 rows=2842 loops=1)
Recheck Cond: ((lower(tags) &@ 'iphone'::text) OR (lower(title) &@
'iph
one'::text))
Filter: ((vc > 100000) AND ((lower(tags) &@ 'ipad'::text) OR
(lower(tit
le) &@ 'ipad'::text)))
Rows Removed by Filter: 12118
Heap Blocks: exact=14499
Buffers: shared hit=14499
-> BitmapOr (cost=1460.03..1460.03 rows=5191750 width=0) (actual
time
=39.352..39.352 rows=0 loops=1)
-> Bitmap Index Scan on robot2_lower_r_delimmit_tags_idx
(cost=
0.00..0.00 rows=2595875 width=0) (actual time=17.836..17.836 rows=6238
loops=1)
Index Cond: (lower(tags) &@ 'iphone'::text)
-> Bitmap Index Scan on robot2_lower_r_mecab_title_idx
(cost=0.
00..0.00 rows=2595875 width=0) (actual time=21.514..21.514 rows=13623
loops=1)
Index Cond: (lower(title) &@ 'iphone'::text)
Planning time: 0.395 ms
Execution time: 273.268 ms
(17 行)
となります。ちなみに、set enable_seqscan しなくても
moovle=# explain ( analyze, buffers ) SELECT c_id, videoid, title, tags
FROM robot WHERE ( lower(tags) &@ lower('iphone') OR lower(title)
&@ lower('iphone') ) AND ( lower(tags) like lower('%ipad%') OR lower(title)
&@ lower('ipad') ) AND vc > 100000 order by vc DESC;
QUERY P
LAN
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
Sort (cost=689570.20..694475.92 rows=1962287 width=279) (actual
time=116.442..
116.797 rows=2211 loops=1)
Sort Key: vc DESC
Sort Method: quicksort Memory: 1434kB
Buffers: shared hit=3687
-> Bitmap Heap Scan on robot (cost=981.14..335189.94 rows=1962287
width=279
) (actual time=14.289..113.610 rows=2211 loops=1)
Recheck Cond: ((lower(tags) ~~ '%ipad%'::text) OR (lower(title) &@
'ipa
d'::text))
Filter: ((vc > 100000) AND ((lower(tags) &@ 'iphone'::text) OR
(lower(t
itle) &@ 'iphone'::text)))
Rows Removed by Filter: 1501
Heap Blocks: exact=3687
Buffers: shared hit=3687
-> BitmapOr (cost=981.14..981.14 rows=2637409 width=0) (actual
time=1
2.718..12.718 rows=0 loops=1)
-> Bitmap Index Scan on robot2_lower_r_delimmit_tags_idx
(cost=
0.00..0.00 rows=41534 width=0) (actual time=4.426..4.426 rows=933 loops=1)
Index Cond: (lower(tags) ~~ '%ipad%'::text)
-> Bitmap Index Scan on robot2_lower_r_mecab_title_idx
(cost=0.
00..0.00 rows=2595875 width=0) (actual time=8.289..8.289 rows=3429 loops=1)
Index Cond: (lower(title) &@ 'ipad'::text)
Planning time: 0.390 ms
Execution time: 118.029 ms
(17 行)
です。現在の解決策は、アプリケーションの中で、私はPHPなので
$result = pg_query( &db_link, ‘set enable_seqscan to off’ );
$result = pg_query( &db_link, 検索 );
$result2 = pg_query( &db_link, ‘set enable_seqscan to on );
としました。
とりあえず、ご報告まで。
-------------- next part --------------
HTMLの添付ファイルを保管しました...
URL: https://lists.osdn.me/mailman/archives/groonga-dev/attachments/20170915/793c220d/attachment-0001.htm