Тестирование PostgreSQL 8.1 на больших таблицах

В очередной раз "ввязался" в дискуссию о разработке больших баз данных на СУБД PostgreSQL. В ходе обсуждения появилась необходимость провести численные измерения скорости выполнения запросов на базах разного масштаба. Так же обнаружилось, что в PostgreSQL 8.3 планировщик работает получше, но принципиальной разницы нет, т.к. того же самого поведения можно добиться и в 8.1.

Выполнено:
Проверил 1 М, 10 М и 100 М записей с тремя индексами (запрос по 4-м полям, но каждый раз использовалось только 3 индекса). Размер выборки во всех случаях был практически идентичный. Для того, чтобы заставить постгрес пользоваться индексами, были подобраны параметры тестового распределения. При 100 М записей постгрес не смог создать индекс после вставки всех записей, требуя больше памяти, потому индексы создавались до вставки. Время измерено в pgadmin, все запросы работали по "горячему кэшу", т.е. запускались несколько раз подряд до стабилизации времени выборки (для 100 М таблицы первые 3 запуска время выполнения было около 40-ка секунд, и только на 4-м запуске кэш "разогрелся").

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

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


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

work_mem = 100000                       # min 64, size in KB
maintenance_work_mem = 2000000          # min 1024, size in KB
shared_buffers = 200000                 # min 16 or max_connections*2, 8KB each


Версия сервера PostgrSQL:

select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.15 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)


Размеры тестовых таблиц и индексов (кол-во 8к блоков):

С индексами по каждому из столбцов:

SELECT relname, relpages, reltuples FROM pg_class
   WHERE NOT relname LIKE 'pg_%' ORDER BY relpages DESC;
              relname               | relpages |  reltuples
------------------------------------+----------+-------------
 tmp_facts8                         |   575323 | 9.00079e+07
 tmp_facts8_a2_idx                  |   279976 | 9.00079e+07
 tmp_facts8_a4_idx                  |   279811 | 9.00079e+07
 tmp_facts8_a1_idx                  |   279740 | 9.00079e+07
 tmp_facts8_a3_idx                  |   279652 | 9.00079e+07
 tmp_facts7                         |    58824 |       1e+07
 tmp_facts7_a1_idx                  |    21899 |       1e+07
 tmp_facts7_a2_idx                  |    21899 |       1e+07
 tmp_facts7_a3_idx                  |    21899 |       1e+07
 tmp_facts7_a4_idx                  |    21899 |       1e+07
 tmp_facts6                         |     5883 |       1e+06
 tmp_facts6_a1_idx                  |     2193 |       1e+06
 tmp_facts6_a2_idx                  |     2193 |       1e+06
 tmp_facts6_a3_idx                  |     2193 |       1e+06
 tmp_facts6_a4_idx                  |     2193 |       1e+06


С индексами по каждому из столбцов и с композитным индексом по всем столбцам:

SELECT relname, relpages, reltuples FROM pg_class
   WHERE NOT relname LIKE 'pg_%' ORDER BY relpages DESC;
              relname               | relpages | reltuples
------------------------------------+----------+-----------
 tmp_facts7                         |    58824 |     1e+07
 tmp_facts7_complex_idx             |    38506 |     1e+07
 tmp_facts7_a1_idx                  |    21899 |     1e+07
 tmp_facts7_a2_idx                  |    21899 |     1e+07
 tmp_facts7_a3_idx                  |    21899 |     1e+07
 tmp_facts7_a4_idx                  |    21899 |     1e+07
 tmp_facts6                         |     5883 |     1e+06
 tmp_facts6_complex_idx             |     3853 |     1e+06
 tmp_facts6_a1_idx                  |     2193 |     1e+06
 tmp_facts6_a2_idx                  |     2193 |     1e+06
 tmp_facts6_a3_idx                  |     2193 |     1e+06
 tmp_facts6_a4_idx                  |     2193 |     1e+06

Для 100 М не удалось провести тест с композитным индексом, см. примечания ниже.

Тест базы 1 М записей.

create table tmp_facts6(a1 int,a2 int,a3 int,a4 int);
insert into tmp_facts6(a1,a2,a3,a4) select (10*random())::int,(10*random())::int,
    (10*random())::int,(10*random())::int from generate_series(1,1000000);

create index tmp_facts6_a1_idx on tmp_facts6(a1);
create index tmp_facts6_a2_idx on tmp_facts6(a2);
create index tmp_facts6_a3_idx on tmp_facts6(a3);
create index tmp_facts6_a4_idx on tmp_facts6(a4);

select count(*) from tmp_facts6 where a1=2 and a2=2 and a3=2 and a4=2;
90
140 ms

explain select * from tmp_facts6 where a1=2 and a2=2 and a3=2 and a4=2;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tmp_facts6  (cost=89.00..93.02 rows=1 width=16)
   Recheck Cond: ((a4 = 2) AND (a3 = 2) AND (a2 = 2))
   Filter: (a1 = 2)
   ->  BitmapAnd  (cost=89.00..89.00 rows=1 width=0)
         ->  Bitmap Index Scan on tmp_facts6_a4_idx  (cost=0.00..29.50 rows=5000 width=0)
               Index Cond: (a4 = 2)
         ->  Bitmap Index Scan on tmp_facts6_a3_idx  (cost=0.00..29.50 rows=5000 width=0)
               Index Cond: (a3 = 2)
         ->  Bitmap Index Scan on tmp_facts6_a2_idx  (cost=0.00..29.50 rows=5000 width=0)
               Index Cond: (a2 = 2)
(10 rows)

select count(*) from tmp_facts6 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
90
233 ms

explain select * from tmp_facts6 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost=0.11..40561.47 rows=68 width=16)
   Hash Cond: ("outer".a1 = "inner"."?column?")
   ->  Hash IN Join  (cost=0.09..40557.01 rows=751 width=16)
         Hash Cond: ("outer".a3 = "inner"."?column?")
         ->  Hash IN Join  (cost=0.07..40508.16 rows=8264 width=16)
               Hash Cond: ("outer".a4 = "inner"."?column?")
               ->  Merge Join  (cost=0.04..39970.95 rows=90909 width=16)
                     Merge Cond: ("outer"."?column?" = "inner".a2)
                     ->  Sort  (cost=0.04..0.05 rows=1 width=4)
                           Sort Key: "IN_subquery"."?column?"
                           ->  HashAggregate  (cost=0.02..0.03 rows=1 width=4)
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     ->  Index Scan using tmp_facts6_a2_idx on tmp_facts6  (cost=0.00..36561.81 rows=1000000 width=16)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Hash  (cost=0.02..0.02 rows=1 width=4)
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)

select count(*)
from tmp_facts6 
join (select 2 as a1) as x1 on x1.a1=tmp_facts6.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts6.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts6.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts6.a4;
90
205 ms

explain select count(*)
from tmp_facts6 
join (select 2 as a1) as x1 on x1.a1=tmp_facts6.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts6.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts6.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts6.a4;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=40561.63..40561.64 rows=1 width=0)
   ->  Hash Join  (cost=0.10..40561.46 rows=68 width=0)
         Hash Cond: ("outer".a1 = "inner".a1)
         ->  Hash Join  (cost=0.08..40557.00 rows=751 width=4)
               Hash Cond: ("outer".a3 = "inner".a3)
               ->  Hash Join  (cost=0.05..40508.15 rows=8264 width=8)
                     Hash Cond: ("outer".a4 = "inner".a4)
                     ->  Merge Join  (cost=0.03..39970.94 rows=90909 width=12)
                           Merge Cond: ("outer".a2 = "inner".a2)
                           ->  Sort  (cost=0.03..0.04 rows=1 width=4)
                                 Sort Key: x2.a2
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           ->  Index Scan using tmp_facts6_a2_idx on tmp_facts6  (cost=0.00..36561.81 rows=1000000 width=16)
                     ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)

С композитным индексом:

create index tmp_facts6_complex_idx on tmp_facts6(a1,a2,a3,a4);

select count(*) from tmp_facts6 where a1=2 and a2=2 and a3=2 and a4=2;
90
35 ms

explain select count(*) from tmp_facts6 where a1=2 and a2=2 and a3=2 and a4=2;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate  (cost=370.14..370.15 rows=1 width=0)
   ->  Bitmap Heap Scan on tmp_facts6  (cost=3.12..369.88 rows=102 width=0)
         Recheck Cond: ((a1 = 2) AND (a2 = 2) AND (a3 = 2) AND (a4 = 2))
         ->  Bitmap Index Scan on tmp_facts6_complex_idx  (cost=0.00..3.12 rows=102 width=0)
               Index Cond: ((a1 = 2) AND (a2 = 2) AND (a3 = 2) AND (a4 = 2))
(5 rows)

select count(*)
from tmp_facts6 
join (select 2 as a1) as x1 on x1.a1=tmp_facts6.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts6.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts6.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts6.a4;
90
220 ms

explain select count(*)
from tmp_facts6 
join (select 2 as a1) as x1 on x1.a1=tmp_facts6.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts6.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts6.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts6.a4;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=40561.63..40561.64 rows=1 width=0)
   ->  Hash Join  (cost=0.10..40561.46 rows=68 width=0)
         Hash Cond: ("outer".a1 = "inner".a1)
         ->  Hash Join  (cost=0.08..40557.00 rows=751 width=4)
               Hash Cond: ("outer".a3 = "inner".a3)
               ->  Hash Join  (cost=0.05..40508.15 rows=8264 width=8)
                     Hash Cond: ("outer".a4 = "inner".a4)
                     ->  Merge Join  (cost=0.03..39970.94 rows=90909 width=12)
                           Merge Cond: ("outer".a2 = "inner".a2)
                           ->  Sort  (cost=0.03..0.04 rows=1 width=4)
                                 Sort Key: x2.a2
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           ->  Index Scan using tmp_facts6_a2_idx on tmp_facts6  (cost=0.00..36561.81 rows=1000000 width=16)
                     ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)



Тест базы 10 М записей.

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);

select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
94
370 ms

explain 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=858.50..862.52 rows=1 width=16)
   Recheck Cond: ((a4 = 2) AND (a3 = 2) AND (a2 = 2))
   Filter: (a1 = 2)
   ->  BitmapAnd  (cost=858.50..858.50 rows=1 width=0)
         ->  Bitmap Index Scan on tmp_facts7_a4_idx  (cost=0.00..286.00 rows=50000 width=0)
               Index Cond: (a4 = 2)
         ->  Bitmap Index Scan on tmp_facts7_a3_idx  (cost=0.00..286.00 rows=50000 width=0)
               Index Cond: (a3 = 2)
         ->  Bitmap Index Scan on tmp_facts7_a2_idx  (cost=0.00..286.00 rows=50000 width=0)
               Index Cond: (a2 = 2)
(10 rows)

select count(*) from tmp_facts7 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
94
1140 ms

explain select * from tmp_facts7 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost=0.10..403957.90 rows=77 width=16)
   Hash Cond: ("outer".a1 = "inner"."?column?")
   ->  Hash IN Join  (cost=0.08..403949.81 rows=1458 width=16)
         Hash Cond: ("outer".a2 = "inner"."?column?")
         ->  Hash IN Join  (cost=0.05..403796.71 rows=27701 width=16)
               Hash Cond: ("outer".a3 = "inner"."?column?")
               ->  Merge IN Join  (cost=0.03..400888.09 rows=526316 width=16)
                     Merge Cond: ("outer".a4 = "inner"."?column?")
                     ->  Index Scan using tmp_facts7_a4_idx on tmp_facts7  (cost=0.00..366677.53 rows=10000000 width=16)
                     ->  Sort  (cost=0.03..0.04 rows=1 width=4)
                           Sort Key: "IN_subquery"."?column?"
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Hash  (cost=0.02..0.02 rows=1 width=4)
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
(18 rows)

select count(*)
from tmp_facts7
join (select 2 as a1) as x1 on x1.a1=tmp_facts7.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts7.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts7.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts7.a4;
94
1106 ms

explain select count(*)
from tmp_facts7
join (select 2 as a1) as x1 on x1.a1=tmp_facts7.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts7.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts7.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts7.a4;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=400010.73..400010.74 rows=1 width=0)
   ->  Hash Join  (cost=0.10..400010.53 rows=77 width=0)
         Hash Cond: ("outer".a1 = "inner".a1)
         ->  Hash Join  (cost=0.08..400002.45 rows=1458 width=4)
               Hash Cond: ("outer".a2 = "inner".a2)
               ->  Hash Join  (cost=0.05..399849.34 rows=27701 width=8)
                     Hash Cond: ("outer".a3 = "inner".a3)
                     ->  Merge Join  (cost=0.03..396940.73 rows=526316 width=12)
                           Merge Cond: ("outer".a4 = "inner".a4)
                           ->  Sort  (cost=0.03..0.04 rows=1 width=4)
                                 Sort Key: x4.a4
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           ->  Index Scan using tmp_facts7_a4_idx on tmp_facts7  (cost=0.00..366677.53 rows=10000000 width=16)
                     ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)

С композитным индексом:

create index tmp_facts7_complex_idx on tmp_facts7(a1,a2,a3,a4);

select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
94
54 ms

explain select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=373.44..373.45 rows=1 width=0)
   ->  Bitmap Heap Scan on tmp_facts7  (cost=3.04..373.20 rows=95 width=0)
         Recheck Cond: ((a1 = 2) AND (a2 = 2) AND (a3 = 2) AND (a4 = 2))
         ->  Bitmap Index Scan on tmp_facts7_complex_idx  (cost=0.00..3.04 rows=95 width=0)
               Index Cond: ((a1 = 2) AND (a2 = 2) AND (a3 = 2) AND (a4 = 2))
(5 rows)


select count(*)
from tmp_facts7
join (select 2 as a1) as x1 on x1.a1=tmp_facts7.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts7.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts7.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts7.a4;
94
1150 ms

explain select count(*)
from tmp_facts7
join (select 2 as a1) as x1 on x1.a1=tmp_facts7.a1
join (select 2 as a2) as x2 on x2.a2=tmp_facts7.a2
join (select 2 as a3) as x3 on x3.a3=tmp_facts7.a3
join (select 2 as a4) as x4 on x4.a4=tmp_facts7.a4;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=400010.73..400010.74 rows=1 width=0)
   ->  Hash Join  (cost=0.10..400010.53 rows=77 width=0)
         Hash Cond: ("outer".a1 = "inner".a1)
         ->  Hash Join  (cost=0.08..400002.45 rows=1458 width=4)
               Hash Cond: ("outer".a2 = "inner".a2)
               ->  Hash Join  (cost=0.05..399849.34 rows=27701 width=8)
                     Hash Cond: ("outer".a3 = "inner".a3)
                     ->  Merge Join  (cost=0.03..396940.73 rows=526316 width=12)
                           Merge Cond: ("outer".a4 = "inner".a4)
                           ->  Sort  (cost=0.03..0.04 rows=1 width=4)
                                 Sort Key: x4.a4
                                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           ->  Index Scan using tmp_facts7_a4_idx on tmp_facts7  (cost=0.00..366677.53 rows=10000000 width=16)
                     ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)


Тест базы 100 М записей.

drop table tmp_facts8;
create table tmp_facts8(a1 int,a2 int,a3 int,a4 int);
create index tmp_facts8_a1_idx on tmp_facts8(a1);
create index tmp_facts8_a2_idx on tmp_facts8(a2);
create index tmp_facts8_a3_idx on tmp_facts8(a3);
create index tmp_facts8_a4_idx on tmp_facts8(a4);
insert into tmp_facts8(a1,a2,a3,a4) select (33*random())::int,(33*random())::int,
    (33*random())::int,(33*random())::int from generate_series(1,10000000);
... выполняем 10 раз для вставки 100 М записей


select count(*) from tmp_facts8 where a1=2 and a2=2 and a3=2 and a4=2;
80
2260 ms

explain select * from tmp_facts8 where a1=2 and a2=2 and a3=2 and a4=2;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tmp_facts8  (cost=55213.58..64940.47 rows=78 width=16)
   Recheck Cond: ((a1 = 2) AND (a3 = 2) AND (a2 = 2))
   Filter: (a4 = 2)
   ->  BitmapAnd  (cost=55213.58..55213.58 rows=2550 width=0)
         ->  Bitmap Index Scan on tmp_facts8_a1_idx  (cost=0.00..18275.05 rows=2765444 width=0)
               Index Cond: (a1 = 2)
         ->  Bitmap Index Scan on tmp_facts8_a3_idx  (cost=0.00..18432.87 rows=2789678 width=0)
               Index Cond: (a3 = 2)
         ->  Bitmap Index Scan on tmp_facts8_a2_idx  (cost=0.00..18505.16 rows=2799187 width=0)
               Index Cond: (a2 = 2)
(10 rows)

select count(*) from tmp_facts8 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
80
7419 ms

explain select * from tmp_facts8 where a1 in (select 2) and a2 in (select 2) 
    and a3 in (select 2) and a4 in (select 2);
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost=100017884.57..100681784.99 rows=69 width=16)
   Hash Cond: ("outer".a1 = "inner"."?column?")
   ->  Hash IN Join  (cost=100017884.55..100681772.57 rows=2341 width=16)
         Hash Cond: ("outer".a2 = "inner"."?column?")
         ->  Hash IN Join  (cost=100017884.52..100681351.09 rows=79609 width=16)
               Hash Cond: ("outer".a4 = "inner"."?column?")
               ->  Nested Loop  (cost=100017884.50..100667021.44 rows=2706708 width=16)
                     ->  HashAggregate  (cost=0.02..0.03 rows=1 width=4)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     ->  Bitmap Heap Scan on tmp_facts8  (cost=17884.48..633187.56 rows=2706708 width=16)
                           Recheck Cond: (tmp_facts8.a3 = "outer"."?column?")
                           ->  Bitmap Index Scan on tmp_facts8_a3_idx  (cost=0.00..17884.48 rows=2706708 width=0)
                                 Index Cond: (tmp_facts8.a3 = "outer"."?column?")
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
         ->  Hash  (cost=0.02..0.02 rows=1 width=4)
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Hash  (cost=0.02..0.02 rows=1 width=4)
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
(19 rows)



Обсуждение:

Композитный индекс для таблицы 100 М записей протестировать не удалось - создать индекс по заполненной таблицы постгрес не смог (говорит, памяти не хватает), а заполнение индексированной таблицы производится очень медленно, притом скорость заполнения падает; 10 М записей были вставлены за время порядка часа, а вставки следущего набора из 1 М мне уже не удалось дождаться.

Заметно, что планировщик с подзапросами работает плохо, в разы замедляя даже простейший запрос. На мелких базах это не так существенно, но на больших создает определенные проблемы. Следовательно, нужно избегать подзапросов, когда это возможно. При этом и JOIN-ов следует избегать, т.к. с ними возникают точно такие же проблемы. Ну, кому-то достаточно и выборок из одной таблицы, что чаще всего и делают на больших БД - для OLTP и этого бывает достаточно. Только сомнительна необходимость в PostgreSQL для подобных задач, когда есть SQLite, BerkeleyDB (чтобы делать выборку из одной таблицы поддержка SQL не сильно и нужна) и другие намного более эффективные решения.

Грубая прикидка показывает, что деградация производительности при росте размера базы примерно пропорциональна корню квадратному из отношения размеров баз до тех пор, пока база помещается в ОЗУ. После этого деградация становится линейно зависящей от роста размера базы. Как очевидное следствие, работа с базами, не помещающимися в shared_memory, проблематична.

Comments

Unknown said…
Было бы хорошо ещё написать: объём оперативной памяти на сервере и объём базы.

Без этого, конечно интересно, но ИМХО недостаточно показательно.
September 12, 2009 at 11:42 AM
Алексей Печников said…
Информацию об ОЗУ и процессоре сервера добавил. А размер базы легко увидеть из размеров таблиц и индексов, которые приведены под заголовком "Размеры тестовых таблиц и индексов (кол-во 8к блоков)".
September 12, 2009 at 3:43 PM
Post a Comment

Popular posts from this blog

Открытый софт для научных расчетов

Задачки у меня бывают разные, кратенькое описание того, что может потребоваться я приводил здесь: Софт для спектрального анализа/численного моделирования/etc. Дело происходит в Linux Debian, соответственно, интересует софт, который устанавливается из стандартного репозитория (что подразумевает опенсорсность).  Для начала я протестировал двумерное Фурье преобразование (прямое и обратное) на широко известном тестовом изображении Lenna. Заодно в процессе поиска нужных функций увидел заметки об истории этого изображения  Просто Лена  и  Обработка изображений: кто такая Лена Выбор софта для такой типичной задачки вполне себе наличествует: Scientific Computing with Free software on GNU/Linux HOWTO Пакеты прикладных программ Инструменты визуализации данных для Linux На практике же оказалось не так радужно, как хотелось бы. Из того, что есть в дебиане, работает "из коробки" только пакет octave . Зато для него можно установить множество расширений - нап
Read more

Счетчики в SQLite

Недавно ко мне обратился прикладной программист, работающий с моим фрэймворком для AOLServer и SQLite с вопросом, как ему сделать счетчик записей, возвращенных запросом. Разумеется, можно создать временную таблицу или забиндить тиклевскую функцию, но есть и более простой способ - использовать написанный мною модуль key или tempkey (отличаются только тем, что последний использует временную таблицу, таким образом, при хранении временных объектов в ОЗУ не требуется запись на диск). Модуль key создает при инициализации таблицу keys (а модуль tempkey - временную таблицу tempkeys), в которой и будут храниться все ключи; таким образом, можно использовать практически неограниченное количество ключей без ущерба для производительности и не нуждаясь в большом количестве ОЗУ. Рассмотрим на примере неименованного временного счетчика: create table colors(name text); insert into colors values ('Red'); insert into colors values ('Green'); insert into colors values ('Blue'); sel
Read more

Кольцевые структуры в геофизике

Опубликовал теоретическое обоснование и результаты численной модели для появления кольцевых структур в гравитационном поле. Поскольку найденные структуры существуют после применения фильтра высоких частот к полю, из этого следуют разные интересные вещи. Например, эти же структуры проявляются в цифровой модели рельефа и на космических снимках. Таким образом, подтверждаются практические работы Ю.И. Фивенского о существовании разномасштабных кольцевых структур, их видимости на рельефе и на снимках и пропорциональности их радиусов с глубиной залегания вызывающих их источников таких, как ловушки углеводородов, с коэффициентом около 0.7. Также можно предположить, что снежный покров работает как фильтр высокой частоты, что и позволяет лучше проявиться паттернам на поверхности - для тех конфигураций источников, которые иначе без фильтрации гравитационного поля не различимы. Ссылка на гитхаб-репозиторий со статьями: https://github.com/mobigroup/articles/  Первые две работы вводные, интересны
Read more