Как выбрать следующие 100 строк из большой таблицы?

Question

[WhatIsHTML]

Есть две связанные таблицы. Делаю выборку:

SELECT * FROM video INNER JOIN recording ON video.\"recordingId\" = recording.id LIMIT 100;

Количество строк в одной таблице свыше 10млн.
1. Как лучше всего реализовать выборку в postgresql, чтобы можно было выбрать следующие 100, 200 и т.д.?
2. Нужно ли включать order by в таких огромных таблицах?
3. Если таблиц нужно связать три? Быстрее ли будет, если сделать три отдельных запросы в бд?

Answer 1

[freeExec]

1) SELECT ... LIMIT 100 OFFSET 300
2) Это добавит тормозов, т.к. базе придётся прочитать все миллион строк и отсортировать их. Если это не критично, то не стоит.
3) Это как?

Comments

[WhatIsHTML]

3. Ну типа берем сначала вытаскиваем данные с одной таблицы, потом по результату смотрим id, по нему вытаскиваем данные связанные со второй таблицы, затем смотрим новый результат и так же вытаскиваем с третьей. Получается в бд не будет строится огромная временная таблица из трех больших таблиц.

[freeExec]

WhatIsHTML, по идее не должно быть заметной разницы, но как советуют, стоит поэкспериментировать.

Answer 2

[Melkij]

2. Нужно ли включать order by в таких огромных таблицах?

Не зависит от размера базы.
Или у вас есть order by и поведение ожидаемое или у вас нет order by и СУБД имеет полное право на каждый запрос выдавать любые подходящие под фильтр данные в любом порядке.
Запрос выдающий неправильные данные обычно никому не нужен, даже если он и быстрый.

offset для пагинации вообще вещь неуклюжая. https://use-the-index-luke.com/no-offset
Быстрая пагинация - это передача уникального идентификатора, последнего на просматриваемой странице. Т.е. запрос на выборку следующих 100 записей начиная после такого-то id.
Бонусом консистентное поведение, если, например, что-то из строк предыдущей страницы удалили. Оффсет тупо потеряет строку из выборки и пользователь может не найти то что искал.

3. Ну типа берем сначала вытаскиваем данные с одной таблицы, потом по результату смотрим id, по нему вытаскиваем данные связанные со второй таблицы, затем смотрим новый результат и так же вытаскиваем с третьей. Получается в бд не будет строится огромная временная таблица из трех больших таблиц.

Вы изобрели то что делает джойн. База в общем случае его сделает лучше.
Будет ли огромная временная таблица - смотрите план.

Answer 3

[Дмитрий Донковцев]

Презентация с одной из конференций (PGDay) https://pgday.ru/presentation/232/5964945ea4142.pdf , посмотрите, начиная со страницы 14, там более сложные способы, но они обеспечат хорошую скорость, в отличие от OFFSET.