PostgreSQL範囲フィルタ+その他のフィールドソートOFFSET LIMIT(マルチフィールド区間フィルタ)の最適化と加速

9612 ワード

タブ
PostgreSQL、範囲フィルタリング、その他のフィールドソート、インデックス、offset、limit
背景
インデックススキャンでは、両方のフィールドスキャンが区間スキャンである場合、あるフィールドのフィルタ条件のみが使用され、別のフィールドはフルスキャンが必要です.
たとえば

create table t(id int, c1 int, c2 int);  
  
insert into t select generate_series(1,6000000), random()*10000, random()*10000;  
  
create index idx_t_1 on t(c1, c2);  
  
explain (analyze,verbose,timing,costs,buffers) select * from t where c1 between 1 and 10000 order by c2 limit 1;  
  
                                                                   QUERY PLAN                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=25496.76..25496.76 rows=1 width=12) (actual time=5330.577..5330.578 rows=1 loops=1)  
   Output: id, c1, c2  
   Buffers: shared hit=6021360  
   ->  Sort  (cost=25496.76..25571.75 rows=29997 width=12) (actual time=5330.576..5330.576 rows=1 loops=1)  
         Output: id, c1, c2  
         Sort Key: t.c2  
         Sort Method: top-N heapsort  Memory: 25kB  
         Buffers: shared hit=6021360  
         ->  Index Scan using idx_t_1 on public.t  (cost=0.43..25346.77 rows=29997 width=12) (actual time=0.032..4526.864 rows=5999724 loops=1)  
               Output: id, c1, c2  
               Index Cond: ((t.c1 >= 1) AND (t.c1 <= 10000))  
               Buffers: shared hit=6021360  
 Planning time: 0.100 ms  
 Execution time: 5330.734 ms  
(14 rows)  
  
explain (analyze,verbose,timing,costs,buffers) select * from t where c1 between 1 and 10000 and c2 between 100000 and 1111110 limit 1;  
                                                           QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.43..5.52 rows=1 width=12) (actual time=248.439..248.439 rows=0 loops=1)  
   Output: id, c1, c2  
   Buffers: shared hit=21828  
   ->  Index Scan using idx_t_1 on public.t  (cost=0.43..763.89 rows=150 width=12) (actual time=248.437..248.437 rows=0 loops=1)  
         Output: id, c1, c2  
         Index Cond: ((t.c1 >= 1) AND (t.c1 <= 10000) AND (t.c2 >= 100000) AND (t.c2 <= 1111110))  
         Buffers: shared hit=21828  
 Planning time: 0.120 ms  
 Execution time: 248.578 ms  
(9 rows)

なぜなら、複合インデックスでは、各複合KEYは秩序化されているが、マルチレベルでは、各層は秩序化されていないからである.例:
では、前述の例SQL 2は、実際にはレイヤ1がインデックスフィルタリングを用いたものであり、レイヤ2はフィルタリングを必要とする.
例SQL 2は、実際にはレイヤ1がインデックスフィルタを用い、条件を満たすすべての行(インデックス外のフィールドが用いられているためINDEX ONLY SCANは使用できない)を取り出し、レイヤ2順に並べ替えるものである.
インデックス構造は次のように参照できます.
『PostgreSQL B-Treeインデックス構造の深入浅出』
では、このようなSQLをどのように最適化しますか?
最適化とシーン
次のようなビジネスが含まれています.
1、注文の支払い時間
2、注文番号
3、一意キー=受注支払時間+受注番号(1つの時点で複数の受注がある可能性があるため)
業務はある支払時間より大きいものを取り出し、一意キーで並べ替え、N本オフセットした後、1本取る必要がある.
DEMO表は以下の通り.

create table test(id int, c1 text, c2 timestamp);

6000万件のオーダーを書き込み

insert into test select id, c1::text||id, c1 from (select generate_series(1,10000000) id, clock_timestamp()::timestamp(1) c1) t;

ポスト・チューニングで使用するインデックスの作成

create index idx1 on test (c1);  
create index idx2 on test (c2);  
create index idx3 on test (c3);

クエリ文は次のとおりです.

select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c1 limit 1 offset 50000;  
  
LOG:  duration: 943.851 ms  plan:  
Query Text: select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c1 limit 1 offset 50000;  
Limit  (cost=4714.82..4714.92 rows=1 width=40) (actual time=943.822..943.823 rows=1 loops=1)  
  Output: id, c1, c2  
  Buffers: shared hit=141577  
  ->  Index Scan using idx1 on public.test  (cost=0.56..339427.36 rows=3600000 width=40) (actual time=928.122..939.856 rows=50001 loops=1)  
        Output: id, c1, c2  
        Filter: (test.c2 >= '2018-01-22 16:13:25.4'::timestamp without time zone)  
        Rows Removed by Filter: 6281766  
        Buffers: shared hit=141577  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 944.485 ms

パフォーマンスは理想的ではありません.1秒くらい時間がかかりました.条件1を満たすデータ量が大きいため,完全に取り出して並べ替えてOFFSETする必要がある.
最適化方法
C 1はC 2+注文番号で構成されているため、C 1の最小値は必ずC 2の最小値の区間に現れる.このように最適化することができます

create or replace function get_test1(timestamp, int) returns test as $$  
declare  
  v1 timestamp;  
  v2 text;  
  res text;  
begin  
  set enable_seqscan=off;  
  
  --     C2   ，     C2  
  --         
  select c2 into v1 from test where c2 >= $1 order by c2 limit 1;  
    
  --     C2 ，    C1  
  --         
  select min(c1) into v2 from test where c2 = v1;  
  
  --      C1，      
  --   offset    
  select t into res from test t where c1 >= v2 order by c1 limit 1 offset $2 ;  
  return res::test;  
end;  
$$ language plpgsql strict;

以上の最適化を使用すると、各ステップのオーバーヘッドが最小になります.効果は次のとおりです.

postgres=# \timing  
postgres=# load 'auto_explain';  
LOAD  
postgres=# set auto_explain.log_nested_statements =on;  
SET  
Time: 0.165 ms  
postgres=# set auto_explain.log_analyze =on;  
SET  
Time: 0.168 ms  
postgres=# set auto_explain.log_buffers =on;  
SET  
Time: 0.144 ms  
postgres=# set auto_explain.log_min_duration =0;  
SET  
Time: 0.161 ms  
postgres=# set auto_explain.log_timing =on;  
SET  
Time: 0.147 ms  
postgres=# set auto_explain.log_verbose =on;  
SET  
Time: 0.173 ms  
  
  
postgres=# select * from get_test1('2018-01-22 16:13:25.4'::timestamp, 50000);  
LOG:  duration: 0.045 ms  plan:  
Query Text: select c2         from test where c2 >= $1 order by c2 limit 1  
Limit  (cost=0.43..0.46 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)  
  Output: c2  
  Buffers: shared hit=4  
  ->  Index Only Scan using idx2 on public.test  (cost=0.43..99814.16 rows=3333333 width=8) (actual time=0.041..0.041 rows=1 loops=1)  
        Output: c2  
        Index Cond: (test.c2 >= $1)  
        Heap Fetches: 1  
        Buffers: shared hit=4  
LOG:  duration: 0.024 ms  plan:  
Query Text: select min(c1)         from test where c2 = v1  
Result  (cost=0.96..0.97 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=1)  
  Output: $0  
  Buffers: shared hit=5  
  InitPlan 1 (returns $0)  
    ->  Limit  (cost=0.56..0.96 rows=1 width=28) (actual time=0.020..0.020 rows=1 loops=1)  
          Output: test.c1  
          Buffers: shared hit=5  
          ->  Index Only Scan using idx3 on public.test  (cost=0.56..31750.61 rows=79365 width=28) (actual time=0.020..0.020 rows=1 loops=1)  
                Output: test.c1  
                Index Cond: ((test.c2 = $4) AND (test.c1 IS NOT NULL))  
                Heap Fetches: 1  
                Buffers: shared hit=5  
LOG:  duration: 57.454 ms  plan:  
Query Text: select t          from test t where c1 >= v2 order by c1 limit 1 offset $2  
Limit  (cost=1699.64..1699.67 rows=1 width=92) (actual time=57.451..57.451 rows=1 loops=1)  
  Output: t.*, c1  
  Buffers: shared hit=774  
  ->  Index Scan using idx1 on public.test t  (cost=0.56..125732.85 rows=3700012 width=92) (actual time=0.048..53.365 rows=50001 loops=1)  
        Output: t.*, c1  
        Index Cond: (t.c1 >= '2018-01-22 16:13:25.46364521'::text)  
        Buffers: shared hit=774  
LOG:  duration: 58.163 ms  plan:  
Query Text: select * from get_test1('2018-01-22 16:13:25.4'::timestamp, 50000);  
Function Scan on public.get_test1  (cost=0.25..0.26 rows=1 width=44) (actual time=58.155..58.156 rows=1 loops=1)  
  Output: id, c1, c2  
  Function Call: get_test1('2018-01-22 16:13:25.4'::timestamp without time zone, 50000)  
  Buffers: shared hit=783  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 58.503 ms

パフォーマンスの向上が顕著です.
ビジネスロジックの最適化
実際、事業者はc 2の値(支払い時間)を要求しているので、UK(c 2+注文番号)でソートする必要は全くありません.つまり、SQLはこのように書き換えることができます.

postgres=# select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c2 limit 1 offset 50000;  
LOG:  duration: 14.945 ms  plan:  
Query Text: select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c2 limit 1 offset 50000;  
Limit  (cost=1497.64..1497.67 rows=1 width=40) (actual time=14.932..14.933 rows=1 loops=1)  
  Output: id, c1, c2  
  Buffers: shared hit=608  
  ->  Index Scan using idx2 on public.test  (cost=0.43..107799.04 rows=3600000 width=40) (actual time=0.030..10.541 rows=50001 loops=1)  
        Output: id, c1, c2  
        Index Cond: (test.c2 >= '2018-01-22 16:13:25.4'::timestamp without time zone)  
        Buffers: shared hit=608  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 15.442 ms

ビジネス層からSQLを1本変更し、最高の効果を達成しました.(そして最後にはインデックスが1つだけ必要です.)
別のレベルから見ると、この最適化では、複数のフィールドが範囲のクエリーであり、複合インデックスは最良の選択ではなく、より良い選択は、パーティションテーブル+単一インデックス、またはパーティションインデックスである可能性があります.カーネルの機能強化が必要です.

cygwinにRVMを入れてRuby1.9.3をインストールしてみた

sqlite 3とiosを組み合わせた開発