Как лучше ускорить выборку с пагинацией?

Question

[Ler Den]

Сразу скажу, я в нюансах не силен, знаю sql в общих чертах.
Сейчас пагинация сделана стандартно через запоминание последнего айди. Примерно так:

SELECT *          
            FROM recording WHERE recording.id > 0 AND recording.artist_id = '269608'
            ORDER BY recording.id
            LIMIT 10

Запрос идет 5-10 секунд. План запроса здесь https://explain.depesz.com/s/a8Xl
Как видно больше всего времени тянет сканирование индекса, не пойму почему.
Если убрать ORDER BY , то все веселее https://explain.depesz.com/s/WpTp
Еще заметил, что если айдишник артиста не 269608, а меньше, например 500, то запрос идет очень быстро. И чем больше айдишник, тем дольше выполняется запрос. Разве это нормально? Такое впечатление, что все айди пересчитываются по порядку пока не дойдет до нужного.
В общем, как быть без ORDER BY, если нужна и скорость (в первую очередь) ну и фильтрация данных?
Таблица содержит 18млн записей, но не думаю, что это прямо таки неподъемная схема для субд (?)

UPD
Опишу задачу полностью.
Нужно достать треки по айдишнику артиста. Треки лежат в таблице recording, артисты - в таблице artist. Но таблицы не связаны напрямую, а только посредством двух других - artist_credit_name и artist_credit.
Связь следующая: artist.id <--> artist_credit_name .artist, artist_credit_name .artist_credit <--> artist_credit.id <--> recording.artist_credit
Вся схема здесь
Полностью запрос выглядит так:

SELECT recording.id  AS "recordingId", recording.name AS "trackName", artist.name AS "artistName"
            FROM artist
            INNER JOIN artist_credit_name ON artist.id = artist_credit_name.artist
            INNER JOIN artist_credit ON artist_credit_name.artist_credit = artist_credit.id
            INNER JOIN recording ON artist_credit.id = recording.artist_credit                      
            WHERE artist.id = $(artistId) AND recording.id > $(index) 
            ORDER BY recording.id LIMIT $(limit)

Получается так, что одному `artist.id` может соответствовать несколько `artist_credit.id`. Поэтому я пытался переписать запрос таким образом, что сначала выбираем все `artist_credit.id` для данного артиста а потом по ним уже с помощью `WHERE IN` выбирать треки, ускорение примерно на 30% (хотя может это погрешность), но результат все равно не тот что нужен.
Индексы по таблицам :
recording: id (PK),
artist_credit: id (PK),
artist_credit_name: id (PK), artist(FK),
artist: id (PK)
Может добавить индекс на поле `recording.artist_credit` ? Не знаю можно ли добавлять индексы на внешние ключи?

UPD#2 Добавил индекс на `recording.artist_credit`, теперь запрос идет быстро

Comments

[Melkij]

Как нетрудно заметить по результату explain - вы показываете совершенно не тот запрос, что исполняет база. Ну или recording - это view.
Соответственно нужны DDL участвующих таблиц и словесное описание что нужно из них достать.

[Ler Den]

Melkij, обновил

Answer 1

[Антон Шаманов]

Таблица содержит 18млн записей, но не думаю, что это прямо таки неподъемная схема для субд (?)

это от сервера зависит. кроме pk индексы есть? или пробовал добавлять, например, unique id + artist_id?
зачем нужна проверка "recording.id > 0", вы авто инкримент не используете?
зачем сортировать по id если значения все равно записаны в данном порядке благодаря авто инкрименту.

Comments

[Ler Den]

кроме pk индексы есть?

Да, в том то и дело, что индекса на artist_credit не было в таблице recording. Из-за этого тормозился весь процесс.

зачем нужна проверка "recording.id > 0"

в реальности вместо 0 там параметром айдишник последней выбранной записи. Для пагинации.

зачем сортировать по id если значения все равно записаны в данном порядке благодаря авто инкрименту.

без сортировки записи выбирались в случайном порядке. Наверное, что легче было выбрать СУБД, что ближе всего лежало то и первым она и выбирала

[Антон Шаманов]

Ler Den, я что-то забыл что у Вас PostgreSQL и по привычке пошпарил по MySql)

в реальности вместо 0 там параметром айдишник последней выбранной записи.

PostgreSQL разве не поддерживает запись вида "ORDER BY $page, 10"?

Answer 2

[dimarick]

У вас проблема в том что постгрес сначала сортирует ВСЁ, а потом берет limit.
Попробуйте добавить что-то вроде: AND recording.id < $(index+дофига), где дофига - это limit + максимальный мыслимый размер "дырки" в списке первичных ключей.