ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация.
10 сообщений из 10, страница 1 из 1
Оптимизация.
    #39005790
Rhim
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rhim
Гость
Привет всем.

Стенд
CPU: 1 x Intel(R) Xeon(R) CPU E3-1270 v3 @ 3.50GHz (Cores 4, Threads 8)
RAM: 4 x 8GB Kingston 9965525-026.A00LF (DIMM Synchronous 1333 MHz (0.8 ns))
SSD: 2 x SAMSUNG MZ7WD480 480GB RAID1 SOFT

Есть таблица test
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
\d test
                                Table "public.test"
 Column  |            Type             |                 Modifiers
---------+-----------------------------+--------------------------------------------
 r       | character varying(36)       | not null
 ct1     | smallint                    | not null default 0
 created | timestamp without time zone | default now()
 m1      | character varying(128)      |
 m2      | character varying(128)      |
 m3      | character varying(128)      |
 cs1     | numeric(10,2)               |
 cs2     | numeric(10,2)               |
 geo     | character varying(4)        | not null default 'NONE'::character varying
 o_id    | smallint                    |
 p2_id   | smallint                    |
Indexes:
    "test_r_key" UNIQUE CONSTRAINT, btree (r)
    "idx_idx_created_o_ct2" btree (o_id, date(created))


SELECT TO_CHAR(COUNT(*), '999 999 999') AS cnt FROM test;
     cnt
--------------
   73 879 555



Минимальный срез статистики 1 день.
статистику просматривать возможно по любому сочетанию полей m1,m2,m3,geo и сортировкой по полям (cs1|cs2).

Отношения количество записей сгруппированных по o_id и уникальности записей
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
o_id |     allr     |   unqr_m1    |   unqr_m2    |   unqr_m3
------+--------------+--------------+--------------+--------------
    1 |    6 181 174 |    6 180 934 |    6 175 276 |    3 972 853
    2 |    5 330 107 |    5 330 107 |    5 325 964 |    3 625 367
    3 |    7 475 120 |    7 475 114 |    7 466 778 |    4 420 233
    4 |    7 473 794 |    7 473 791 |    7 465 680 |    4 421 455
    5 |    7 474 849 |    7 474 849 |    7 466 620 |    4 421 811
    6 |    7 472 560 |    7 472 560 |    7 464 337 |    4 421 250
    7 |    7 479 182 |    7 479 182 |    7 471 011 |    4 421 988
    8 |    7 473 756 |    7 473 756 |    7 465 645 |    4 420 054
    9 |    7 476 075 |    7 476 072 |    7 467 737 |    4 422 696
   10 |    7 476 266 |    7 476 266 |    7 468 113 |    4 420 313
   23 |    1 283 389 |    1 283 389 |    1 283 110 |    1 163 275
   24 |    1 283 281 |    1 283 280 |    1 283 024 |    1 163 742



План запроса с созданием MV
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
SET work_mem='1500MB';
SET

EXPLAIN (ANALYZE, BUFFERS) CREATE MATERIALIZED VIEW mv_test AS SELECT ROW_NUMBER() OVER() AS id, * FROM (SELECT m2, m3, geo, SUM(cs2) AS cnt FROM test WHERE (DATE(created) BETWEEN '2015-06-10' AND  '2015-07-10') AND o_id = 1 GROUP BY m2, m3, geo ORDER BY cnt DESC) AS t1;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2205712.82..2360285.07 rows=6182890 width=47) (actual time=10066.967..11726.691 rows=6180939 loops=1)
   Buffers: shared read=897991
   ->  Sort  (cost=2205712.82..2221170.05 rows=6182890 width=18) (actual time=10066.961..10417.891 rows=6180939 loops=1)
         Sort Key: (sum(test.cs2))
         Sort Method: quicksort  Memory: 679494kB
         Buffers: shared read=897991
         ->  HashAggregate  (cost=1431001.34..1508287.47 rows=6182890 width=18) (actual time=6726.174..8848.287 rows=6180939 loops=1)
               Group Key: test.m2, test.m3, test.geo
               Buffers: shared read=897991
               ->  Bitmap Heap Scan on test  (cost=164852.42..1369172.44 rows=6182890 width=18) (actual time=952.663..3534.270 rows=6181174 loops=1)
                     Recheck Cond: ((o_id = 1) AND (date(created) >= '2015-06-10'::date) AND (date(created) <= '2015-07-10'::date))
                     Heap Blocks: exact=876612
                     Buffers: shared read=897991
                     ->  Bitmap Index Scan on idx_idx_created_o_ct2  (cost=0.00..163306.70 rows=6182890 width=0) (actual time=727.321..727.321 rows=6181174 loops=1)
                           Index Cond: ((o_id = 1) AND (date(created) >= '2015-06-10'::date) AND (date(created) <= '2015-07-10'::date))
                           Buffers: shared read=21379
 Planning time: 0.323 ms
 Execution time: 14217.656 ms



Без MV
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
EXPLAIN (ANALYZE,BUFFERS)  SELECT ROW_NUMBER() OVER() AS id, * FROM (SELECT m2, m3, geo, SUM(cs2) AS cnt FROM test WHERE (DATE(created) BETWEEN '2015-06-10' AND  '2015-07-10') AND o_id = 1 GROUP BY m2, m3, geo ORDER BY cnt DESC) AS t1;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2205712.82..2360285.07 rows=6182890 width=47) (actual time=9943.879..11500.149 rows=6180939 loops=1)
   Buffers: shared read=897991
   ->  Sort  (cost=2205712.82..2221170.05 rows=6182890 width=18) (actual time=9943.873..10255.053 rows=6180939 loops=1)
         Sort Key: (sum(test.cs2))
         Sort Method: quicksort  Memory: 679494kB
         Buffers: shared read=897991
         ->  HashAggregate  (cost=1431001.34..1508287.47 rows=6182890 width=18) (actual time=6613.830..8725.360 rows=6180939 loops=1)
               Group Key: test.m2, test.m3, test.geo
               Buffers: shared read=897991
               ->  Bitmap Heap Scan on test  (cost=164852.42..1369172.44 rows=6182890 width=18) (actual time=943.323..3518.391 rows=6181174 loops=1)
                     Recheck Cond: ((o_id = 1) AND (date(created) >= '2015-06-10'::date) AND (date(created) <= '2015-07-10'::date))
                     Heap Blocks: exact=876612
                     Buffers: shared read=897991
                     ->  Bitmap Index Scan on idx_idx_created_o_ct2  (cost=0.00..163306.70 rows=6182890 width=0) (actual time=718.155..718.155 rows=6181174 loops=1)
                           Index Cond: ((o_id = 1) AND (date(created) >= '2015-06-10'::date) AND (date(created) <= '2015-07-10'::date))
                           Buffers: shared read=21379
 Planning time: 0.311 ms
 Execution time: 11773.253 ms




На оригинальной таблице висит триггер которым группируется статистика без m1,m2,m3.
В таблицу в среднем происходит 100 инсертов в сек + 10 апдейтов в сек.

Желание ускорить выборку по m1,m2,m3 поэтому вопросы
1. Каким образом возможно ускорить запрос(определенное хранение данных)?
2. Возможно денормализация данных неверный шаг? Но это ускоряет вставку, что так же очень важно.
3. Возможно не правильно выбран инструмент для задачи?
...
Рейтинг: 0 / 0
13.07.2015, 12:13
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005801
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Rhim,

partitioned table ?
...
Рейтинг: 0 / 0
13.07.2015, 12:17
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005821
Rhim
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rhim
Гость
Щукина Анна,

Оригинальная таблица партицированная по месяцам, но это не спасает от проблемы, добавляется только Append на пол сек.
...
Рейтинг: 0 / 0
13.07.2015, 12:28
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005822
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
RhimПривет всем.
Желание ускорить выборку по m1,m2,m3 поэтому вопросы
1. Каким образом возможно ускорить запрос(определенное хранение данных)?
2. Возможно денормализация данных неверный шаг? Но это ускоряет вставку, что так же очень важно.
3. Возможно не правильно выбран инструмент для задачи?

Никаким иструментом вы не ускорите получение 6M строк ни от чего.
Есть фиксированный overhead на обработку 1 строки в базе и на передачу ее по сети.
У вас уже быстро все работает на самом деле.
Приведите реалистичный пример чего вам надо ускорить не отдающий 6M строк.
Если же надо именно 6M строк то объясните зачем они вам и что дальше с этим данными происходит.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
13.07.2015, 12:29
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005857
Rhim
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rhim
Гость
Maxim Boguk,

Нужно сгруппировать к примеру m2,m3 и показать статистику по этим данным отсортированными по окончательным показателям SUM(cs2), дальше с этим срезом работаю постранично.
...
Рейтинг: 0 / 0
13.07.2015, 12:46
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005909
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
RhimMaxim Boguk,

Нужно сгруппировать к примеру m2,m3 и показать статистику по этим данным отсортированными по окончательным показателям SUM(cs2), дальше с этим срезом работаю постранично.

Тогда вам надо анализировать варианты запросов с Limit/offset.
Если вы всем 6M строк ответа тянете на клиент - то толку от этого не будет никогда и никак.

PS: я не совсем понял смысл приводить запрос генерации matview ? тот же запрос что и обычный + запись в таблицу.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
13.07.2015, 13:28
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005948
Rhim
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rhim
Гость
Maxim Boguk,

В MV складываются уже готовые данные и постраничное отображение статистики будет моментальным, к примеру WHERE id > 100 AND d <= 200.
Каждый раз обрабатывать 6 млн строк накладно, то есть подготовление данных занимает 14 секунд.
Проблемы начнутся когда кто то захочет посмотреть за больший период статистику к примеру 30 млн будут подготавливаться почти минуту с выделением что то около 6GB памяти.

Пытаюсь понять как можно хранить уже подготовленные данные, что бы избежать или ускорить запрос
с учетом что GROUP BY может быть любое сочетание m1,m2,m3,geo
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT ROW_NUMBER() OVER() AS id, * 
FROM (
            SELECT m2, m3, geo, SUM(cs2) AS cnt 
            FROM test 
            WHERE (DATE(created) BETWEEN '2015-06-10' AND  '2015-07-10') AND o_id = 1 
            GROUP BY m2, m3, geo 
            ORDER BY cnt DESC
) AS t1;
...
Рейтинг: 0 / 0
13.07.2015, 14:06
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005967
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
RhimMaxim Boguk,
Пытаюсь понять как можно хранить уже подготовленные данные, что бы избежать или ускорить запрос
с учетом что GROUP BY может быть любое сочетание m1,m2,m3,geo


Всего 15 вариантов сочетаний.
Вот и предпросчитать заранее все 15 сочетаний в group by и далее по ним уж генерировать.

PS: но вообще генерация matview штука очень тяжелая и дорогая. И делать для каждой новой страниц новый matview будет очень дорого.

PPS: в общем случае подобные задачи не решаются быстро. На создание эффективных OLAP систем вообще ресурсов убито очень много, без особого успеха впрочем.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
13.07.2015, 14:17
| Ответить | Цитировать | Написать  
Оптимизация.
    #39005972
Rhim
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rhim
Гость
Maxim Boguk,
нет, MV генерируется один раз, затем работа идет с матвьюхой(срезом данных). До момента обновления данных или выбора другого сочетания.

Максим я Вас понял, спасибо огромное за консультацию.
...
Рейтинг: 0 / 0
13.07.2015, 14:23
| Ответить | Цитировать | Написать  
Оптимизация.
    #39006860
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
RhimMaxim Boguk,
нет, MV генерируется один раз, затем работа идет с матвьюхой(срезом данных). До момента обновления данных или выбора другого сочетания.

Максим я Вас понял, спасибо огромное за консультацию.
эмуляция олапа.
Делай так. Сначала сделай уже сгруппированную витрину (или матвью) с группой m1,m2,m3,geo и sum.
После чего если тебе надо группа: m1, m2, sum или m2,geo, sum - то ты группируй уже по первой таблице!
...
Рейтинг: 0 / 0
14.07.2015, 12:40
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=108&tid=1997885]:
 0ms
get settings:
 11ms
get forum list:
 18ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 35ms
get topic data:
 11ms
get forum data:
 4ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 252ms
total:  386ms
созд. / загр.: 386ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]