Это не первое место, которое надо оптимизировать, если запрос тормозит, скорее последнее. Изложенные ниже идеи осмысленно применять когда план выполнения (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’а уменьшается.
Возможно, читатель с удивлением спросит: почему же мы сразу столбцы с нулями не выбросили при выборке, например при помощи частичного индекса?
А мы поясним: в реальных таблицах много колонок, и нули в них расположены независимо.
Ссылки на документацию:
ссылка на оригинал статьи http://habrahabr.ru/post/186788/
Добавить комментарий