user_of_toster
@user_of_toster

Как оптимизировать SQL Join + Expresssion?

Есть view:
create view posts_view as
select p.*,
         case when pl.likes is null then 0 else pl.likes end
         -
         case when pdl.dislikes is null then 0 else pdl.dislikes end
         as rating
  from posts as p
  left join (select
             count(*) as likes,
             post_id
             from post_rate
             where rate = 1
             group by post_id) as pl
             on p.id = pl.post_id
  left join (select
             count(*) as dislikes,
             post_id
             from post_rate
             where rate = -1
             group by post_id) as pdl
             on p.id = pdl.post_id;


Есть запрос:
select * from posts_view order by rating limit 5;
Order by в запросе вычисляет rating для всей таблицы, а потом делает сортировку.

С чего начать оптимизацию? Можно ли создать индекс по rating так, чтобы rating хранился в вычисленном+сортированном виде и эффективно перевычислялся в случае изменения post_rate ?
  • Вопрос задан
  • 59 просмотров
Решения вопроса 2
trapwalker
@trapwalker
Программист, энтузиаст
Всё плохо:
1.
case when pl.likes is null then 0 else pl.likes end

добнее записывать по-другому:
coalesce(pl.likes)
2. Зачем вы группируете рейтинг по постам, если потом фильтруете при джойне?!
3. Зачем вообще делаете такие джойны с подзапросами, когда можно один раз сделать джойн с таблцей оценок, делать группировку по посту, а по оценке аггрегировать суммированием?

select pr.id, sum(pr.rate) as rating
  from posts as p
      left join post_rate pr on pr.post_id = p.id
  group by p.id

Если нужно еще что-т из таблицы постов в селект включить, то либо под группировку это аносите и в селект включайте, либо весь приведенный мной запрос в with заносите и джойньте его еще раз с таблицей постов по id с доставанием необходимого
Ответ написан
Melkij
@Melkij
PostgreSQL DBA
Order by в запросе вычисляет rating для всей таблицы, а потом делает сортировку.

А как вы ещё предлагаете считать topN? Откуда знать, что где-то в середине множества данных не найдётся новой top1 величины? Конечно надо найти все величины и затем из них выбрать топ.

coalesce, джойны, сортировки - всё фигня на фоне count. Это для вас одна маловажная циферка (вот реально ведь критично, 157832 лайков там или 157784?) а базе идти и вычитывать всю таблицу, проверять, видна ли каждая конкретная строка именно вашей текущей транзакции, группировать и считать. Да не будет это никогда нормально работать в OLTP нагрузке, конечно.

А задача простая и банальная. "как делать count в OLTP" описано много где - не делать count.
Как получить бесполезную циферку быстро - зависит от потребностей.
- спросить грубую оценку в explain и не считать вообще
- материализовать в отдельную таблицу post_id, likes_count, dislikes_count и обновлять триггерами
- материализовать в табличку и обновлять по таймеру, а сами счётчики считать где-то во внешнем мире - redis тот же хорошо умеет increment
- просто кэшировать и обновлять рейтинг в фоне (как самое простое для вашей view - заменить её на materialized view + периодический refresh concurrently и повесить индекс на рейтинг)
- ещё что угодно подходящее под вашу задачу и ваши ограничения, но по-прежнему исключающее count большого числа строк из oltp нагрузки
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы