質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.92%

postgresのデータ取得時間を短縮したい

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 3,324

zekterra

score 14

タイトルの通りpostgresからデータを取得する時間を短縮したいです。
以前にも質問させていただいたのですが、前回とは少し内容が変わっております。

テーブルには2700万件ほどデータが格納されております。

日付の範囲を指定して取得していたデータの中から100件以下のデータを取得して画面に表示させるということをやっております。

以前は指定された範囲内のデータを全て取得してプログラム内で間引いていたのですが、SQL内で間引くようにしました。
プログラム内で間引いていた時よりだいぶ速度は改善されたのですが、検索範囲を1年で検索した際は7秒近くかかっている状況になります。
それを3秒以内に抑えたいです。

vacuum、reindexは行っております。
テーブルの分割については色々事情があってなるべくしたくない方法です。
DBの設定、OS、ハードなどについては、すでに動いているシステムなので変更することが出来ない状況です。

なるべくSQLの見直し、テーブルの設計だけで速度を改善させたいです。

postgresのバージョンは9.5になります。
以下にテーブルの構成、SQL文、実行計画を載せます。

申し訳ございませんがどなたかアドバイスを頂ければと思います。

注)テーブルにrow_numの項目を追加してサブクエリを実行しないSQLでも行ったのですが、あまり変化はありませんでした。

※テーブル構成  テーブル名=「details_table」 
response_time  timestamp 
AA_id           varchar 
port            varchar 
octets          numeric

※SQL 
SELECT
response_time
, octets_diff 
FROM

SELECT
total.response_time
, total.octets
, row_number() over (ORDER BY response_time DESC) AS no 
FROM
details_table total 
WHERE
AA_id = '0000-0000-0000-0840' 
AND port = '2110' 
AND response_time BETWEEN '2006/10/20 11:27:05' AND '2008/10/20 11:27:05'
) AS totaltable 
WHERE
mod(no, 960) = 1  ←検索範囲によって960という値を変化させてデータを100件以下取得する
ORDER BY
totaltable.response_time DESC
, no DESC

※実行計画 
Sort  (cost=193984.85..193984.90 rows=22 width=24)  Sort Key: totaltable.response_time, totaltable.no
+-Subquery Scan on totaltable  (cost=193844.45..193984.36 rows=22 width=24)  Filter: (mod(totaltable.no, 960::bigint) = 1)
+-WindowAgg  (cost=193844.45..193919.78 rows=4305 width=16)
+-Sort  (cost=193844.45..193855.21 rows=4305 width=16)  Sort Key: total.response_time
+-Seq Scan on details_table total  (cost=0.00..193584.60 rows=4305 width=16)  Filter: ((response_time >= '2006-10-20 11:27:05+09'::timestamp with time zone) AND (response_time <= '2008-10-20 11:27:05+09'::timestamp with time zone) AND ((AA_id)::text = '0000-0000-0000-0840'::text) AND ((port)::text = '2110'::text))

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+2

分割は否と言われていますが、可能で有ればパーティショニングをご検討されてはいかがでしょうか?

Postgresのパーティショニングは親テーブルと子テーブルの構成で親テーブルはデータを持たず、
親データ項目を継承した子テーブルがデータを保持します。

INSERT、UPDATE、DELETE、SELECTは親テーブルに対して行えます。当然、分割を単位で直接子テーブル
に同様なアクセスができます。
親テーブルへのアクセスは分割情報元に検索等の実行を子テーブルへと振り分けます。
パーティショニングも数は、100以下であれば親テーブルからの検索でも速度向上が望めます。
私事ですが現行DBはこの100を優に越えています、なので親検索は行わす子テーブルに直接アクセスしております。

例えばですが、テーブル項目AA_id、port、response_time の中で仮にportの分割が100以下になるとした場合、
親テーブル(details_table)と子テーブル(details_table_2110...)として作成する様にできます。(仮にですよ)
今回は、特に検索速度の向上なので1検索がパーティションを跨って行われる場合や分割キー以外での検索と
なら無い様なパーティショニングを見つけ出す事が必要です。言い換えるとそうでなければ検討はそこまでです。
運用者大変でしょうが、がんばってください。

ご参考になればと思い作成してみました。

--仮にportで分割するとしました、他の項目であれば置換えしてください。(もしかしてAA_id)
CREATE TABLE details_table (
response_time  timestamp,
AA_id           varchar,
port            varchar,
octets          numeric);

-- 2110, 2210, 2310, 2410 の4分割の場合
-- INHERITSで親を継承してインデックスを作成しています。必要に応じて追加してください。response_time等
-- CHECKを付けて分割の範囲を指定します。親テーブルからの検索使います。
CREATE TABLE IF NOT EXISTS details_table_2110 (
 LIKE details_table INCLUDING INDEXES
 INCLUDING DEFAULTS
 INCLUDING CONSTRAINTS,
 CHECK ( port = '2110' )
 ) INHERITS (details_table);
CREATE INDEX details_table_2110_idx ON details_table_2110 (port);

CREATE TABLE IF NOT EXISTS details_table_2210 (
 LIKE details_table INCLUDING INDEXES
 INCLUDING DEFAULTS
 INCLUDING CONSTRAINTS,
 CHECK ( port = '2210' )
 ) INHERITS (details_table);
CREATE INDEX details_table_2210_idx ON details_table_2210 (port);

CREATE TABLE IF NOT EXISTS details_table_2310 (
 LIKE details_table INCLUDING INDEXES
 INCLUDING DEFAULTS
 INCLUDING CONSTRAINTS,
 CHECK ( port = '2310' )
 ) INHERITS (details_table);
CREATE INDEX details_table_2310_idx ON details_table_2310 (port);

CREATE TABLE IF NOT EXISTS details_table_2410 (
 LIKE details_table INCLUDING INDEXES
 INCLUDING DEFAULTS
 INCLUDING CONSTRAINTS,
 CHECK ( port = '2410' )
 ) INHERITS (details_table);
CREATE INDEX details_table_2410_idx ON details_table_2410 (port);

-- 親テーブルのトリガーを作成します。
-- 親へのインサート命令を子テーブルへのインサートに置換えます。
-- RETURN NULLにより親テーブルへのインサートを中止します。子テーブルへインサート
CREATE OR REPLACE FUNCTION details_table_trigger() RETURNS TRIGGER AS
$$
  BEGIN
        -- key is port
        EXECUTE 'INSERT INTO ' || 'details_table_' || new.port || ' VALUES(($1).*)' USING new;
        RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

-- 上記のトリガーを親テーブルへ付けます。
-- BEFORE INSERTとして親テーブルへのインサート前に呼び出し、NULLにてスキップします。
DROP TRIGGER IF EXISTS details_table_trigger ON details_table;
CREATE TRIGGER details_table_trg
  BEFORE INSERT
  ON details_table
  FOR EACH ROW
  EXECUTE PROCEDURE details_table_trigger();

/*
 データの作成は、子テーブルへ直接行う方が早いです。
 その際は子テーブル毎にインサートしてください。
  今回大量なので分割子テーブルへ直接挿入する方がよろしいかと思います。
*/

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/24 11:59

    わざわざSQLまで貼って頂きありがとうございます!
    今はメモリDBの使用を検討しているのですが、メモリDBも容量の都合上で不安があり無理そうだなと思っている状態です。
    メモリDBでも出来ない場合はその方法も検討してみます!

    キャンセル

  • 2016/10/24 12:07

    自分の言っている事が正しいかの検証も兼ねていますので、誤りが有りますればご指摘ください。

    キャンセル

  • 2016/10/26 11:06

    お世話になります。
    実は、前々日にSQLを見直すことによって改善されることが判明しました。
    方法としては、
    1.カラムにシーケンス番号を追加する
    2.検索範囲内の始めのシーケンス番号と終わりのシーケンス番号を取得する
    3.2つのシーケンス番号の差からだいたい等間隔に100件取得できるようなシーケンス番号のリストを作成する(java内の処理)
    4.シーケンス番号のリストをwhere文の中に記述する
       例:where seq = 10 or seq = 20 or seq = 30
    以上の方法でどうにか速度の改善をすることが出来ました。
    分割するSQLも書いていただいたのですが検証する前に実装できてしまいました。
    前回の質問に引き続き回答して下さり、ありがとうございました。

    キャンセル

+1

>ORDER BY totaltable.response_time DESC, no DESC 

無駄なソートキーがあります。
ORDER BY no DESC
でいいかと

100件しか取らないなら
ORDER BY no DESC FETCH FIRST 100 ROWS ONLY
とすれば、効果があるかも

さらには、
Row_Numberとmodを組み合わせるのでなく、
NTileというWindow関数を使えば、効果があるかもしれませんね。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/21 18:33

    回答ありがとうございます。
    NTileとFETCH FIRSTについては初めて見る関数です。
    無駄なソートキーのご指摘ありがとうございます。

    指摘された点を改善してみます。

    キャンセル

  • 2016/10/21 18:51

    NTileの仕様を勘違いしてました。

    mod(Row_Number() over(order by ソートキー),割る数)

    NTile(割る数) over(order by ソートキー)
    は、結果というか値の分布がだいぶ違いますね。

    NTileは多分使えないです。

    キャンセル

check解決した方法

0

1.カラムにシーケンス番号を追加する
2.検索範囲内の始めのシーケンス番号と終わりのシーケンス番号を取得する
3.2つのシーケンス番号の差からだいたい等間隔に100件取得できるようなシーケンス番号のリストを作成する(java内の処理)
4.シーケンス番号のリストをwhere文の中に記述する
例:where seq = 10 or seq = 20 or seq = 30
以上の方法でどうにか速度の改善をすることが出来ました。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.92%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る