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

Question

タイトルの通り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))

Answer 1

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

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

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

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

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

--仮に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();

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

Comments

[zekterra]

また回答して頂きありがとうございます。
子テーブルに分けるというのは考えきれませんでした。
５２テーブルに分けられそうなのでそれで試してみます。

[A.Ichi]

親テーブルへのINSERT 結果行数が 0 になってしまいます（正常に作成されても）にご注意ください。子テーブルは大丈夫

[zekterra]

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

[A.Ichi]

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

[zekterra]

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

Answer 2

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関数を使えば、効果があるかもしれませんね。

Comments

[zekterra]

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

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

[tamako]

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

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

NTileは多分使えないです。

Answer 3

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