PostgreSQL 13: pagination heureuse AVEC TIES

La semaine dernière, deux articles ont été publiés à la fois (de Hubert 'depesz' Lubaczewski et de l'auteur du patch Alvaro Herrera ), consacrés au support de l'option WITH TIESdu standard SQL: 2008 implémenté dans la prochaine version de PostgreSQL 13 :

OFFSET start { ROW | ROWS }

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

Qu'est-ce que c'est et comment élimine-t-il les problèmes d'implémentation de la pagination dont j'ai parlé dans PostgreSQL Antipatterns: Registry Navigation ?







Permettez-moi de vous rappeler que dans cet article, nous nous sommes arrêtés au point que si nous avons un signe comme celui-ci:

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

... puis pour organiser la pagination chronologique par elle (par ts DESC), il est plus efficace d'utiliser l'index suivant:

CREATE INDEX ON events(ts DESC);

... et ce modèle de requête:

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;

Bonne vieille sous-requête

Jetons un coup d'œil au plan d'une telle requête si nous voulons obtenir le segment suivant du début de cette année:

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;

[regardez expliquez.tensor.ru]



Pourquoi y a-t-il une requête imbriquée ici? Exactement pour ne pas avoir les problèmes décrits dans cet article en "sautant" les mêmes valeurs de la clé de tri entre les segments demandés:

Essayer AVEC TIES "jusqu'aux dents"

Mais c'est exactement à cela que sert la fonctionnalité WITH TIES: sélectionner tous les enregistrements avec la même valeur de la clé de limite à la fois !

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

[regardez expliquez.tensor.ru] La



requête semble beaucoup plus simple, presque 2 fois plus rapide, et en un seul Index Scan- un excellent résultat!



Veuillez noter que même si nous n'avons "commandé" que 26 disques, j'en ai Index Scanextrait un de plus - juste pour m'assurer que le "suivant" ne nous convient plus.







Eh bien, nous attendons la sortie officielle de PostgreSQL 13, qui est prévue pour demain.