Медленный запрос при использовании LIMIT для разбиения на страницы с помощью Symfony/PostgreSQL ⇐ Php

[Anonymous]

Здравствуйте,
Мне нужна помощь в оптимизации моей системы фильтрации/пагинации под Symfony/PostgreSQL.
У меня есть таблица «ссылка» с ~5 млн строк и этот запрос используется для системы нумерации страниц с https://github.com/KnpLabs/KnpPaginatorBundle.

Код: Выделить всё

SELECT *
FROM   link l0_
WHERE  l0_.team_id = 21
AND    l0_.folder_id IS NULL
AND    l0_.created_at >= '2024-09-01 00:00:00'
AND    l0_.created_at < '2024-09-30 00:00:00'
AND    l0_.origin = 'API'
ORDER  BY l0_.id DESC
LIMIT  10;

Эта команда (21) имеет около 2 миллионов ссылок в этой таблице. Вы можете видеть, что в запросе есть несколько фильтров, которые пытаются ограничить количество ссылок, отображаемых на страницах.
Проблема в том, что для этого конкретного клиента этот запрос очень длинный (> 20с).
В то время как в данном случае этот запрос возвращает 0 результата (это нормально, все его ссылки находятся в папках).
Я обнаружил, что удаление инструкции LIMIT 10 "исправляет "Скорость. Вот объяснения обоих запросов:
С лимитом:

Код: Выделить всё

Limit  (cost=0.43..397.88 rows=10 width=611) (actual time=22046.648..22046.650 rows=0 loops=1)
->  Index Scan Backward using idx_23374_primary on link l0_  (cost=0.43..436955.10 rows=10994 width=611) (actual time=22046.646..22046.647 rows=0 loops=1)
Filter: ((folder_id IS NULL) AND (created_at >= '2024-09-01 00:00:00+02'::timestamp with time zone) AND (created_at < '2024-09-30 00:00:00+02'::timestamp with time zone) AND (team_id = 21) AND ((origin)::text = 'API'::text))
Rows Removed by Filter: 5759952
Planning Time: 0.182 ms
Execution Time: 22046.683 ms

Без ограничений:

Код: Выделить всё

Sort  (cost=10741.35..10768.83 rows=10993 width=611) (actual time=0.674..0.675 rows=0 loops=1)
Sort Key: id DESC
Sort Method: quicksort  Memory: 25kB
->  Index Scan using idx_links_without_folder on link l0_  (cost=0.42..10003.48 rows=10993 width=611) (actual time=0.670..0.670 rows=0 loops=1)
Index Cond: ((team_id = 21) AND (created_at >= '2024-09-01 00:00:00+02'::timestamp with time zone) AND (created_at < '2024-09-30 00:00:00+02'::timestamp with time zone) AND ((origin)::text = 'API'::text))
Planning Time: 0.132 ms
Execution Time: 0.691 ms

Конечно, у меня есть несколько индексов, которые могут помочь:

Код: Выделить всё

$this->addSql('CREATE INDEX CONCURRENTLY idx_links_with_folder ON link (team_id, folder_id, created_at, origin, id) WHERE folder_id IS NOT NULL');
$this->addSql('CREATE INDEX CONCURRENTLY idx_links_without_folder ON link (team_id, created_at, origin, id) WHERE folder_id IS NULL');

Но вы можете видеть, что idx_links_without_folder не используется в случае запроса с LIMIT.
Я обнаружил, что это возможно с PostgreSQL: https://www.gojek .io/blog/the-case-s-of-postgres-not-using-index (CASE 5)
Есть ли у вас какой-нибудь совет или подсказка, которые помогут мне улучшить мою систему? Потому что опыт этого клиента действительно ужасен, и я пока не знаю, как его улучшить.
Заранее спасибо!

Подробнее здесь: https://stackoverflow.com/questions/790 ... postgresql