PostgreSQL 13: Fröhliche Paginierung MIT KRAWATTEN

Letzte Woche wurden zwei Artikel gleichzeitig veröffentlicht (von Hubert 'depesz' Lubaczewski und dem Autor des Patches selbst, Alvaro Herrera ), die sich der Unterstützung der Option WITH TIESaus dem SQL: 2008-Standard widmen, der in der kommenden Version von PostgreSQL 13 implementiert ist :

OFFSET start { ROW | ROWS }

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

Was ist das und wie werden die Paging-Implementierungsprobleme behoben, die ich in PostgreSQL Antipatterns: Registry Navigation besprochen habe ?







Ich möchte Sie daran erinnern, dass wir in diesem Artikel an dem Punkt stehen geblieben sind, an dem wir ein solches Zeichen haben:

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

... um dann das chronologische Paging nach (von ts DESC) zu organisieren , ist es am effektivsten, den folgenden Index zu verwenden:

CREATE INDEX ON events(ts DESC);

... und dieses Abfragemodell:

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;

Gute alte Unterabfrage

Schauen wir uns den Plan für eine solche Abfrage an, wenn wir das nächste Segment ab Anfang dieses Jahres erhalten möchten:

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;

[siehe EXPLAIN.tensor.ru]



Warum gibt es hier eine verschachtelte Abfrage? Genau, um nicht die in diesem Artikel beschriebenen Probleme mit dem "Springen" der gleichen Werte des Sortierschlüssels zwischen den angeforderten Segmenten zu haben:

Versuchen mit Krawatten "bis zu den Zähnen"

Genau dafür ist jedoch die Funktionalität erforderlich WITH TIES- um alle Datensätze mit demselben Wert des Grenzschlüssels gleichzeitig auszuwählen !

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

[siehe EXPLAIN.tensor.ru] Die



Abfrage sieht viel einfacher aus, fast zweimal schneller und in nur einem Index Scan- ein hervorragendes Ergebnis!



Bitte beachten Sie, dass ich, obwohl wir nur 26 Datensätze "bestellt" haben, Index Scaneinen weiteren extrahiert habe - nur um sicherzustellen, dass der "nächste" nicht mehr zu uns passt.







Nun, wir warten auf die offizielle Veröffentlichung von PostgreSQL 13, die für morgen geplant ist.