Как оптимизировать запросы для показа лучших фотографий пользователя с сортировкой и лимитом

Что проверяют

Умеете ли вы сделать так, чтобы запрос «лучшие фото пользователя» не сортировал все его фотографии при каждом показе. Интервьюер ждёт составной индекс и план запроса, а следом спрашивает про пагинацию на глубоких страницах и про кэш.

Как отвечать

Сам запрос простой, JOIN с users не нужен, фильтр идёт прямо по photos.user_id:

SELECT id, url, likes FROM photos WHERE user_id = %s ORDER BY likes DESC, id DESC LIMIT 10

Дальше по шагам:

• составной индекс (user_id, likes DESC, id DESC): база находит фото пользователя и читает их сразу в нужном порядке, останавливаясь на десятой строке. В EXPLAIN ANALYZE не должно быть узла Sort, только Index Scan под Limit;
• покрывающий индекс: в PostgreSQL INCLUDE (url) позволяет обойтись index-only scan без чтения самой таблицы;
• id в сортировке: при равных лайках порядок иначе не определён, и страницы пересекаются;
• готовый счётчик: если «лучшие» считаются через COUNT(*) по таблице лайков, храните likes или score колонкой и обновляйте при лайке или фоновой задачей;
• keyset-пагинация вместо OFFSET: WHERE (likes, id) < (%s, %s). OFFSET 10000 заставляет прочитать и выбросить десять тысяч строк;
• кэш: Redis с TTL и сбросом ключа при новом лайке или загрузке.

В Django это Photo.objects.filter(user_id=uid).order_by("-likes", "-id").only("id", "url", "likes")[:10] плюс models.Index(fields=["user", "-likes", "-id"]) в Meta.indexes. В SQLAlchemy то же через order_by(Photo.likes.desc()).limit(10).

Где спотыкаются

Создают два отдельных индекса, по user_id и по likes. Планировщик возьмёт один, а сортировку всё равно сделает в памяти.

Кэшируют через cachetools внутри процесса. У каждого воркера gunicorn свой кэш, сбросить его по новому лайку нельзя, и пользователи видят разные данные.

Считают рейтинг выражением прямо в ORDER BY, например likes * 2 + comments. Обычный индекс тут не работает, нужен индекс по выражению или готовая колонка.