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

Question

[user_of_toster]

Есть 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 ?

Answer 1

[Сергей Паньков]

Всё плохо:
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 с доставанием необходимого

Comments

[user_of_toster]

Большое спасибо за замечания, код поправил, время исполнения уменьшилось в два раза.

Можно ли сверху осуществить это?

Можно ли создать индекс по rating так, чтобы rating хранился в вычисленном+сортированном виде и эффективно перевычислялся в случае изменения post_rate ?

[Сергей Паньков]

user_of_toster, можно на добавление post_rate триггер сделать. который будет инкрементировать рейтинг у поста. Имеет смысл хранить ретйинг в отдельной таблице, чтобы не лочить таблицу постов каждым дайком.
Если поток лайков\дизлайков очень бстрый и есть желание аггрегировать это не в онлайн-режиме, а периодически, то можно добавить в таблицу лайков дату время и по расписанию. скажем, раз в 5 минут, аггрегировать рейтинг и писать его в талицу для каждого поста. То есть суммировать не все лайки, а только созданные после последней аггрегации. Получившееся число нужно добавлять к тому, что было посчитано ранее.

Индекс - это про другое. Это про ускорение джойнов, про ускорение сортировок и фильтрации.

Answer 2

[Melkij]

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 нагрузки

Comments

[user_of_toster]

Большое спасибо за ответ, каждый вариант сейчас рассматриваю