YugabyteDBとPostgreSQLにおける効率的なペグネーション📃🐘🚀

ページネーションで問い合わせをする場合は、offsetを使用しないでください.正しい方法は、Markus Windandによって説明されますUse The Index, Luke . しかし、最高のインデックスパターンは何ですかPostgreSQL and YugabyteDB ?
TLドクター

検索キーの後に順番に使用される列を含むユニークなインデックスを作成します.また、一意でない場合は、表の主キーから列を作成します

最後の行を識別するために、すべての列を返すクエリを使用します

WHERE句の列を使用して、最後の行の直後に範囲スキャンを開始します

次のページのみを取得する行だけを読み込むように、実行計画を常にチェックします

愛は、データ処理論理を維持して、維持するのが簡単なストアドプロシージャを保存します

yugabytedbまたはpostgresqlはインフラストラクチャの選択であり、アプリケーションコードを自由に保つことができる

テストケース

私は、このソリューションが分散SQLデータベースで効率的であることを確認するために、yugabytedb上でこれを実行しています.PostgreSQLで動作し、違いがあれば説明します.私はここでyugabytedb 2.8を使いますWARNING: 'analyze' is a beta feature! が期待されている).
結合はページ化されたクエリーに関与するかもしれません.ここに、1つのファクトテーブルと寸法表があります.

drop table demo1;
drop table demo2;
create extension pgcrypto;

create table demo2(
 id int primary key
, name text);

insert into demo2 select generate_series(0,1000),'x';

create table demo1(
 id uuid default gen_random_uuid()
, key int
, ts timestamptz not null
, val int
, ref int not null references demo2
);

insert into demo1(key, ts, val, ref)
select 1,timestamp'2022-01-01 00:00:00'+mod(n,500)*interval '1 minute',n,mod(n,10) from generate_series(1,1000000) n
;

analyze demo1;
analyze demo2;

私は多くのデータを追加していない.私は実行計画をチェックするので、私はそれを必要としません.これは効率的なクエリを書く正しい方法です.あなたがそれを縮めたいならば、generateHeight Services ()で範囲を増やしてください、そして、応答時間がページ化サイズだけに依存することを確認してください.

インデックス

目標は、“DEMO 1”からの行を取得するには、“DEMO 2”に接続され、1つの“キー”のために、最も最近の“TS”で注文した.

create unique index demo1_key_ts_id
 on demo1(key, ts desc, id desc);

タイムスタンプが一意でないかもしれないので、私は「id」を加えました、そして、私はユニークなインデックスとしてそれをつくることができます.これは正確な次のページを求めるためにページ化に重要です.

絹篩で篩うたよう。。。どこ。…で注文する.限界

簡単にするために関数を使用します.

create or replace function read_by_page(int,timestamptz,uuid,int) 
returns table(id uuid, key int, ts timestamptz, val int, ref int, name text)
as $$
select demo1.*,demo2.name
from demo2 join demo1 on demo1.ref=demo2.id
where demo1.key=$1 
 and ( 
 -- for the first page, pass null values
 ($2 is null and $3 is null) 
 -- for the next page, pass last values fetched
 or (demo1.ts,demo1.id)<($2,$3)
) 
order by demo1.ts desc,demo1.id desc
limit $4;
$$ language sql;

実行は本当に簡単です.これはid = 1の最初のページを取得します.

select * from read_by_page(1,null,null,4);

結果:

yugabyte=> select * from read_by_page(1,null,null,4);

                   id                  | key |           ts           |  val   | ref | name
--------------------------------------+-----+------------------------+--------+-----+------
 fffc4d86-cef6-4caa-9dd9-6dc47866d7aa |   1 | 2022-01-01 08:19:00+00 | 559999 |   9 | x
 ffb4fde0-0cba-4e52-b011-385b0821160d |   1 | 2022-01-01 08:19:00+00 | 130499 |   9 | x
 ff866c28-4105-4557-b227-feb9e6f2cc48 |   1 | 2022-01-01 08:19:00+00 | 633499 |   9 | x
 ff7ff77d-6e42-48fe-8691-8b0d8f6b8dac |   1 | 2022-01-01 08:19:00+00 | 669999 |   9 | x
(4 rows)

次のページを呼び出します.

select * from read_by_page(1,'2022-01-01 08:19:00+00','ff7ff77d-6e42-48fe-8691-8b0d8f6b8dac',4);

これは次の行を与える:

yugabyte=> select * from read_by_page(1,'2022-01-01 08:19:00+00','ff7ff77d-6e42-48fe-8691-8b0d8f6b8dac',4);

                  id                  | key |           ts           |  val   | ref | name
--------------------------------------+-----+------------------------+--------+-----+------
 ff05778d-b494-4fab-b144-0e90376974ab |   1 | 2022-01-01 08:19:00+00 | 688999 |   9 | x
 fef0ef1c-9e89-4ba1-ac6f-1791f4c58a96 |   1 | 2022-01-01 08:19:00+00 | 170499 |   9 | x
 fec4b1f8-c0e4-48bb-8738-6adc64348376 |   1 | 2022-01-01 08:19:00+00 | 147499 |   9 | x
 febd8f05-dde5-4575-8dc2-d230e390fbc1 |   1 | 2022-01-01 08:19:00+00 | 971999 |   9 | x
(4 rows)

これは高速(ミリ秒単位).しかし、最新のページに移動したい場合は、この100万行のテーブルに数秒かかることがあります.

yugabyte=> explain analyze select * from read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Function Scan on read_by_page  (cost=0.25..10.25 rows=1000 width=68) (actual time=21418.485..21418.485 rows=1 loops=1)
 Planning Time: 0.031 ms
 Execution Time: 21418.510 ms
(3 rows)

すべての前の行がフェッチされなければならなかったので、おそらくこの21秒は最後の行だけを得るために.しかし、機能上の実行計画は、あまり話さない

プリペアドステートメント

私は準備された声明を使うのを好むでしょう($2 is null and $3 is null) 一般的な計画は良いはずがない.私は、問題を示して、実行計画を見るためにそれを示しています.しかし、適切な解決策は、関数呼び出しで上記のものです.より良いそれを分析するたびに、カスタムプランを取得します.
以下に関数と同じコードを用意します.

deallocate all;
prepare read_by_page(int,timestamptz,uuid,int) as
select demo1.*,demo2.name
from demo2 join demo1 on demo1.ref=demo2.id
where demo1.key=$1 
 and ( 
 -- for the first page, pass null values
 ($2 is null and $3 is null) 
 -- for the next page, pass last values fetched
 or (demo1.ts,demo1.id)<($2,$3)
) 
order by demo1.ts desc,demo1.id desc
limit $4;

明らかに同じ結果を得た.

yugabyte=> execute read_by_page(1,null,null,4);

                  id                  | key |           ts           |  val   | ref | name
--------------------------------------+-----+------------------------+--------+-----+------
 fffc4d86-cef6-4caa-9dd9-6dc47866d7aa |   1 | 2022-01-01 08:19:00+00 | 559999 |   9 | x
 ffb4fde0-0cba-4e52-b011-385b0821160d |   1 | 2022-01-01 08:19:00+00 | 130499 |   9 | x
 ff866c28-4105-4557-b227-feb9e6f2cc48 |   1 | 2022-01-01 08:19:00+00 | 633499 |   9 | x
 ff7ff77d-6e42-48fe-8691-8b0d8f6b8dac |   1 | 2022-01-01 08:19:00+00 | 669999 |   9 | x
(4 rows)

yugabyte=> execute read_by_page(1,'2022-01-01 08:19:00+00','ff7ff77d-6e42-48fe-8691-8b0d8f6b8dac',4);

                  id                  | key |           ts           |  val   | ref | name
--------------------------------------+-----+------------------------+--------+-----+------
 ff05778d-b494-4fab-b144-0e90376974ab |   1 | 2022-01-01 08:19:00+00 | 688999 |   9 | x
 fef0ef1c-9e89-4ba1-ac6f-1791f4c58a96 |   1 | 2022-01-01 08:19:00+00 | 170499 |   9 | x
 fec4b1f8-c0e4-48bb-8738-6adc64348376 |   1 | 2022-01-01 08:19:00+00 | 147499 |   9 | x
 febd8f05-dde5-4575-8dc2-d230e390fbc1 |   1 | 2022-01-01 08:19:00+00 | 971999 |   9 | x
(4 rows)

そして、最後のページの同じ時間.しかし、現在実行についてのより多くの洞察で.

分析する

パフォーマンスの複雑さを理解するために、実行計画を見ますEXPLAIN 私はインデックスの範囲スキャンをしていることを確認するにはANALYZE 正確に私が必要とする列の数を読んでください.
私は、カスタムプランが使用されることを気にします.そうでない場合、私は再配置し、ステートメントを再準備します.現在のyugabytedbのバージョンはPostgreSQL 11.2互換性がありません.plan_cache_mode パラメータ

yugabyte=> explain analyze execute read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);
                                                                           QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
-------------------
 Limit  (cost=0.00..237.42 rows=1000 width=68) (actual time=26446.024..26748.760 rows=1000 loops=1)
   ->  Nested Loop  (cost=0.00..23753.30 rows=100046 width=68) (actual time=26446.022..26748.240 rows=1000 loops=1)
         ->  Index Scan using demo1_key_ts_id on demo1  (cost=0.00..12759.84 rows=100046 width=36) (actual time=26444.459..26447.198 rows=1000
 loops=1)
               Index Cond: ((key = 1) AND (ROW(ts, id) < ROW('2022-01-01 00:00:01+00'::timestamp with time zone, '000fffff-ffff-ffff-ffff-ffff
ffffffff'::uuid)))
               Rows Removed by Index Recheck: 998000
         ->  Index Scan using demo2_pkey on demo2  (cost=0.00..0.11 rows=1 width=36) (actual time=0.285..0.285 rows=1 loops=1000)
               Index Cond: (id = demo1.ref)
 Planning Time: 12.457 ms
 Execution Time: 26751.767 ms
(9 rows)

The Rows Removed by Index Recheck 問題がどこにあるか私に話してください.それは、私が私が必要としたものより多くの列を読んだことを意味します.オフセットと同じです.4のオフセットで、これはまだ速いです、しかし、ここで最後のページのために、998000は後で捨てられるために読まれました.Rows Removed by Index Recheck それらの行が捨てられる前にノードの間で出荷されなければならないので、yugabytedbのような分散SQLデータベースで避けることです.
私は、賢い比較でスマートになりたかったです(demo1.ts,demo1.id)<($2,$3) . 前にNULLをテストする必要があるので、すでにスマートではありませんでした.正しいインデックスの使用法では、カラムの比較を行う必要があります(DEMO1.TS<=2 and (DEMO1.TS <$2 or (DEMO1.TS =$2 and DEMO1.ID <$3)))

deallocate all;
prepare read_by_page(int,timestamptz,uuid,int) as
select demo1.*,demo2.name
from demo2 join demo1 on demo1.ref=demo2.id
where demo1.key=$1 
 and ( 
 -- for the first page, pass null values
 ($2 is null and $3 is null) 
 -- for the next page, pass last values fetched
 or ((DEMO1.TS<=$2 and (DEMO1.TS <$2 or (DEMO1.TS =$2 and DEMO1.ID <$3))))
) 
order by demo1.ts desc,demo1.id desc
limit $4;

次のページをもう一度変更しようとします.

yugabyte=# explain analyze execute read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);

                                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..249.92 rows=1000 width=68) (actual time=18.769..305.666 rows=1000 loops=1)
   ->  Nested Loop  (cost=0.00..25069.13 rows=100307 width=68) (actual time=18.768..305.425 rows=1000 loops=1)
         ->  Index Scan using demo1_key_ts_id on demo1  (cost=0.00..14046.96 rows=100307 width=36) (actual time=18.121..19.741 rows=1000 loops=1)
               Index Cond: ((key = 1) AND (ts <= '2022-01-01 00:00:01+00'::timestamp with time zone))
               Filter: ((ts < '2022-01-01 00:00:01+00'::timestamp with time zone) OR ((ts = '2022-01-01 00:00:01+00'::timestamp with time zone) AND (id < '000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
         ->  Index Scan using demo2_pkey on demo2  (cost=0.00..0.11 rows=1 width=36) (actual time=0.275..0.275 rows=1 loops=1000)
               Index Cond: (id = demo1.ref)
 Planning Time: 6.833 ms
 Execution Time: 309.985 ms
(9 rows)

今、計画はまだ正しいタイムスタンプを求めていますRows Removed by Index Recheck . これは、ストレージ層での読み取り時にすべての行をフィルタリングし、直接右側のポイントを求めることを意味します.
この例では、プリペアドステートメントを何度も実行することができますし、カスタムプランを使用してもジェネリックなものに切り替えることはできません.しかし、私はそれに頼りません.より良い機能をそこで使用します.

関数の再試行

ここで私がこのケースで使用する関数は、コードがジェネリックであり、計画がカスタムであること、およびUsegabyteDBに完全にプッシュされていることを確認した状態です.

create or replace function read_by_page(int,timestamptz,uuid,int) 
returns table(id uuid, key int, ts timestamptz, val int, ref int, name text)
as $$
select demo1.*,demo2.name
from demo2 join demo1 on demo1.ref=demo2.id
where demo1.key=$1 
 and ( 
 -- for the first page, pass null values
 ($2 is null and $3 is null) 
 -- for the next page, pass last values fetched
 or (DEMO1.TS<=$2 and (DEMO1.TS <$2 or (DEMO1.TS =$2 and DEMO1.ID <$3)))
) 
order by demo1.ts desc,demo1.id desc
limit $4;
$$ language sql;

私は、これが速いと思っていましたが、

yugabyte=> explain analyze select * from read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Function Scan on read_by_page  (cost=0.25..10.25 rows=1000 width=68) (actual time=21657.987..21657.988 rows=1 loops=1)
 Planning Time: 0.028 ms
 Execution Time: 21658.008 ms
(3 rows)

私の推測では、それは一般的な計画を使用しています.次に、関数を使用したい場合は、最初と次のページの2つのクエリをplpgsql関数で定義します.

create or replace function read_by_page(p_key int, p_ts timestamptz, p_id uuid, p_rows int) 
returns table(id uuid, key int, ts timestamptz, val int, ref int, name text)
as $$ begin
if (p_id is null and p_ts is null) then
 -- for the first page, pass null values
return query
 select demo1.*,demo2.name
  from demo2 join demo1 on demo1.ref=demo2.id
  where demo1.key=p_key
  order by demo1.ts desc,demo1.id desc
  limit p_rows;
else
 -- for the next page, pass last values fetched
return query
 select demo1.*,demo2.name
  from demo2 join demo1 on demo1.ref=demo2.id
  where demo1.key=p_key
   and (DEMO1.TS<=$2 and (DEMO1.TS <$2 or (DEMO1.TS =$2 and DEMO1.ID <$3)))
  order by demo1.ts desc,demo1.id desc
  limit p_rows;
end if;
end; $$ language plpgsql;

これにより、最後のページも速くなります.

yugabyte=# select * from read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);

                  id                  | key |           ts           |  val  | ref | name
--------------------------------------+-----+------------------------+-------+-----+------
 00015f08-b5b1-460b-97e1-7995b9a8f1f8 |   1 | 2022-01-01 00:00:00+00 | 78000 |   0 | xy
(1 row)

yugabyte=#  explain analyze select * from read_by_page(1,'2022-01-01 00:00:01+00','000fffff-ffff-ffff-ffff-ffffffffffff',1000);
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Function Scan on read_by_page  (cost=0.25..10.25 rows=1000 width=68) (actual time=9.291..9.291 rows=1 loops=1)
 Planning Time: 0.027 ms
 Execution Time: 9.318 ms

もちろん、カスタムクエリを構築することもできます.

select demo1.*,demo2.name
from demo2 join demo1 on demo1.ref=demo2.id
where demo1.key= 1 
  and (
    DEMO1.TS <= '2022-01-01 00:00:01+00'
    and (
      DEMO1.TS < '2022-01-01 00:00:01+00'
      or (
        DEMO1.TS = '2022-01-01 00:00:01+00'
        and DEMO1.ID < 'ffffffff-ffff-ffff-ffff-ffffffffffff'
      )
    )
  )
order by demo1.ts desc,demo1.id desc
limit 42;

あなたが変換する闘争(demo1.ts,demo1.id) <= ('2022-01-01 00:00:01+00','ffffffff-ffff-ffff-ffff-ffffffffffff') 優れたJooq翻訳ツールを使用できます.https://www.jooq.org/translate/ “MS Access”、“SQL Server”または“Oracle”のような行ごとの比較をサポートしていないターゲットで.
最近のPostgreSQLバージョンでは、ジェネリックプリペアドステートメントを使用することができますplan_cache_mode=force_custom_plan .
YugabyteDBのために、私は開きましたissue 11794 行の比較で同じパフォーマンスを得るには、回避策は簡単です.
すべてのケースでは、私はPLPGSQL関数が好きです:これはビジネスロジックではなく、純粋にデータ処理をテーブル定義に関連付けられています.