[PostgreSQL, SQL, Администрирование баз данных, Высокая производительность] PostgreSQL 13: happy pagination WITH TIES
Автор
Сообщение
news_bot ®
Стаж: 6 лет 3 месяца
Сообщений: 27286
На прошедшей неделе вышло сразу две статьи (от Hubert 'depesz' Lubaczewski и автора самого патча Alvaro Herrera), посвященные реализованной в грядущей версии PostgreSQL 13 поддержке опции WITH TIES из стандарта SQL:2008:
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
Что это, и как оно избавляет от проблем с реализацией пейджинга, о которых я рассказывал в статье «PostgreSQL Antipatterns: навигация по реестру»?
Напомню, что в той статье мы остановились на моменте, что если у нас есть табличка такого вида:
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]
Зачем тут вложенный запрос? Ровно за тем, чтобы не иметь описанных в той статье проблем с «перепрыгиванием» одинаковых значений ключа сортировки между запрашиваемыми сегментами:
Пробуем WITH TIES «на зуб»
Но ведь ровно для этого и нужен функционал 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]
Запрос выглядит гораздо проще, почти в 2 раза быстрее, и всего лишь за один Index Scan — отличный результат!
Обратите внимание, что хоть мы и «заказывали» всего 26 записей, Index Scan извлек на одну больше — ровно для того, чтобы убедиться, что «следующая» нам уже не подходит.
Ну что же, ждем официального релиза PostgreSQL 13, который запланирован на завтра.
===========
Источник:
habr.com
===========
Похожие новости:
- [Высокая производительность, Python, Распределённые системы, Финансы в IT] Фоновые задачи на Faust, Часть II: Агенты и Команды
- [Высокая производительность, PostgreSQL, Программирование, Go] Приключения одного бага или как починить pgx чужими руками
- [Видеокарты, Высокая производительность, Компьютерное железо, Процессоры] Ampere — новейшая игровая архитектура NVIDIA. Самое важное из вайт пейпера (перевод)
- [Высокая производительность, Суперкомпьютеры] Обновление списка Top50: частные приросты и общее падение
- [Julia, Высокая производительность, Исследования и прогнозы в IT, Программирование, Промышленное программирование] Julia готова для прода (перевод)
- [IT-инфраструктура, Microsoft SQL Server, Системное администрирование, Хранение данных] Дорожная карта миграции почты IBM Notes/Domino в Exchange и Office 365
- [Высокая производительность, Программирование] Как не сгореть на проекте
- [MySQL, Django] Настройка docker для django на mysql
- [Высокая производительность, Компьютерное железо, Презентации, Видеокарты, Настольные компьютеры] RTX 3080 – Мечта, которой нет в наличии
- [Python, Высокая производительность, Распределённые системы, Финансы в IT] Собираем данные AlphaVantage с Faust. Часть 1. Подготовка и введение
Теги для поиска: #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_vysokaja_proizvoditelnost (Высокая производительность), #_postgresql, #_postgresql_13, #_sql, #_sql_tips_and_tricks, #_with_ties, #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
), #_vysokaja_proizvoditelnost (
Высокая производительность
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 15-Май 22:49
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 3 месяца |
|
|
На прошедшей неделе вышло сразу две статьи (от Hubert 'depesz' Lubaczewski и автора самого патча Alvaro Herrera), посвященные реализованной в грядущей версии PostgreSQL 13 поддержке опции 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')::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] Зачем тут вложенный запрос? Ровно за тем, чтобы не иметь описанных в той статье проблем с «перепрыгиванием» одинаковых значений ключа сортировки между запрашиваемыми сегментами:  Пробуем WITH TIES «на зуб» Но ведь ровно для этого и нужен функционал 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] Запрос выглядит гораздо проще, почти в 2 раза быстрее, и всего лишь за один Index Scan — отличный результат! Обратите внимание, что хоть мы и «заказывали» всего 26 записей, Index Scan извлек на одну больше — ровно для того, чтобы убедиться, что «следующая» нам уже не подходит.  Ну что же, ждем официального релиза PostgreSQL 13, который запланирован на завтра. =========== Источник: habr.com =========== Похожие новости:
Блог компании Тензор ), #_postgresql, #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ), #_vysokaja_proizvoditelnost ( Высокая производительность ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 15-Май 22:49
Часовой пояс: UTC + 5