[pgsql-jp: 41997] 【実行時間】関数を用いた ORDER BY
toshio_uchiyama @ mirror.ocn.ne.jp
toshio_uchiyama @ mirror.ocn.ne.jp
2018年 12月 17日 (月) 09:56:00 UTC
JPUG 各位
お世話になります。内山と申します。
ORDER BY 句でのソートに、列を指定した場合と
関数演算を指定した場合で実行時間が、100倍程度
違ってきます。今回は、件数を絞り込めないのですが
何か良い方法があるでしょうか。ちなみに、関数版の
ORDER BY は、algorithm という単語の出現回数です。
explain analyze buffers の結果を引用します。
〇vc 列でソート
moovle=# explain ( analyze, buffers ) WITH v AS
( SELECT id, video, track, stime, content, theme FROM subtitled
WHERE content &@~ 'algorithm' ORDER BY vc DESC OFFSET 0 LIMIT 70 )
select id, video, track, stime, theme, array_to_json( content ) as c from v;
QUERY PLAN
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
---------------
CTE Scan on v (cost=37767.73..37769.31 rows=70 width=180)
(actual time=32.827..41.907 rows=70 loops=1)
Buffers: shared hit=9714
CTE v
-> Limit (cost=37767.56..37767.73 rows=70 width=1077) (actual
time=32.697
..32.713 rows=70 loops=1)
Buffers: shared hit=9361
-> Sort (cost=37767.56..37855.40 rows=35137 width=1077)
(actual time=32.695..32.706 rows=70 loops=1)
Sort Key: subtitled.vc DESC
Sort Method: top-N heapsort Memory: 137kB
Buffers: shared hit=9361
-> Index Scan using subtitled2_pgroonga_content_idx on
subtitled
(cost=0.00..36515.05 rows=35137 width=1077)
(actual time=3.708..28.850 rows=10084 loops=1)
Index Cond: (content &@~ 'algorithm'::text)
Buffers: shared hit=9361
Planning time: 0.353 ms
Execution time: 42.206 ms
(14 行)
〇関数を含む ORDER BY (algorithm という単語の出現回数)
moovle=# explain ( analyze, buffers ) WITH v AS
( SELECT id, video, track, stime, content, theme FROM subtitled
WHERE content &@~ 'algorithm'
ORDER BY ( (char_length( array_to_string( content, '' ) )
- char_length( replace( array_to_string( content, '' ), 'algorithm', '' )) )
/ char_length( 'algorithm' ) ) DESC OFFSET 0 LIMIT 70 )
select id, video, track, stime, theme, array_to_json( content ) as c from v;
QUERY PLAN
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
--------------------------------------
CTE Scan on v (cost=38382.63..38384.21 rows=70 width=180)
(actual time=5447.048..5468.595 rows=70 loops=1)
Buffers: shared hit=121547
CTE v
-> Limit (cost=38382.46..38382.63 rows=70 width=1073)
(actual time=5446.719..5446.744 rows=70 loops=1)
Buffers: shared hit=121052
-> Sort (cost=38382.46..38470.30 rows=35137 width=1073)
(actual time=5446.718..5446.737 rows=70 loops=1)
Sort Key: (((char_length(array_to_string(subtitled.content,
'':
:text)) - char_length(replace(array_to_string(subtitled.content, ''::text),
'
algorithm'::text, ''::text))) / 9)) DESC
Sort Method: top-N heapsort Memory: 58kB
Buffers: shared hit=121052
-> Index Scan using subtitled2_pgroonga_content_idx on
subtitled
(cost=0.00..37129.95 rows=35137 width=1073)
(actual time=7.956..5439.700 rows=10084 loops=1)
Index Cond: (content &@~ 'algorithm'::text)
Buffers: shared hit=121049
Planning time: 0.751 ms
Execution time: 5468.803 ms
(14 行)
PostgreSQL 9.6 です。PostgreSQL 10 とは少し違う実行計画かと思います。
10でも、100倍程度かかりました。10だと、実行計画が Index Scan のあとの
Bitmap Heap Scan の Recheck で時間がかかっていました。
良い知恵があったら、ご教示のほどよろしくお願いします。
pgsql-jp メーリングリストの案内