- PVSM.RU - https://www.pvsm.ru -
На прошедшей неделе вышло сразу две статьи (от Hubert 'depesz' Lubaczewski [1] и автора самого патча Alvaro Herrera [2]), посвященные реализованной в грядущей версии PostgreSQL 13 поддержке опции WITH TIES
[3] из стандарта SQL:2008:
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
Что это, и как оно избавляет от проблем с реализацией пейджинга, о которых я рассказывал в статье «PostgreSQL Antipatterns: навигация по реестру» [4]?
Напомню, что в той статье мы остановились на моменте, что если у нас есть табличка такого вида:
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);
… то для организации хронологического пейджинга по ней (по 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
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;
[посмотреть на explain.tensor.ru] [5]
Зачем тут вложенный запрос? Ровно за тем, чтобы не иметь описанных в той статье проблем с «перепрыгиванием» одинаковых значений ключа сортировки между запрашиваемыми сегментами:
Но ведь ровно для этого и нужен функционал WITH TIES
— чтобы отобрать сразу все записи с одинаковым значением граничного ключа!
EXPLAIN (ANALYZE, BUFFERS)
SELECT
*
FROM
events
WHERE
ts < '2020-01-01'::timestamp
ORDER BY
ts DESC
FETCH FIRST 26 ROWS WITH TIES;
[посмотреть на explain.tensor.ru] [6]
Запрос выглядит гораздо проще, почти в 2 раза быстрее, и всего лишь за один Index Scan
— отличный результат!
Обратите внимание, что хоть мы и «заказывали» всего 26 записей, Index Scan
извлек на одну больше — ровно для того, чтобы убедиться, что «следующая» нам уже не подходит.
Ну что же, ждем официального релиза PostgreSQL 13, который запланирован на завтра.
Автор: Боровиков Кирилл
Источник [7]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/357220
Ссылки в тексте:
[1] Hubert 'depesz' Lubaczewski: https://www.depesz.com/2020/09/22/waiting-for-postgresql-13-support-fetch-first-with-ties/
[2] Alvaro Herrera: https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/
[3] WITH TIES
: https://www.postgresql.org/docs/13/sql-select.html#SQL-LIMIT
[4] «PostgreSQL Antipatterns: навигация по реестру»: https://habr.com/ru/post/498740/
[5] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/0033f58891e838808f5ea751a6673ccc:0:2020-09-23
[6] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/0a9ccbef8130328547168d1d5f97ea8e:0:2020-09-23
[7] Источник: https://habr.com/ru/post/520294/?utm_source=habrahabr&utm_medium=rss&utm_campaign=520294
Нажмите здесь для печати.