Рассмотрим ситуацию: имеется статистическая таблица с колонками-идентификаторами и колонками-счётчиками. Требуется просуммировать счётчики по некоторому подмножеству. При этом нас не интересует, каким образом мы выбираем интересующее нас множество — про индексы и партицирование написано множество книг и статей. Будем считать, что все данные уже выбраны самым оптимальным способом и изучим, как быстрее суммировать.
Это не первое место, которое надо оптимизировать, если запрос тормозит, скорее последнее. Изложенные ниже идеи осмысленно применять когда план выполнения (explain) уже с виду идеальный и комар в нём носа не подточит, но хочется «выжать» ещё немного.
Сделаем тестовую таблицу и запишем в неё 10 миллионов записей:
create table s (
d date,
browser_id int not null,
banner_id int not null,
views bigint,
clicks bigint,
primary key(d, browser_id, banner_id)
);
insert into s
select d, browser_id, banner_id, succ + insucc, succ
from (
select d, browser_id, banner_id, (array[0,0,50,500])[ceil(random()*4)] succ, (array[0,0,400,400000])[ceil(random()*4)] insucc
from generate_series(now() - interval '99 day', now(), '1 day') d
cross join generate_series(0, 999) banner_id
cross join generate_series(0, 99) browser_id
)_;
Дата, id-шники и primary key даны лишь для приличия — задача у нас будет простая, просуммировать всю таблицу. Странная генерация значений для views и clicks нужна чтобы смоделировать реальную ситуацию, в которой значения часто бывают и нулевыми и довольно большими.
Итак, приступим. Все измерения времени будем делать при повторном исполнении запроса чтобы исключить влияние холодного кеша.
Честных бенчмарков не проводил, запускал несколько раз, брал среднее и округлял. Машина слабенькая, у вас будет быстрее!
Метод 1: «В лоб»
select sum(clicks) from s;9 секунд.
Метод 2: меняем тип
Пересоздадим нашу таблицу, при этом счётчики сделаем типа numeric:
create table s (
d date,
browser_id int not null,
banner_id int not null,
views numeric,
clicks numeric,
primary key(d, browser_id, banner_id)
);
8 секунд. Казалось бы, тип numeric должен быть более неповоротливым, так как допускает хранение чисел очень больших размеров и следовательно далёк от машинного представления. На деле он оказывается быстрее.
Разгадка такова: sum, принимая на вход bigint или numeric, возвращает в обоих случаях numeric. Это сделано во избежание переполнения. Когда мы сразу даём numeric на вход, мы избегаем неявной конвертации.
Замечание первое: если нам хватает для счётчика типа int (а он может принимать значения до ~2 млрд) — то с ним работа будет ещё быстрее. Sum в таком случае возвращает bigint.
Замечание второе: обычные арифметические операции (+, -, *, /) с типом numeric работают медленнее чем с bigint. А sum — быстрее с numeric.
Метод 3: не считаем нули
select sum(clicks) from s where clicks <> 0;
Такой метод даёт ускорение до 7 секунд. Но он имеет недостаток: при суммировании значений из нескольких колонок непонятно как его применять, особенно если колонки обнуляются независимо.
Метод 4: заменяем нули на null'ы
select sum(nullif(сlicks, 0)) from s;
Те же 7 секунд, но метод работает лучше предыдущего при суммировании нескольких колонок.
Причина состоит в том, что sum, как строгая (strict) агрегатная функция игнорирует поступающие на вход null'ы.
Методы 3 и 4 целесообразны, когда в колонке существенное количество нулей (хотя бы 10%, а лучше половина).
Метод 5: заменяем нули на null'ы прямо в таблице
Этот способ оптимизации несколько «неэтичен»: мы предлагаем нагло попрать семантику значения null. Зато работает.
create table s2 (like s including all);
insert into s2 select d, browser_id, banner_id, nullif(views, 0), nullif(clicks, 0) from s;
select sum(clicks) from s2;
6 секунд. Видимо, причина кроется в том что постгрес хранит nulls в битмапе и поэтому размер tupl'а уменьшается.
Возможно, читатель с удивлением спросит: почему же мы сразу столбцы с нулями не выбросили при выборке, например при помощи частичного индекса?
А мы поясним: в реальных таблицах много колонок, и нули в них расположены независимо.
Ссылки на документацию:
Автор: basht
