Melkij

Каждая проверка - целостный срез данных? То есть может быть такое, что для фразы 5 последняя проверка была в 2020-07-05, а для фразы 6 надо отматывать в 2020-07-03?

В общем-то, едим слона по частям:

извлечь суммарную информацию: сколько позиций фраз выросло, сколько позиций просело, сколько позиций осталось неизменными

select count(*) filter(where r1.position < r2.position) as pos_down,
count(*) filter(where r1.position = r2.position) as pos_same,
count(*) filter(where r1.position > r2.position) as pos_up from ...

Укус второй: надо откуда-то получить r1 и r2 соответственно строки таблицы за сравниваемые срезы. Это

tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....

Доедаем слона:
нужно дописать условие фильтрации и достать для r1 данные одного среза, для r2 - другого. Тут у вас не вполне конкретизировано что есть "предыдущая проверка". Например, достанем из таблицы tasks предыдущий task_id:

where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)

Cnt число?
Select cnt * interval '1 day'

Увидел 3 ответа, ну думаю, элементарно, ответили уже. Но зачем так сложно?
group by field_name is null
всё. Группировка по выражению, зачем его делать строкой в подзапросе или числом? bool самого по себе более чем достаточно.

select count(*) filter (where status = 'active'),
count(*) filter (where status = 'offline') ...

наиболее оптимально

не делать count вовсе.

select source, 
count( distinct user_id) filter(where watch > 0) as watch_cnt,
count( distinct user_id) filter(where download > 0) as download_cnt
from help group by source

?

select distinct on (price_id), price_id,value  from tablename order by price_id, time_action desc

Если на каждый price_id много строк - то вы неверно думаете что запрос замедляет.

select price_id, value from prices as t1 lateral (select value from test t2 where t1.id=t2.price_id order by time_action desc limit 1) on true;

Если таблицы prices нет - то сооружать loose index scan вручную.
Всё разумеется с подходящим индексом.

ORDER BY ID

хм.

Покажите explain (analyze,buffers) запросов. И покажите планы запросов с "ORDER BY ID + 0". И ещё

select * from pg_stats where tablename = 'table' and attname = 'column'

Я предположу что у вас вовсе не in медленный, а or ломает оценку планировщика, но в этом конкретном случае это оказывается неожиданным благом.

Не надо использовать большой offset. Всё. В любой СУБД.
https://use-the-index-luke.com/no-offset
https://use-the-index-luke.com/sql/partial-results...

Вопросы оптимизации запросов не имеют смысла без указания используемой СУБД.

select ..., p.event_id is not null from events as e left join participations as p on p.user_id = ? and e.id = p.event_id

либо

select ..., exists(select null from participations as p on p.user_id = ? and e.id = p.event_id) from events as e

Это синтаксис именно MS SQL.
Но sqlite его для совместимости тоже умеет: https://www.sqlite.org/lang_keywords.html

Следовательно, импортируйте сперва в sqlite, затем экспортируйте нормально. В нормальных стандартных кавычках - если ваш mysql в sql_mode=ANSI_QUOTES - или в специфичных для mysql апострофах `.

create type person_type as enum ('registrator', 'coordinator', 'user');
create table person (
id serial,
name person_type not null
);

В том смысле чтобы participants.contest проверял, что такой uuid есть в users.guid или teams.guid?
Нет, так нельзя.

Сделайте триггер который будет копировать логику FK.
Вынесите общие поля в отдельную таблицу, объединяйте как 1:1 с teams или users
Вынесите общие поля в отдельную таблицу, воспользуйтесь наследованием таблиц (inherits).

select distinct on (raw_id) raw_id, id, single_sum 
from tablename
order by raw_id, id desc

Может быть как обычно перепутал направление сортировки.

В таблице items 12 млн записей, в using_items 11 млн. Разумеется количество гипотетическое и маловероятное.

Да нагенерируйте и посмотрите explain analyze. Были бы действительно объёмы, а не всего-то десяток миллионов строк.

not in дорог из-за требования стандарта по части обработки NULL в not in и отсутствии специальных оптимизаций для этого случая. Может быть оптимизация появится.
not exists оптимален.
через left join план может быть идентичным not exists