PostgreSQL 13: selamat pagination DENGAN TIES

Minggu lalu, dua artikel diterbitkan sekaligus (dari Hubert 'depesz' Lubaczewski dan penulis tambalan Alvaro Herrera ), yang didedikasikan untuk mendukung opsi WITH TIESdari standar SQL: 2008 yang diterapkan dalam versi PostgreSQL 13 yang akan datang :

OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

Apa itu, dan bagaimana cara menghilangkan masalah paging yang saya diskusikan di PostgreSQL Antipatterns: Registry Navigation ?







Izinkan saya mengingatkan Anda bahwa di artikel itu kami berhenti pada titik bahwa jika kami memiliki tanda seperti ini:

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);

... maka untuk mengatur halaman kronologis dengan itu (dengan ts DESC) paling efektif menggunakan indeks berikut:

CREATE INDEX ON events(ts DESC);

... dan model kueri ini:

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;

Subkueri tua yang bagus

Mari kita lihat rencana query seperti itu jika kita ingin mendapatkan segmen berikutnya dari awal tahun ini:

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;

[lihat menjelaskan.tensor.ru]



Mengapa ada kueri bersarang di sini? Persis agar tidak mengalami masalah yang dijelaskan dalam artikel tersebut dengan "melompati" nilai yang sama dari kunci pengurutan di antara segmen yang diminta:

Mencoba DENGAN DASI "ke gigi"

Tetapi fungsi WITH TIESinilah yang dibutuhkan - untuk memilih semua record dengan nilai kunci batas yang sama sekaligus !

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

[lihat menjelaskan.tensor.ru]



Kueri terlihat jauh lebih sederhana, hampir 2 kali lebih cepat, dan hanya dalam satu Index Scan- hasil yang luar biasa!



Harap perhatikan bahwa meskipun kami "memesan" hanya 26 rekaman, saya Index Scanmengekstrak satu lagi - hanya untuk memastikan bahwa yang "berikutnya" tidak lagi cocok untuk kami.







Nah, kita tunggu rilis resmi PostgreSQL 13 yang dijadwalkan besok.