WITH TIES из стандарта SQL:2008:OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH
TIES }
Напомню, что в той статье мы остановились на моменте, что если у нас есть табличка такого вида:
CREATE TABLE events( id serial PRIMARY KEY, ts timestamp, data json);INSERT INTO events(ts)SELECT now() - ((random() * 1e8) || ' sec')::intervalFROM generate_series(1, 1e6);
то для организации хронологического пейджинга по ней (по
ts
DESC) эффективнее всего использовать вот такой индекс:
CREATE INDEX ON events(ts DESC);
и вот такую модель запроса:
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;
Старый-добрый подзапрос
Давайте посмотрим на план такого запроса, если мы хотим получить очередной сегмент от начала этого года:
EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM eventsWHERE 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;
[посмотреть на explain.tensor.ru]
Зачем тут вложенный запрос? Ровно за тем, чтобы не иметь описанных в той статье проблем с перепрыгиванием одинаковых значений ключа сортировки между запрашиваемыми сегментами:
Пробуем WITH TIES на зуб
Но ведь ровно для этого и нужен функционал
WITH TIES
чтобы отобрать сразу все записи с одинаковым значением
граничного ключа!
EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM eventsWHERE ts < '2020-01-01'::timestampORDER BY ts DESCFETCH FIRST 26 ROWS WITH TIES;
[посмотреть на explain.tensor.ru]
Запрос выглядит гораздо проще, почти в 2 раза быстрее, и всего лишь за один
Index Scan отличный результат!Обратите внимание, что хоть мы и заказывали всего 26 записей,
Index Scan извлек на одну больше ровно для
того, чтобы убедиться, что следующая нам уже не подходит.
Ну что же, ждем официального релиза PostgreSQL 13, который запланирован на завтра.
