Влияние размера статистики на время планирования запроса в PostgreSQL.
Планировщик - подсистема которая планирует выполнение пришедшего запроса и выбирает оптимальный план который в конечном счете будет использоваться для выполнения запроса. Планировщик в свой работе использует т.н. статистику - специальная информация о данных что хранятся в колонках. Эта статистика периодически собирается с помощью фонового процесса autoanalyze, либо может быть обновлена немедленно с помощью ручного вызова ANALYZE.Детализация собираемой статистики регулируется двумя способами. Первый способ - это определение параметра default_statistics_target. Это значение определяется по умолчанию для всех полей всех таблиц в БД. Второй способ используется в случаях когда нужно определить значение статистики для конкретного поля таблицы, заключается он в выполнении следующей команды: ALTER TABLE mytable ALTER COLUMN mycol SET STATISTICS value;
Таким образом можно регулировать подробность собираемой статистики. Чем подробнее статистика, тем оптимальней план сможет выбрать планировщик. Оптимальный план в свою очередь это скорость и производительность. Будет логично предположить, что выставив default_statistics_target в максимально возможное значение, мы заставим планировщик всегда выбирать оптимальный план. Однако тут есть обратная сторона медали, и заключается она в том, что с увеличением объема собираемой статистики увеличивается время планирования запроса. И чем больше величина, тем больше время планирования.
Ниже я провожу эксперимент с увеличением величины собираемой статистики для полей с разным типом и покажу насколько изменяется время планирования в зависимости от этой величины.
Характеристики стенда на котором проводился эксперимент:
Intel Core i5-3337U, 8GB RAM, SanDisk SDSSDP12 128GB
Fedora Linux 20 kernel 3.16.2-200.fc20.x86_64
PostgreSQL 9.4beta2
Алгоритм эсперимента:
1. увеличиваем величину статистику, собираем статистику
2. прогоняем запросы, усредняем полученные значения
Сначала создаем рабочую таблицу на 2 млн. строк, и устанавливаем начальное значения статистики для полей (по умолчанию default_statistics_target = 100, выставим это значение явно) после чего выполняем ручной ANALYZE чтобы собрать статистику. Затем прогоняем 300 запросов для каждого из полей. Запрос для всех полей выбран один и тот же. План запроса в общем виде одинаковый для всех. Как только выполнилось 300 запросов для одного поля, усредняем полученные значения Planning Time и переходим к выполнению запросов на следующем поле. После выполнения запросов по всем полям, увеличиваем статистику до следующего значения и проводим следующую итерацию экспримента. Запросы будут выполняться при следующих величинах статистики: 100, 500, 1000, 5000, 10000.
Теперь более детально. Создаем следующую таблицу и индексы:
# create table products as select
generate_series(1,2000000) as id,
md5(random()::text)::char(10) as name,
(random()*1000)::numeric(10,2) as price,
(random() * 5500.63 + 1000.12)::real as weight,
(random() * 21 + 22)::int as size,
(array['green','blue','yellow','black','white','red'])[ceil(random()*6)]::varchar(20) as color,
(now() - interval '6 months' + interval '5 months' * random())::timestamptz as updated_at,
(now() - interval '2 year' + interval '1 year' * random())::date as built,
random()::int::bool as avail;
# create index products_price_idx on products (price);
# create index products_weight_idx on products (weight);
# create index products_size_idx on products (size);
# create index products_color_idx on products (color);
# create index products_updated_at_idx on products (updated_at);
# create index products_built_idx on products (built);
Возьмем минимальные и максимальные значения полей
# select min(price)as min_price, max(price) as max_price, min(weight) as min_weight, max(weight) as max_weight, min(size) as min_size, max(size) as max_size, min(updated_at) as min_u, max(updated_at) as max_u, min(built) min_b,max(built) max_b from products;
-[ RECORD 1 ]-----------------------------
min_price | 0.00
max_price | 1000.00
min_weight | 1000.12
max_weight | 6500.75
min_size | 22
max_size | 43
min_u | 2014-03-20 16:09:59.083137+06
max_u | 2014-08-19 16:09:49.609537+06
min_b | 2012-09-20
max_b | 2013-09-19
Используемые запросы:
# select price,count(*) from products where price > 400 and price < 600 group by 1 order by 2 desc;
# select weight,count(*) from products where weight > 3500.00 and weight < 4500.00 group by 1 order by 2 desc;
# select size,count(*) from products where size > 29 and size < 34 group by 1 order by 2 desc;
# select color,count(*) from products where color in ('black','white') group by 1 order by 2 desc;
# select updated_at,count(*) from products where updated_at >= '2014-05-19' and updated_at < '2014-06-20' group by 1 order by 1;
# select built,count(*) from products where built >= '2013-01-19' and built < '2013-04-20' group by 1 order by 1;
Таким образом план во всех запросах будет одинаковый за исключением имен полей и используемых индексов:
# explain analyze select price,count(*) from products where price > 400 and price < 600 group by 1 order by 2 desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2659.16..2685.64 rows=10593 width=6) (actual time=48.110..49.404 rows=12564 loops=1)
Sort Key: (count(*))
Sort Method: quicksort Memory: 1072kB
-> HashAggregate (cost=1845.04..1950.97 rows=10593 width=6) (actual time=40.635..44.559 rows=12564 loops=1)
Group Key: price
-> Bitmap Heap Scan on products (cost=421.48..1746.90 rows=19628 width=6) (actual time=15.286..23.447 rows=19849 loops=1)
Recheck Cond: ((price > 400::numeric) AND (price < 600::numeric))
Heap Blocks: exact=1031
-> Bitmap Index Scan on products_price_idx (cost=0.00..416.57 rows=19628 width=0) (actual time=14.896..14.896 rows=19849 loops=1)
Index Cond: ((price > 400::numeric) AND (price < 600::numeric))
В качестве бонуса, после завершения эксперимента я буду дописывать в таблицу дополнительно 2 млн. и 4 млн. строк и повторять эксперимент. Будет ли изменяться время планирования в зависмости от размера таблицы.
Размеры таблицы:
2 млн. строк: 161MB (518MB с индексами)
4 млн. строк: 322MB (992MB с индексами)
8 млн. строк: 644MV (2070MB с индексами)
Итак результаты:
1 колонка - имя/тип поля и значение статистики
2 колонка - результаты для таблицы с 2 млн. строк
3 колонка - результаты для таблицы с 4 млн. строк
4 колонка - результаты для таблицы с 8 млн. строк
Время выполнения ANALYZE (в ms)
statistics target = 100 639.739 685.179 705.880
statistics target = 500 3115.727 3462.748 3713.264
statistics target = 1000 6556.464 6822.859 7766.403
statistics target = 5000 36745.238 38598.987 41191.251
statistics target = 10000 47801.794 82168.402 85104.524
Время планирования для разных типов полей (в ms)
(price numeric) statistics target = 100 0.947757 0.897493 1.0202
(price numeric) statistics target = 500 1.18513 1.16416 1.21251
(price numeric) statistics target = 1000 1.41473 1.41166 1.42612
(price numeric) statistics target = 5000 2.99679 2.97306 2.99931
(price numeric) statistics target = 10000 4.87488 4.77795 4.72791
(weight real) statistics target = 100 0.812873 0.821597 0.940837
(weight real) statistics target = 500 0.854417 0.92789 0.97752
(weight real) statistics target = 1000 0.884037 0.97609 1.07439
(weight real) statistics target = 5000 1.26579 1.33048 1.36672
(weight real) statistics target = 10000 1.68124 1.75143 1.85807
(size integer) statistics target = 100 0.765417 0.761053 0.794383
(size integer) statistics target = 500 0.759563 0.69591 0.87039
(size integer) statistics target = 1000 0.76174 0.687777 0.770663
(size integer) statistics target = 5000 0.755143 0.71562 0.82004
(size integer) statistics target = 10000 0.754023 0.681577 0.76801
(color varchar) statistics target = 100 0.93082 0.976607 0.9781
(color varchar) statistics target = 500 0.90293 0.903903 1.05091
(color varchar) statistics target = 1000 0.91543 0.872247 0.89474
(color varchar) statistics target = 5000 0.912583 0.903353 0.934407
(color varchar) statistics target = 10000 0.905897 0.90957 0.900067
(updated_at timestamptz) statistics target = 100 0.745513 0.726177 0.822323
(updated_at timestamptz) statistics target = 500 0.84018 0.80479 0.999067
(updated_at timestamptz) statistics target = 1000 0.86203 0.868613 0.843613
(updated_at timestamptz) statistics target = 5000 1.47448 1.19595 1.27509
(updated_at timestamptz) statistics target = 10000 2.41814 1.67079 1.70048
(built date) statistics target = 100 0.729587 0.681103 0.751793
(built date) statistics target = 500 0.81245 0.77315 1.01078
(built date) statistics target = 1000 0.85582 0.94638 0.83206
(built date) statistics target = 5000 0.843517 0.788523 0.88953
(built date) statistics target = 10000 0.833103 0.772553 0.95626
Какие можно сделать выводы из этих результатов:
1. С увеличением величины собираемой статистики время планирования увеличивается (что в итоге сказывается на общем времени выполнения запроса).
2. Время планирования увеличивается в большей степени для полей с типами numeric и timestamptz, в меньшей степени для real. Также можно предположить что этот вывод распространяется на double precision, decimal и timestamp.
3. Время планирования для integer, varchar и date практически неизменно.
4. С увеличением размеров таблицы время планирования существенно не изменяется.
5. Увеличение размера таблицы приводит увеличению времени выполнения ANALYZE.
Собственно вот, хочется добавить что эксперимент проводился на синтетических данных с простым запросом и одним вариантом плана, поэтому в реальных условиях, на данных приближенным к жизни, результаты могут отличаться в большую или меньшую сторону.
На главную "Virtualizing Linux"
Комментариев нет:
Отправить комментарий