пятница, 4 декабря 2009 г.

Тестирование PostgreSQL 8.3 на больших таблицах: 10М записей

Продолжение статьи
Тестирование PostgreSQL 8.1 на больших таблицах: денормализация

Как один из вариантов оптимизации скорости выборки может быть использована денормализация таблиц. Продолжим работать с тем же тестовым распределением, но теперь в таблице фактов заменим числовые ключи на строковые значения, соответствующие значениям из справочников. Для генерации тестового распределения использована приведенная ниже функция, создающая строки из символа "0", повторенного случайное число раз от нуля до заданного аргументом значения.

Для вычисления времени выполнения запроса на этот раз использую команду explain analyze, хотя имхо она дает заниженное значение времени. Количество возвращаемых строк также можно увидеть в выводе названной команды, но из соображений удобства привожу и результат запроса на получение числа строк.

Параметры сервера:

Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz
MemTotal: 8310900 kB


Параметры сервера PostgreSQL:

shared_buffers = 1600MB
work_mem = 100MB # min 64kB
maintenance_work_mem = 200MB # min 1MB


PostgreSQL version

select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 8.3.8 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2
(1 row)


Вспомогательные функции:

CREATE OR REPLACE FUNCTION public.populate_varstring(length int)
RETURNS text AS
$BODY$

return [string repeat 0 [expr {round($1*rand())}]]

$BODY$
LANGUAGE 'pltcl' VOLATILE;


CREATE OR REPLACE FUNCTION public.populate_string(length int)
RETURNS text AS
$BODY$

return [string repeat 0 $1]

$BODY$
LANGUAGE 'pltcl' VOLATILE;


Нормализованная таблица без справочников (10M записей)

create table tmp_facts7(a1 int,a2 int,a3 int,a4 int);
insert into tmp_facts7(a1,a2,a3,a4) select (18*random())::int,(18*random())::int,
(18*random())::int,(18*random())::int from generate_series(1,10000000);

create index tmp_facts7_a1_idx on tmp_facts7(a1);
create index tmp_facts7_a2_idx on tmp_facts7(a2);
create index tmp_facts7_a3_idx on tmp_facts7(a3);
create index tmp_facts7_a4_idx on tmp_facts7(a4);

analyze;

select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
count
-------
89
(1 row)

explain analyze select * from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tmp_facts7 (cost=25718.25..30799.05 rows=77 width=16) (actual time=278.834..280.541 rows=89 loops=1)
Recheck Cond: ((a4 = 2) AND (a3 = 2) AND (a2 = 2))
Filter: (a1 = 2)
-> BitmapAnd (cost=25718.25..25718.25 rows=1458 width=0) (actual time=278.354..278.354 rows=0 loops=1)
-> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.901..84.901 rows=554262 loops=1)
Index Cond: (a4 = 2)
-> Bitmap Index Scan on tmp_facts7_a3_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=85.277..85.277 rows=555661 loops=1)
Index Cond: (a3 = 2)
-> Bitmap Index Scan on tmp_facts7_a2_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.142..84.142 rows=556353 loops=1)
Index Cond: (a2 = 2)
Total runtime: 280.818 ms
(11 rows)

SELECT relname, relpages, reltuples FROM pg_class
WHERE NOT relname LIKE 'pg_%' ORDER BY relpages DESC;

relname | relpages | reltuples
-----------------------------------+----------+-------------
tmp_facts7 | 54055 | 1.00002e+07
tmp_facts7_a3_idx | 21946 | 1.00002e+07
tmp_facts7_a4_idx | 21946 | 1.00002e+07
tmp_facts7_a2_idx | 21946 | 1.00002e+07
tmp_facts7_a1_idx | 21946 | 1.00002e+07


Нормализованная таблица со справочниками (10M записей + 10,100,1k,10k записей в справочниках)

drop table tmp_facts7_a1;
create table tmp_facts7_a1(id int, name text);
insert into tmp_facts7_a1
select generate_series,populate_string(generate_series::int) from generate_series(1,10);
create index tmp_facts7_a1_name_idx on tmp_facts7_a1(name);


drop table tmp_facts7_a2;
create table tmp_facts7_a2(id int, name text);
insert into tmp_facts7_a2
select generate_series,populate_string(generate_series) from generate_series(1,100);
create index tmp_facts7_a2_name_idx on tmp_facts7_a2(name);


drop table tmp_facts7_a3;
create table tmp_facts7_a3(id int, name text);
insert into tmp_facts7_a3
select generate_series,populate_string(generate_series) from generate_series(1,1000);
create index tmp_facts7_a3_name_idx on tmp_facts7_a3(name);


drop table tmp_facts7_a4;
create table tmp_facts7_a4(id int, name text);
insert into tmp_facts7_a4
select generate_series,populate_string(generate_series) from generate_series(1,10000);
create index tmp_facts7_a4_name_idx on tmp_facts7_a4(name);

analyze;

select count(*)
from tmp_facts7
join tmp_facts7_a1 on tmp_facts7.a1=tmp_facts7_a1.id
join tmp_facts7_a2 on tmp_facts7.a2=tmp_facts7_a2.id
join tmp_facts7_a3 on tmp_facts7.a3=tmp_facts7_a3.id
join tmp_facts7_a4 on tmp_facts7.a4=tmp_facts7_a4.id
where tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
92

explain analyze
select count(*)
from tmp_facts7
join tmp_facts7_a1 on tmp_facts7.a1=tmp_facts7_a1.id
join tmp_facts7_a2 on tmp_facts7.a2=tmp_facts7_a2.id
join tmp_facts7_a3 on tmp_facts7.a3=tmp_facts7_a3.id
join tmp_facts7_a4 on tmp_facts7.a4=tmp_facts7_a4.id
where tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=31225.63..31225.64 rows=1 width=0) (actual time=292.260..292.260 rows=1 loops=1)
-> Nested Loop (cost=26112.94..31225.62 rows=1 width=0) (actual time=279.488..292.219 rows=92 loops=1)
Join Filter: (tmp_facts7.a1 = tmp_facts7_a1.id)
-> Nested Loop (cost=26112.94..31224.49 rows=1 width=4) (actual time=279.421..283.928 rows=1727 loops=1)
-> Nested Loop (cost=0.00..19.81 rows=1 width=12) (actual time=0.067..0.094 rows=1 loops=1)
-> Nested Loop (cost=0.00..11.53 rows=1 width=8) (actual time=0.054..0.075 rows=1 loops=1)
-> Seq Scan on tmp_facts7_a2 (cost=0.00..3.25 rows=1 width=4) (actual time=0.009..0.027 rows=1 loops=1)
Filter: (name = '00'::text)
-> Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4 (cost=0.00..8.27 rows=1 width=4) (actual time=0.042..0.043 rows=1 loops=1)
Index Cond: (tmp_facts7_a4.name = '00'::text)
-> Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3 (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=1)
Index Cond: (tmp_facts7_a3.name = '00'::text)
-> Bitmap Heap Scan on tmp_facts7 (cost=26112.94..31179.16 rows=1458 width=16) (actual time=279.350..282.432 rows=1727 loops=1)
Recheck Cond: ((tmp_facts7.a4 = tmp_facts7_a4.id) AND (tmp_facts7.a3 = tmp_facts7_a3.id) AND (tmp_facts7.a2 = tmp_facts7_a2.id))
-> BitmapAnd (cost=26112.94..26112.94 rows=1458 width=0) (actual time=278.866..278.866 rows=0 loops=1)
-> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.969..84.969 rows=555316 loops=1)
Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
-> Bitmap Index Scan on tmp_facts7_a3_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.886..84.886 rows=554854 loops=1)
Index Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
-> Bitmap Index Scan on tmp_facts7_a2_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.375..84.375 rows=554019 loops=1)
Index Cond: (tmp_facts7.a2 = tmp_facts7_a2.id)
-> Seq Scan on tmp_facts7_a1 (cost=0.00..1.12 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1727)
Filter: (tmp_facts7_a1.name = '00'::text)
Total runtime: 292.545 ms
(24 rows)

select count(*)
from tmp_facts7
where
tmp_facts7.a1 IN (select id from tmp_facts7_a1 where name='00') and
tmp_facts7.a2 IN (select id from tmp_facts7_a2 where name='00') and
tmp_facts7.a3 IN (select id from tmp_facts7_a3 where name='00') and
tmp_facts7.a4 IN (select id from tmp_facts7_a4 where name='00');
92

explain analyze
select count(*)
from tmp_facts7
where
tmp_facts7.a1 IN (select id from tmp_facts7_a1 where name='00') and
tmp_facts7.a2 IN (select id from tmp_facts7_a2 where name='00') and
tmp_facts7.a3 IN (select id from tmp_facts7_a3 where name='00') and
tmp_facts7.a4 IN (select id from tmp_facts7_a4 where name='00');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=74626.19..74626.20 rows=1 width=0) (actual time=1793.824..1793.825 rows=1 loops=1)
-> Nested Loop IN Join (cost=8720.70..74626.19 rows=1 width=0) (actual time=106.328..1793.736 rows=92 loops=1)
Join Filter: (tmp_facts7.a1 = tmp_facts7_a1.id)
-> Nested Loop IN Join (cost=8720.70..74625.05 rows=1 width=4) (actual time=102.717..1785.337 rows=1727 loops=1)
Join Filter: (tmp_facts7.a2 = tmp_facts7_a2.id)
-> Hash IN Join (cost=8720.70..74621.79 rows=1 width=8) (actual time=101.388..1171.259 rows=30813 loops=1)
Hash Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
-> Nested Loop (cost=8712.42..74609.74 rows=1000 width=12) (actual time=101.341..913.673 rows=555316 loops=1)
-> HashAggregate (cost=8.28..8.29 rows=1 width=4) (actual time=0.041..0.042 rows=1 loops=1)
-> Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4 (cost=0.00..8.27 rows=1 width=4) (actual time=0.037..0.038 rows=1 loops=1)
Index Cond: (name = '00'::text)
-> Bitmap Heap Scan on tmp_facts7 (cost=8704.15..68022.40 rows=526325 width=16) (actual time=101.294..452.725 rows=555316 loops=1)
Recheck Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
-> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.889..84.889 rows=555316 loops=1)
Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
-> Hash (cost=8.27..8.27 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=1)
-> Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3 (cost=0.00..8.27 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)
Index Cond: (name = '00'::text)
-> Seq Scan on tmp_facts7_a2 (cost=0.00..3.25 rows=1 width=4) (actual time=0.003..0.018 rows=1 loops=30813)
Filter: (tmp_facts7_a2.name = '00'::text)
-> Seq Scan on tmp_facts7_a1 (cost=0.00..1.12 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1727)
Filter: (tmp_facts7_a1.name = '00'::text)
Total runtime: 1794.108 ms
(23 rows)


select count(*)
from tmp_facts7,tmp_facts7_a1,tmp_facts7_a2,tmp_facts7_a3,tmp_facts7_a4
where tmp_facts7.a1=tmp_facts7_a1.id and tmp_facts7.a2=tmp_facts7_a2.id and tmp_facts7.a3=tmp_facts7_a3.id and tmp_facts7.a4=tmp_facts7_a4.id
and tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
92

explain analyze
select count(*)
from tmp_facts7,tmp_facts7_a1,tmp_facts7_a2,tmp_facts7_a3,tmp_facts7_a4
where tmp_facts7.a1=tmp_facts7_a1.id and tmp_facts7.a2=tmp_facts7_a2.id and tmp_facts7.a3=tmp_facts7_a3.id and tmp_facts7.a4=tmp_facts7_a4.id
and tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=31225.63..31225.64 rows=1 width=0) (actual time=293.169..293.169 rows=1 loops=1)
-> Nested Loop (cost=26112.94..31225.62 rows=1 width=0) (actual time=280.494..293.127 rows=92 loops=1)
Join Filter: (tmp_facts7.a1 = tmp_facts7_a1.id)
-> Nested Loop (cost=26112.94..31224.49 rows=1 width=4) (actual time=280.427..284.903 rows=1727 loops=1)
-> Nested Loop (cost=0.00..19.81 rows=1 width=12) (actual time=0.074..0.101 rows=1 loops=1)
-> Nested Loop (cost=0.00..11.53 rows=1 width=8) (actual time=0.060..0.081 rows=1 loops=1)
-> Seq Scan on tmp_facts7_a2 (cost=0.00..3.25 rows=1 width=4) (actual time=0.012..0.031 rows=1 loops=1)
Filter: (name = '00'::text)
-> Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4 (cost=0.00..8.27 rows=1 width=4) (actual time=0.046..0.048 rows=1 loops=1)
Index Cond: (tmp_facts7_a4.name = '00'::text)
-> Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3 (cost=0.00..8.27 rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1)
Index Cond: (tmp_facts7_a3.name = '00'::text)
-> Bitmap Heap Scan on tmp_facts7 (cost=26112.94..31179.16 rows=1458 width=16) (actual time=280.349..283.395 rows=1727 loops=1)
Recheck Cond: ((tmp_facts7.a4 = tmp_facts7_a4.id) AND (tmp_facts7.a3 = tmp_facts7_a3.id) AND (tmp_facts7.a2 = tmp_facts7_a2.id))
-> BitmapAnd (cost=26112.94..26112.94 rows=1458 width=0) (actual time=279.869..279.869 rows=0 loops=1)
-> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=85.839..85.839 rows=555316 loops=1)
Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
-> Bitmap Index Scan on tmp_facts7_a3_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=85.048..85.048 rows=554854 loops=1)
Index Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
-> Bitmap Index Scan on tmp_facts7_a2_idx (cost=0.00..8572.57 rows=526325 width=0) (actual time=84.387..84.387 rows=554019 loops=1)
Index Cond: (tmp_facts7.a2 = tmp_facts7_a2.id)
-> Seq Scan on tmp_facts7_a1 (cost=0.00..1.12 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1727)
Filter: (tmp_facts7_a1.name = '00'::text)
Total runtime: 293.460 ms
(24 rows)


Денормализованная таблица (10M записей)

create table tmp_facts7(a1 text,a2 text,a3 text,a4 text);
insert into tmp_facts7(a1,a2,a3,a4)
select populate_varstring(18),populate_varstring(18),populate_varstring(18),populate_varstring(18) from generate_series(1,10000000);

create index tmp_facts7_a1_idx on tmp_facts7(a1);
create index tmp_facts7_a2_idx on tmp_facts7(a2);
create index tmp_facts7_a3_idx on tmp_facts7(a3);
create index tmp_facts7_a4_idx on tmp_facts7(a4);

analyze;

select count(*) from tmp_facts7 where a1='0000000000' and a2='0000000000' and a3='0000000000' and a4='0000000000';
count
-------
83
(1 row)

explain analyze select count(*) from tmp_facts7 where a1='0000000000' and a2='0000000000' and a3='0000000000' and a4='0000000000';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=37537.35..37537.36 rows=1 width=0) (actual time=414.299..414.299 rows=1 loops=1)
-> Bitmap Heap Scan on tmp_facts7 (cost=32288.35..37537.15 rows=77 width=0) (actual time=411.738..414.258 rows=83 loops=1)
Recheck Cond: ((a3 = '0000000000'::text) AND (a4 = '0000000000'::text) AND (a2 = '0000000000'::text))
Filter: (a1 = '0000000000'::text)
-> BitmapAnd (cost=32288.35..32288.35 rows=1458 width=0) (actual time=411.149..411.149 rows=0 loops=1)
-> Bitmap Index Scan on tmp_facts7_a3_idx (cost=0.00..10759.93 rows=526185 width=0) (actual time=123.355..123.355 rows=555966 loops=1)
Index Cond: (a3 = '0000000000'::text)
-> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..10763.93 rows=526185 width=0) (actual time=121.477..121.477 rows=555279 loops=1)
Index Cond: (a4 = '0000000000'::text)
-> Bitmap Index Scan on tmp_facts7_a2_idx (cost=0.00..10763.93 rows=526185 width=0) (actual time=125.600..125.600 rows=554631 loops=1)
Index Cond: (a2 = '0000000000'::text)
Total runtime: 414.743 ms
(12 rows)

SELECT relname, relpages, reltuples FROM pg_class
WHERE NOT relname LIKE 'pg_%' ORDER BY relpages DESC;
relname | relpages | reltuples
-----------------------------------+----------+-------------
tmp_facts7 | 85434 | 9.99752e+06
tmp_facts7_a1_idx | 32364 | 9.99752e+06
tmp_facts7_a4_idx | 32359 | 9.99752e+06
tmp_facts7_a2_idx | 32359 | 9.99752e+06
tmp_facts7_a3_idx | 32357 | 9.99752e+06


Выводы

При денормализации таблицы размер самой таблицы и индексов увеличился примерно на 50% и во столько же раз упала скорость выборки. Заметим, что при тесте все данные помещались в буфере СУБД, при работе с диском можно ожидать худших результатов.

При выборке из нормализованной таблицы со справочниками результаты, в отличии от полученных в версии 8.1 уже не столь отвратительные. Если присмотреться, из каждого справочника выборается единственное значение (равное числу 2), и запрос должен выполняться с той же скоростью, что и при выборке из таблицы фактов с указанными значениями идентификаторов справочников. В случае с join планировщик научился делать такие выборки, но с подзапросами проблема осталась, хотя скорость выборки возросла на порядок, что есть довольно-таки занимательный результат.

Однако в "живой" системе версия 8.3 не дает заметного выигрыша на сложных запросах. По крайней мере, те запросы, что использовались в тестах
PostgreSQL 8.1 vs. SQLite 3.6.20 in the real application
выполняются одинаково в 8.1 и 8.3. Так что все проекты необходимо тестировать на их собственных БД и запросах, иначе возможны очень неприятные неожиданности.

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru