PostgreSQL 13: feliz paginação COM TIES

Na semana passada, dois artigos foram publicados de uma vez (de Hubert 'depesz' Lubaczewski e do próprio autor do patch, Alvaro Herrera ), dedicados ao suporte da opção WITH TIESdo padrão SQL: 2008 implementado na próxima versão do PostgreSQL 13 :

OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
O que é e como elimina os problemas de implementação de paginação que discuti em Antipadrões do PostgreSQL: Navegação no registro ?







Deixe-me lembrá-lo de que naquele artigo paramos no ponto que, se tivermos uma placa como esta:



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


... então, para organizar a paginação cronológica por ele (por ts DESC), é mais eficaz usar o seguinte índice:



CREATE INDEX ON events(ts DESC);


... e 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;


Boa e velha subconsulta



Vejamos o plano para tal consulta, se quisermos obter o próximo segmento do início deste ano:



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;




[olhe para explain.tensor.ru]



Por que há uma consulta aninhada aqui? Exatamente para não ter os problemas descritos naquele artigo de "saltar" os mesmos valores da chave de ordenação entre os segmentos solicitados:







Tentar COM LAÇOS "até os dentes"



Mas é exatamente para isso que serve a funcionalidade WITH TIES- selecionar todos os registros com o mesmo valor da chave de limite de uma vez !



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




[olhe para explain.tensor.ru] A



consulta parece muito mais simples, quase 2 vezes mais rápida e em apenas uma Index Scan- um resultado excelente!



Observe que, embora tenhamos "pedido" apenas 26 registros, Index Scanextraí mais um - apenas para ter certeza de que o "próximo" não nos serve mais.







Bem, estamos aguardando o lançamento oficial do PostgreSQL 13, que está agendado para amanhã.



All Articles