playground列のあるテーブルがありval、列valにインデックスが付けられています。
範囲のリストがあり[(min1, max1), (min2, max2), ... , (minN, maxN)]、valそれらの範囲のいずれかに適合するすべての行を選択したいと思います。
たとえば、私の範囲は次のようになり[(1,5), (20,25), (200,400)]ます。対応する行を抽出する簡単なクエリは次のとおりです。
select p.*
from playground p
where (val between 1 AND 5) or (val between 20 and 25) or
(val between 200 and 400);
ここでの問題は、この範囲のリストが動的であるということです。私のアプリケーションはそれを生成し、クエリと一緒にpostgresに送信します。
範囲の動的リストを受け入れるようにクエリを書き直そうとしました。
select p.*
from playground p,
unnest(ARRAY [(1, 5),(20, 25),(200, 400)]) as r(min_val INT, max_val INT)
where p.val between r.min_val and r.max_val;
同じ行を抽出しますが、効果的なクエリかどうかわかりませんか？
これは、最初のクエリの説明がどのように見えるかです。
Bitmap Heap Scan on playground p (cost=12.43..16.45 rows=1 width=36) (actual time=0.017..0.018 rows=4 loops=1)
Recheck Cond: (((val >= 1) AND (val <= 5)) OR ((val >= 20) AND (val <= 25)) OR ((val >= 200) AND (val <= 400)))
Heap Blocks: exact=1
-> BitmapOr (cost=12.43..12.43 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
-> Bitmap Index Scan on playground_val_index (cost=0.00..4.14 rows=1 width=0) (actual time=0.010..0.010 rows=3 loops=1)
Index Cond: ((val >= 1) AND (val <= 5))
-> Bitmap Index Scan on playground_val_index (cost=0.00..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((val >= 20) AND (val <= 25))
-> Bitmap Index Scan on playground_val_index (cost=0.00..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Index Cond: ((val >= 200) AND (val <= 400))
Planning Time: 0.071 ms
Execution Time: 0.057 ms
そしてここに2番目の説明があります：
Nested Loop (cost=0.14..12.52 rows=2 width=36) (actual time=0.033..0.065 rows=4 loops=1)
-> Function Scan on unnest r (cost=0.00..0.03 rows=3 width=8) (actual time=0.011..0.012 rows=3 loops=1)
-> Index Scan using playground_val_index on playground p (cost=0.13..4.15 rows=1 width=36) (actual time=0.008..0.015 rows=1 loops=3)
Index Cond: ((val >= r.min_val) AND (val <= r.max_val))
Planning Time: 0.148 ms
Execution Time: 0.714 ms
注：どちらの場合もset enable_seqscan = false;、インデックスを機能させるために行いました。
「ネステッドループ」のステージが気になります。大丈夫ですか？または、範囲の動的リストをクエリに渡すためのより効果的な方法はありますか？私のpostgresバージョンは12.1
あなたはより多くの情報を追加しましたが、まだはるかに関連性があります。正確なテーブルとインデックスの定義、カーディナリティ、データ分布、行サイズの統計、述語の範囲の数、テーブルの目的、書き込みパターンなど。パフォーマンスの最適化には、取得できるすべての入力が必要です。
暗闇での撮影：範囲が重複しない場合、UNION ALLクエリは最高のパフォーマンスを発揮する可能性があります。
SELECT * FROM playground WHERE val BETWEEN 1 AND 5
UNION ALL
SELECT * FROM playground WHERE val BETWEEN 20 AND 25
UNION ALL
SELECT * FROM playground WHERE val BETWEEN 200 AND 400;
私たちは知っている範囲が重複しないように、それはあなたの試みに余分な作業を行う必要がありますので、Postgresは、しません。このクエリはBitmapOr、最初のNested Loop計画と2番目の計画の両方を回避する必要があります。各範囲をフェッチして、出力に追加するだけです。次のような計画になります。
Append (cost=0.13..24.50 rows=3 width=40)
-> Index Scan using playground_val_idx on playground (cost=0.13..8.15 rows=1 width=40)
Index Cond: ((val >= 1) AND (val <= 5))
-> Index Scan using playground_val_idx on playground playground_1 (cost=0.13..8.15 rows=1 width=40)
Index Cond: ((val >= 20) AND (val <= 25))
-> Index Scan using playground_val_idx on playground playground_2 (cost=0.13..8.15 rows=1 width=40)
Index Cond: ((val >= 200) AND (val <= 400))
さらに、各サブSELECTは、範囲のリストが長い場合でも、一般的な推定値ではなく、特定の範囲の実際の統計に基づいています。参照（推奨！）：
クライアントでクエリを生成するか、サーバー側の関数を記述して動的SQLを生成および実行できます（結果の種類がわかっている場合に適用可能）。
LOOP（多くの場合効率が低下しますが、これは例外である可能性があります）を使用してサーバー側関数をテストすることもできます。
CREATE OR REPLACE FUNCTION foo(_ranges int[])
RETURNS SETOF playground LANGUAGE plpgsql PARALLEL SAFE STABLE AS
$func$
DECLARE
_range int[];
BEGIN
FOREACH _range SLICE 1 IN ARRAY _ranges
LOOP
RETURN QUERY
SELECT * FROM playground WHERE val BETWEEN _range[1] AND _range[2];
END LOOP;
END
$func$;
オーバーヘッドは、通話のいくつかの範囲に対して支払われない場合があります。しかし、他に何もないとしても、呼び出すのは非常に便利です。
SELECT * FROM foo('{{1,5},{20,25},{200,400}}');
関連：
db <>フィドルはこちら
行の物理的な順序が大いに役立つ場合があります。行が順番に格納されている場合、処理する必要のあるデータページは（はるかに）少なくなります。未公開の詳細に依存します。ビルトインCLUSTERまたは拡張機能pg_repackまたはそれpg_squeezeを助けるかもしれません。関連：
また、使用中のメジャーバージョンには、利用可能な最新のマイナーリリースを使用することをお勧めします。これを書いている時点では12.2になります（2020-02-13リリース）。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加