PostgreSQL 13: paginación feliz CON LAZOS

La semana pasada se publicaron dos artículos a la vez (de Hubert 'depesz' Lubaczewski y del propio autor del parche, Alvaro Herrera ), dedicados al soporte de la opción WITH TIESdel estándar SQL: 2008 implementado en la próxima versión de PostgreSQL 13 :

OFFSET start { ROW | ROWS }

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

¿Qué es y cómo elimina los problemas de implementación de paginación que mencioné en PostgreSQL Antipatterns: Registry Navigation ?







Déjame recordarte que en ese artículo nos detuvimos en el punto que si tenemos un cartel como este:

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

... entonces, para organizar la paginación cronológica por él (por ts DESC) es más eficaz utilizar el siguiente índice:

CREATE INDEX ON events(ts DESC);

... y este modelo de consulta:

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;

Buena vieja subconsulta

Veamos el plan para una consulta de este tipo, si queremos obtener el siguiente segmento desde principios de este año:

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;

[mira explica.tensor.ru]



¿Por qué hay una consulta anidada aquí? Exactamente para no tener los problemas descritos en ese artículo con "saltar" los mismos valores de la clave de clasificación entre los segmentos solicitados:

Probar CON LAZOS "a los dientes"

Pero esto es exactamente para lo que WITH TIESse necesita la funcionalidad : ¡seleccionar todos los registros con el mismo valor de la clave de límite a la vez !

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

[mira explica.tensor.ru] La



consulta parece mucho más simple, casi 2 veces más rápida, y en solo una Index Scan, ¡un resultado excelente!



Tenga en cuenta que a pesar de que "ordenamos" sólo 26 registros, Index Scanextraje uno más , solo para asegurarme de que el "siguiente" ya no nos conviene.







Bueno, estamos esperando el lanzamiento oficial de PostgreSQL 13, que está programado para mañana.