Clever Geek Handbook

PostgreSQL 13：ネクタイとの幸せなページネーション

先週は、2件の記事を一度に公開された（からヒューバート「depesz」Lubaczewskiとパッチの作者アルバロ・ヘレラオプションのサポートに専念し、）WITH TIESSQLから：2008標準PostgreSQLの13の次期バージョンで実装：

OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
それは何ですか？また、PostgreSQL Antipatterns：Registry Navigationで説明したページング実装の問題をどのように排除しますか？







その記事では、次のような兆候がある場合に停止したことを思い出させてください。 



CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

INSERT INTO events(ts)
SELECT
  now() - ((random() * 1e8) || ' sec')::interval
FROM
  generate_series(1, 1e6);


...次に、時系列のページングを（によってts DESC）整理するには、次のインデックスを使用するのが最も効果的です。 



CREATE INDEX ON events(ts DESC);


...そしてこのクエリモデル： 



SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;


古き良きサブクエリ



今年の初めから次のセグメントを取得したい場合は、そのようなクエリの計画を見てみましょう。 



EXPLAIN (ANALYZE, BUFFERS)
SELECT
  *
FROM
  events
WHERE
  ts < '2020-01-01'::timestamp AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < '2020-01-01'::timestamp
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;




[explain.tensor.ruを見てください]



なぜここにネストされたクエリがあるのですか？要求されたセグメント間でソートキーの同じ値を「ジャンプ」するというその記事で説明されている問題が発生しないようにするためです：







「歯に」ネクタイで試してみる



しかし、これはまさに、境界キーの値が同じであるすべてのレコードを一度WITH TIESに選択するために必要な機能です。 



EXPLAIN (ANALYZE, BUFFERS)
SELECT
  *
FROM
  events
WHERE
  ts < '2020-01-01'::timestamp
ORDER BY
  ts DESC
FETCH FIRST 26 ROWS WITH TIES;




[explain.tensor.ruを見てください]



クエリははるかに単純に見え、ほぼ2倍速く、たった1つでIndex Scan-素晴らしい結果です！



26レコードのみを「注文」したにもかかわらず、「次の」レコードがIndex Scan適切でなくなったことを確認するために、もう1つ抽出したことに注意してください。







さて、明日予定されているPostgreSQL13の公式リリースを待っています。


More articles:


All Articles