[pgsql-jp: 41996] Re: array_to_json 関数によるSQL実行時間

[toshio_uchiyama ＠ mirror.ocn.ne.jp]

長田さん

　ありがとうございます。サーバーで実行してみたところ

moovle=# explain ( analyze, buffers ) WITH v AS (
 SELECT id, video, track, stime,
 content,
 theme FROM subtitled WHERE content &@~ 'algorithm search'
 ORDER BY vc DESC OFFSET 0 LIMIT 70)
SELECT id, video, track, stime, array_to_json( content ) as c, theme from v;

  QUERY PLAN

----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
---------------
 CTE Scan on v  (cost=37767.73..37769.31 rows=70 width=180)
                (actual time=57.670..101.133 rows=70 loops=1)
   Buffers: shared hit=3182
   CTE v
     ->  Limit  (cost=37767.56..37767.73 rows=70 width=1077)
                (actual time=54.907..54.955 rows=70 loops=1)
           Buffers: shared hit=2705
           ->  Sort  (cost=37767.56..37855.40 rows=35137 width=1077)
                     (actual time=54.905..54.935 rows=70 loops=1)
                 Sort Key: subtitled.vc DESC
                 Sort Method: top-N heapsort  Memory: 107kB
                 Buffers: shared hit=2705
                 ->  Index Scan using subtitled2_pgroonga_content_idx on
                     subtitled
                     (cost=0.00..36515.05 rows=35137 width=1077)
                     (actual time=45.510..52.494 rows=2769 loops=1)
                       Index Cond: (content &@~ 'algorithm search'::text)
                       Buffers: shared hit=2705
 Planning time: 1.101 ms
 Execution time: 101.717 ms
(14 行)

という結果になりました。問題なく使えます。
　これができたので、欲が出て、もう一つ質問します。メールを分けます。
よろしくお願いします。

-----Original Message-----
From: Yugo Nagata <nagata ＠ sraoss.co.jp>
Sent: Monday, December 17, 2018 11:07 AM
To: PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
Cc: toshio_uchiyama ＠ mirror.ocn.ne.jp
Subject: Re: [pgsql-jp: 41994] array_to_json 関数によるSQL実行時間

内山さん

長田です。

WITH v (
 SELECT id, video, track, stime,
 content,
 theme FROM subtitled WHERE content &@~ 'algorithm search'
 ORDER BY vc DESC OFFSET 0 LIMIT 70)
SELECT array_to_json( content ) as c from v;

のように、ソート＆LIMIT部分をCTE or サブクエリにして、行数を絞った後で
array_to_json 適用するのではどうでしょうか。

On Sun, 16 Dec 2018 10:44:08 +0900
<toshio_uchiyama ＠ mirror.ocn.ne.jp> wrote:

> JPUG 各位
>
> 　お世話にになります。内山と申します。
> カンファレンスではお世話になりました。
> ところで、SQL 実行結果に array_to_json 関数を
> 適用すると実行時間が、10倍以上になるのですが、
> これを早くする方法はないでしょうか。
> 　実際に、SQL を実行した、explain ( analyze, buffers )
> の結果を掲載します。
>
> 〇array_to_json 関数なし
> moovle=# explain ( analyze, buffers ) SELECT id, video, track, stime,
> content as c, theme FROM subtitled WHERE content &@~ 'algorithm
> search'
> ORDER BY vc DESC OFFSET 0 LIMIT 70;
>
> QU
> ERY PLAN
>
> ----------------------------------------------------------------------
> ------
> ----
> Limit  (cost=37767.56..37767.73 rows=70 width=1077)
> 　　　　(actual time=50.507..50.542 rows=70 loops=1)
>    Buffers: shared hit=2705
>    ->  Sort  (cost=37767.56..37855.40 rows=35137 width=1077)
>              (actual time=50.505..50.528 rows=70 loops=1)
>          Sort Key: vc DESC
>          Sort Method: top-N heapsort  Memory: 107kB
>          Buffers: shared hit=2705
>          ->  Index Scan using subtitled2_pgroonga_content_idx on
> subtitled
> (cost=0.00..36515.05 rows=35137 width=1077) (actual
> time=41.746..48.214 rows=2769 loops=1)
>                Index Cond: (content &@~ 'algorithm search'::text)
>                Buffers: shared hit=2705  Planning time: 1.354 ms
> Execution time: 51.315 ms
> (11 行)
>
> 〇array_to_json 関数あり
> moovle=# explain ( analyze, buffers ) SELECT id, video, track, stime,
> array_to_json( content ) as c, theme FROM subtitled WHERE content &@~
> 'algorithm search'
> ORDER BY vc DESC OFFSET 0 LIMIT 70;
>
> QU
> ERY PLAN
>
> ----------------------------------------------------------------------
> ------
> ----
> Limit  (cost=37855.40..37855.58 rows=70 width=751)
>        (actual time=966.402..966.689 rows=70 loops=1)
>    Buffers: shared hit=21517
>    ->  Sort  (cost=37855.40..37943.24 rows=35137 width=751)
>              (actual time=966.401..966.682 rows=70 loops=1)
>          Sort Key: vc DESC
>          Sort Method: top-N heapsort  Memory: 2996kB
>          Buffers: shared hit=21517
>          ->  Index Scan using subtitled2_pgroonga_content_idx on
> subtitled
> (cost=0.00..36602.89 rows=35137 width=751) (actual
> time=45.692..951.021 rows=2769 loops=1)
>                Index Cond: (content &@~ 'algorithm search'::text)
>                Buffers: shared hit=21517  Planning time: 1.349 ms
> Execution time: 967.509 ms
> (11 行)
>
> よろしくお願いします。
>


--
Yugo Nagata <nagata ＠ sraoss.co.jp>