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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id,
 sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl.rch_qty) AS rch_qty, sum(lbl.rsrv_qty) AS rsrv_qty, 
 min(lbl.date_qty) AS date_qty, min(lbl.date_rchqty) AS date_rchqty, max(lbl.sequence) AS sequence, max((lbl.state)::text) AS state, 
 max(lbl.comment) AS comment, array_to_string(array_agg(lbl.id), ','::text) AS mat_ids 
 FROM tbl1 lb 
 	JOIN tbl2 lbl ON lb.id = lbl.laborder_id
    JOIN tbl2 prd ON lbl.mt_id = prd.id
 GROUP BY lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id;



Которая выполняется 2658.875 ms
План запроса work_mem = 1 mb :

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
GroupAggregate  (cost=105065.17..127333.04 rows=356286 width=85) (actual time=1376.793..2636.413 rows=202404 loops=1)
   ->  Sort  (cost=105065.17..105955.88 rows=356286 width=85) (actual time=1376.753..1491.741 rows=356286 loops=1)
         Sort Key: lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id
         Sort Method: external merge  Disk: 27336kB
         ->  Hash Join  (cost=92.77..38106.83 rows=356286 width=85) (actual time=1.498..826.742 rows=356286 loops=1)
               Hash Cond: (lbl.mt_id = prd.id)
               ->  Merge Join  (cost=0.81..32670.59 rows=356286 width=85) (actual time=0.035..671.049 rows=356286 loops=1)
                     Merge Cond: (lb.id = lbl.lorder_id)
                     ->  Index Scan using tbl1_pkey on tbl1 lb  (cost=0.00..10545.07 rows=186136 width=16) (actualtime=0.015..105.777 rows=186136 loops=1)
                     ->  Index Scan using tbl2_lorder_id_index on tbl2 lbl  (cost=0.00..17206.72 rows=356286 width=77) (actual time=0.009..219.785 rows=356286 loops=1)
               ->  Hash  (cost=64.82..64.82 rows=2171 width=4) (actual time=1.441..1.441 rows=2171 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 77kB
                     ->  Index Only Scan using tbl2_pkey on tbl2 prd  (cost=0.00..64.82 rows=2171 width=4) (actual time=0.089..0.659 rows=2171 loops=1)
                           Heap Fetches: 0
 Total runtime: 2658.875 ms
(15 rows)



work_mem = 200mb

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
  GroupAggregate  (cost=63667.22..85935.09 rows=356286 width=85) (actual time=1057.591..2247.091 rows=202404 loops=1)
   ->  Sort  (cost=63667.22..64557.93 rows=356286 width=85) (actual time=1057.553..1105.126 rows=356286 loops=1)
         Sort Key: lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id
         Sort Method: quicksort  Memory: 62432kB
         ->  Hash Join  (cost=8321.01..30812.88 rows=356286 width=85) (actual time=122.309..673.887 rows=356286 loops=1)
               Hash Cond: (lbl.mt_id = prd.id)
               ->  Hash Join  (cost=8229.06..25376.64 rows=356286 width=85) (actual time=121.224..534.207 rows=356286 loops=1)
                     Hash Cond: (lbl.lorder_id = lb.id)
                     ->  Seq Scan on tbl2 lbl  (cost=0.00..10021.86 rows=356286 width=77) (actual time=0.003..70.330 rows=356286 loops=1)
                     ->  Hash  (cost=5902.36..5902.36 rows=186136 width=16) (actual time=121.065..121.065 rows=186136 loops=1)
                           Buckets: 32768  Batches: 1  Memory Usage: 8725kB
                           ->  Seq Scan on tbl1 lb  (cost=0.00..5902.36 rows=186136 width=16) (actual time=0.039..71.138 rows=186136 loops=1)
               ->  Hash  (cost=64.82..64.82 rows=2171 width=4) (actual time=1.075..1.075 rows=2171 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 77kB
                     ->  Index Only Scan using tbl2_pkey on tbl2 prd  (cost=0.00..64.82 rows=2171 width=4) (actual time=0.021..0.537 rows=2171 loops=1)
                           Heap Fetches: 0
 Total runtime: 2268.394 ms
(17 rows)



К этому представлению делается запрос :

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT view1."rsrv_qty",view1."comment",view1."rch_qty",view1."sequence",view1."order_id",
view1."qty",view1."uom_id",view1."categ_id",view1."mat_ids",view1."date_rchqty",
view1."par_id",view1."mt_id",view1."state",view1."orig_qty",view1."tracking_id",
view1."lbl_id",view1."date_qty",view1.id 
FROM "view1" 
WHERE view1.id IN (271053, 271054) 
ORDER BY id



Котрый выполняется Total runtime: 2277.899 ms
Посоветуйте как ускорить.
...
Рейтинг: 0 / 0
03.03.2016, 14:21
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39184971
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
NewBie77Доброго веремени суток форумчане !
Имеется представление с запросом :

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id,
 sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl.rch_qty) AS rch_qty, sum(lbl.rsrv_qty) AS rsrv_qty, 
 min(lbl.date_qty) AS date_qty, min(lbl.date_rchqty) AS date_rchqty, max(lbl.sequence) AS sequence, max((lbl.state)::text) AS state, 
 max(lbl.comment) AS comment, array_to_string(array_agg(lbl.id), ','::text) AS mat_ids 
 FROM tbl1 lb 
 	JOIN tbl2 lbl ON lb.id = lbl.laborder_id
    JOIN tbl2 prd ON lbl.mt_id = prd.id
 GROUP BY lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id;



Которая выполняется 2658.875 ms
План запроса work_mem = 1 mb :

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
GroupAggregate  (cost=105065.17..127333.04 rows=356286 width=85) (actual time=1376.793..2636.413 rows=202404 loops=1)
   ->  Sort  (cost=105065.17..105955.88 rows=356286 width=85) (actual time=1376.753..1491.741 rows=356286 loops=1)
         Sort Key: lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id
         Sort Method: external merge  Disk: 27336kB
         ->  Hash Join  (cost=92.77..38106.83 rows=356286 width=85) (actual time=1.498..826.742 rows=356286 loops=1)
               Hash Cond: (lbl.mt_id = prd.id)
               ->  Merge Join  (cost=0.81..32670.59 rows=356286 width=85) (actual time=0.035..671.049 rows=356286 loops=1)
                     Merge Cond: (lb.id = lbl.lorder_id)
                     ->  Index Scan using tbl1_pkey on tbl1 lb  (cost=0.00..10545.07 rows=186136 width=16) (actualtime=0.015..105.777 rows=186136 loops=1)
                     ->  Index Scan using tbl2_lorder_id_index on tbl2 lbl  (cost=0.00..17206.72 rows=356286 width=77) (actual time=0.009..219.785 rows=356286 loops=1)
               ->  Hash  (cost=64.82..64.82 rows=2171 width=4) (actual time=1.441..1.441 rows=2171 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 77kB
                     ->  Index Only Scan using tbl2_pkey on tbl2 prd  (cost=0.00..64.82 rows=2171 width=4) (actual time=0.089..0.659 rows=2171 loops=1)
                           Heap Fetches: 0
 Total runtime: 2658.875 ms
(15 rows)



work_mem = 200mb

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
  GroupAggregate  (cost=63667.22..85935.09 rows=356286 width=85) (actual time=1057.591..2247.091 rows=202404 loops=1)
   ->  Sort  (cost=63667.22..64557.93 rows=356286 width=85) (actual time=1057.553..1105.126 rows=356286 loops=1)
         Sort Key: lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id
         Sort Method: quicksort  Memory: 62432kB
         ->  Hash Join  (cost=8321.01..30812.88 rows=356286 width=85) (actual time=122.309..673.887 rows=356286 loops=1)
               Hash Cond: (lbl.mt_id = prd.id)
               ->  Hash Join  (cost=8229.06..25376.64 rows=356286 width=85) (actual time=121.224..534.207 rows=356286 loops=1)
                     Hash Cond: (lbl.lorder_id = lb.id)
                     ->  Seq Scan on tbl2 lbl  (cost=0.00..10021.86 rows=356286 width=77) (actual time=0.003..70.330 rows=356286 loops=1)
                     ->  Hash  (cost=5902.36..5902.36 rows=186136 width=16) (actual time=121.065..121.065 rows=186136 loops=1)
                           Buckets: 32768  Batches: 1  Memory Usage: 8725kB
                           ->  Seq Scan on tbl1 lb  (cost=0.00..5902.36 rows=186136 width=16) (actual time=0.039..71.138 rows=186136 loops=1)
               ->  Hash  (cost=64.82..64.82 rows=2171 width=4) (actual time=1.075..1.075 rows=2171 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 77kB
                     ->  Index Only Scan using tbl2_pkey on tbl2 prd  (cost=0.00..64.82 rows=2171 width=4) (actual time=0.021..0.537 rows=2171 loops=1)
                           Heap Fetches: 0
 Total runtime: 2268.394 ms
(17 rows)



К этому представлению делается запрос :

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT view1."rsrv_qty",view1."comment",view1."rch_qty",view1."sequence",view1."order_id",
view1."qty",view1."uom_id",view1."categ_id",view1."mat_ids",view1."date_rchqty",
view1."par_id",view1."mt_id",view1."state",view1."orig_qty",view1."tracking_id",
view1."lbl_id",view1."date_qty",view1.id 
FROM "view1" 
WHERE view1.id IN (271053, 271054) 
ORDER BY id



Котрый выполняется Total runtime: 2277.899 ms
Посоветуйте как ускорить.

Через view - никак. Тут надо условия под GROUP BY вносить а postgresql этого не умеет.

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

Да ну тут-то вопрос в другом, как я полагаю.
Тут окончательная выборка двух строк, от полной не слишком сильно по времени отличается. В обоих случаях примерно одинаково и долго. Хотя данных-то не много, если верить rows'ам.
Может в индексах много несуществующих записей?

Я не шарю в оптимизациях, но довольно простая агрегация на не больших объёмах и так долго выполняется...
...
Рейтинг: 0 / 0
03.03.2016, 15:14
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185047
NewBie77
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NewBie77
Гость
У меня запрос на представление работает медленно, перед explain analyze сделал vacuum на таблицы.
...
Рейтинг: 0 / 0
03.03.2016, 15:19
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185115
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
GeniyZMaxim Boguk,

Да ну тут-то вопрос в другом, как я полагаю.
<т.п. бред>
вот не надо полагать
сказали в морг -- значит в морг


вам же разжевали и объяснили, что пж не может протащить условие, наложенное после группировки -- вовнутрь группового запроса (вьюхи), в условие "до группироки" -- т.е. в обоих случаях вычисляется полный агрегат всей таблицы. а во втором он , после подсчёта, фильтруется.
т.е .время счёта в обоих случаях -- время полной группировки.


неужели так сложно напрячь межушный ганглий и помедитировать над сказанным, йоксель
...
Рейтинг: 0 / 0
03.03.2016, 15:54
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185127
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
как я вижу -- там и в самом запросе это поле -- агрегат .
т.е. протащить в параметрическую табличную ф--ю не светит -- будет в HAVING


хотя , возможно , 2. можно переписать сам запрос. или 1. -- агрегат там ленивый, вместо включения в GROUP--list.
...
Рейтинг: 0 / 0
03.03.2016, 16:03
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185132
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim BogukЧерез view - никак. Тут надо условия под GROUP BY вносить а postgresql этого не умеет.
Патч есть и шанс есть, что его в 9.6 добавят: www.postgresql.org/message-id/flat/8783.1456842626@sss.pgh.pa.us
Очень прям хочется!
...
Рейтинг: 0 / 0
03.03.2016, 16:05
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185135
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
 
поправил ссылкуvyegorovПатч есть и шанс есть, что его в 9.6 добавят: http://www.postgresql.org/message-id/flat/8783.1456842626@sss.pgh.pa.us]http://www.postgresql.org/message-id/flat/8783.1456842626@sss.pgh.pa.us
Очень прям хочется!
...
Рейтинг: 0 / 0
03.03.2016, 16:07
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185159
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

приятная новость, но к делу не относится

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT min(lbl.id) AS id
, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id,
 sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl.rch_qty) AS rch_qty, sum(lbl.rsrv_qty) AS rsrv_qty, 
 min(lbl.date_qty) AS date_qty, min(lbl.date_rchqty) AS date_rchqty, max(lbl.sequence) AS sequence, max((lbl.state)::text) AS state, 
 max(lbl.comment) AS comment, array_to_string(array_agg(lbl.id), ','::text) AS mat_ids 
 FROM tbl1 lb 
 	JOIN tbl2 lbl ON lb.id = lbl.laborder_id
    JOIN tbl2 prd ON lbl.mt_id = prd.id
 GROUP BY lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id;



-- откуда мораль:
по заданным в списке конечного запроса lbl.id (и условиям связи) найти ключи группировки , и вставить их в WHERE исходного view.

и, напрмиер, добавить HAVING фильтр на предмет того, что там не найдется меньщих id

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT min(lbl.id) AS id
, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id,
 sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl.rch_qty) AS rch_qty, sum(lbl.rsrv_qty) AS rsrv_qty, 
 min(lbl.date_qty) AS date_qty, min(lbl.date_rchqty) AS date_rchqty, max(lbl.sequence) AS sequence, max((lbl.state)::text) AS state, 
 max(lbl.comment) AS comment, array_to_string(array_agg(lbl.id), ','::text) AS mat_ids 
 FROM tbl1 lb 
 	JOIN tbl2 lbl ON lb.id = lbl.laborder_id
    JOIN tbl2 prd ON lbl.mt_id = prd.id
WHERE 
  (lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id) IN (SELECT 
     lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id
     FROM  tbl1 lb 
 	JOIN tbl2 lbl ON lb.id = lbl.laborder_id
    JOIN tbl2 prd ON lbl.mt_id = prd.id
     WHERE lbl.id = ANY($1) -- IN ?
)
 GROUP BY lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id;
HAVING min(lbl.id) AS id = ANY($1) --min?



--запхать это в SQL --ф-ю, и дергать вместо запроса.
...
Рейтинг: 0 / 0
03.03.2016, 16:21
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185166
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

ах, да, это будет работать, пока в ключах группировки не будет NULL-s.

Как только -- придется писать оператор, выполняющий "IS NOT DISTINCT FROM" (не оператор, к сожалению, ни разу) для вашего row, что скорее всего неразрешимо.
разрешимо -- написать полный перебор случаев вхождения NULL в ваш ROW().
...
Рейтинг: 0 / 0
03.03.2016, 16:26
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185309
GeniyZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
GeniyZ
Гость
qwwq,
Сударь, я прекрасно понимаю что пж не протаскивает условие в группировку.
В данном конкретном случае топикстартеру это и не надо. Он и спрашивает-то о другом.
Лишь об этом я писал. А вы сразу йоксель,... .

Вот:
У меня запрос на представление работает медленно, перед explain analyze сделал vacuum на таблицы.
...
Рейтинг: 0 / 0
03.03.2016, 18:53
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39185383
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
GeniyZqwwq,
Сударь, я прекрасно понимаю что пж не протаскивает условие в группировку.
В данном конкретном случае топикстартеру это и не надо. Он и спрашивает-то о другом.
Лишь об этом я писал. А вы сразу йоксель,... .

Вот:
У меня запрос на представление работает медленно, перед explain analyze сделал vacuum на таблицы.
ну фу
не надо юлить под оппонентом
не в борделе чай

вон, выше -- все ходы записаны

у него единственный IS -- IOS по свежайшей VM
авторHeap Fetches: 0
всё остальное -- фулл сканами

т.ч. до сударьканья вам ещё расти и расти, мил. чел.

вот ещё мощность мн-ва после группировки практически не падает (с rows=356286 => rows=202404) -- т.ч. даже 200М не даёт перейти к хеш-агрегату.
Половину времени отжирают сканы, половину -- ГруппАггрегат.

т.ч. спрашивает он как раз об этом -- как ускориться для пары ключей из 200 000.
а то, что он в пылу и горячке что--то анализирует -- то тоже верно, статистику освежыть никогда не вредно. особенно, когда в голове туман.
...
Рейтинг: 0 / 0
03.03.2016, 20:55
| Ответить | Цитировать | Написать  
Оптимизация представления
    #39186067
Author the new one
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Author the new one
Участник
NewBie77,

Перепишите как функцию.
...
Рейтинг: 0 / 0
04.03.2016, 16:27
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация представления
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39184971&tid=1997380]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 173ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 245ms
total:  527ms
созд. / загр.: 527ms
x
x
Закрыть


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