[Из песочницы] Оптимизация sum в PostgreSQL

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

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

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers. Five Filters recommends: 'You Say What You Like, Because They Like What You Say' - http://www.medialens.org/index.php/alerts/alert-archive/alerts-2013/731-you-say-what-you-like-because-they-like-what-you-say.html