90220


# 説明

以下のようにテーブル"stock_bar"があります。  
このテーブルには４０００万行以上のデータが入っています。

## stock_barテーブル定義

[code]

                            Table "public.stock_bar"
       Column    |           Type           | Collation | Nullable | Default 
    -------------+--------------------------+-----------+----------+---------
     timestamp   | timestamp with time zone |           | not null | 
     symbol      | character(8)             |           | not null | 
     time_scale  | character(8)             |           | not null | 
     open        | numeric                  |           | not null | 
     high        | numeric                  |           | not null | 
     low         | numeric                  |           | not null | 
     close       | numeric                  |           | not null | 
     volume      | integer                  |           | not null | 
     trade_count | integer                  |           | not null | 
     vwap        | numeric                  |           | not null | 
    Indexes:
        "stock_bar_pk" PRIMARY KEY, btree ("timestamp", symbol, time_scale)
    
[/code]

ここで、以下のsqlを実行します。

## 実行するSQL

[code]

    select * from stock_bar where symbol = 'SPY' and time_scale = '1Min' and "timestamp" >= '2016/01/01'::timestamp order by "timestamp";
    
[/code]

このSELECT文は成功するのですが、データ量の多さのせいか、実行完了まで12~14秒もかかってしまいます。

## 上記SQLの実行計画

[code]

                                                                             QUERY PLAN                                                                         
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
     Gather Merge  (cost=1046546.40..1181429.25 rows=1156058 width=66)
       Workers Planned: 2
       ->  Sort  (cost=1045546.38..1046991.45 rows=578029 width=66)
             Sort Key: "timestamp"
             ->  Parallel Seq Scan on stock_bar  (cost=0.00..966517.74 rows=578029 width=66)
                   Filter: (("timestamp" >= '2016-01-01 00:00:00'::timestamp without time zone) AND (symbol = 'SPY'::bpchar) AND (time_scale = '1Min'::bpchar))
     JIT:
       Functions: 2
       Options: Inlining true, Optimization true, Expressions true, Deforming true
    (9 rows)
    
[/code]

# 疑問

どうすれば、このsql文の実行時間を短くすることができますか？

  * 有効なインデックスの貼り方？
  * 別のSQL？
  * PostgreSQLを止め、別のDBを使う？
  * 複合主キーを止める？
  * カラム定義を変える？

などの良い解決方法があれば、教えていただけませんか？

※symboやtime_scaleカラムにインデックスを貼ってみたりしたのですが、いまいち時間短縮効果が見られませんでした。

# 実行環境

[code]

    $ psql --version
    psql (PostgreSQL) 14.4 (Ubuntu 14.4-0ubuntu0.22.04.1)
    
    $ lsb_release -a
    No LSB modules are available.
    Distributor ID: Ubuntu
    Description:    Ubuntu 22.04 LTS
    Release:    22.04
    Codename:   jammy
    
[/code]

CPU : i5-8250U CPU @ 1.60GHz  
メモリ : 8G

# 追記

## count(*)の実行結果

### sql

[code]

    select count(*) from stock_bar where symbol = 'SPY' and time_scale = '1Min' and "timestamp" >= '2016/01/01'::timestamp;
    
[/code]

こちらのcount(*)の実行にも12~14秒程度の時間がかかる。

※ count(*)実行のため、はじめのsql文にある"order by"の箇所は省いてあるので注意。

### 上記sqlの実行計画

[code]

                                                                                QUERY PLAN                                                                            
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Finalize Aggregate  (cost=968963.03..968963.04 rows=1 width=8)
       ->  Gather  (cost=968962.81..968963.02 rows=2 width=8)
             Workers Planned: 2
             ->  Partial Aggregate  (cost=967962.81..967962.82 rows=1 width=8)
                   ->  Parallel Seq Scan on stock_bar  (cost=0.00..966517.74 rows=578029 width=0)
                         Filter: (("timestamp" >= '2016-01-01 00:00:00'::timestamp without time zone) AND (symbol = 'SPY'::bpchar) AND (time_scale = '1Min'::bpchar))
     JIT:
       Functions: 6
       Options: Inlining true, Optimization true, Expressions true, Deforming true
    (9 rows)
    
[/code]


CC BY-SA 4.0
2022-07-26T13:44:05.737
90206
2022-07-26T22:51:06.473
2022-07-26T14:59:35.213
9616
9616
question
sql postgresql database
SELECT文の実行時間が長すぎる


>
[code]

>     Filter: (("timestamp" >= '2016-01-01 00:00:00'::timestamp without time
> zone) AND (symbol = 'SPY'::bpchar) AND (time_scale = '1Min'::bpchar))
>  
[/code]

と出ています。元の式は `"timestamp" >= '2016/01/01'::timestamp`
であり、`'2016/01/01'`をタイムゾーンなしの日時と判断し、各行との比較毎にタイムゾーン変換をしているように見受けられます。

まずはクエリを`'2016/01/01'::timestamp with time zone`に修正して確認してください。

それでもまだ`stock_bar_pk`インデックスが使われないかもしれません。というのも

  * 2022/07/27 07:43 JST
  * 2022/07/26 22:43 UTC

は表現は異なりますが同じ時刻を指します。では `'2022/07/27'::timestamp without time
zone`と比較したらどのような結果が得られるか、を考慮するとインデックスが使えない気がします。

この場合はカラム定義を`timestamp without time zone`に変更し、格納する値をUTCに統一することで改善が見込めるかもしれません。


CC BY-SA 4.0
2022-07-26T22:51:06.473
90220
2022-07-26T22:51:06.473
4236
90206
answer