Pull to refresh

Оптимизация sum в PostgreSQL

Reading time 3 min
Views 19K
Рассмотрим ситуацию: имеется статистическая таблица с колонками-идентификаторами и колонками-счётчиками. Требуется просуммировать счётчики по некоторому подмножеству. При этом нас не интересует, каким образом мы выбираем интересующее нас множество — про индексы и партицирование написано множество книг и статей. Будем считать, что все данные уже выбраны самым оптимальным способом и изучим, как быстрее суммировать.

Это не первое место, которое надо оптимизировать, если запрос тормозит, скорее последнее. Изложенные ниже идеи осмысленно применять когда план выполнения (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'а уменьшается.

Возможно, читатель с удивлением спросит: почему же мы сразу столбцы с нулями не выбросили при выборке, например при помощи частичного индекса?
А мы поясним: в реальных таблицах много колонок, и нули в них расположены независимо.

Ссылки на документацию:
  1. Numeric Types
  2. Sum aggregate function
  3. User-defined aggregate functions, strictness

Tags:
Hubs:
+28
Comments 23
Comments Comments 23

Articles