Re: Parallel Query

Поиск
Список
Период
Сортировка
От Luís Roberto Weck
Тема Re: Parallel Query
Дата
Msg-id 9bae6043-436f-3335-d84a-f4dbd47e59cd@siscobra.com.br
обсуждение исходный текст
Ответ на Re: Parallel Query  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-performance
Дерево обсуждения
Em 13/11/2019 19:08, Jeff Janes escreveu:
On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck <luisroberto@siscobra.com.br> wrote:


Indeed, reducing the costs made the query run in parallel, but the improvement in speed was not worth the cost (CPU).

Could you show the plan for that? 
Sure:

Finalize GroupAggregate  (cost=842675.56..1017018.29 rows=3470567 width=14) (actual time=61419.510..65635.188 rows=86 loops=1)
  Group Key: c.concod, cp.conparnum, cp.conpardatven
  Filter: (count(*) > 1)
  Rows Removed by Filter: 6787359
  ->  Gather Merge  (cost=842675.56..947606.94 rows=3470568 width=22) (actual time=51620.609..60648.085 rows=6787506 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Partial GroupAggregate  (cost=842575.50..862097.45 rows=867642 width=22) (actual time=51585.526..53477.065 rows=1357501 loops=5)
              Group Key: c.concod, cp.conparnum, cp.conpardatven
              ->  Sort  (cost=842575.50..844744.61 rows=867642 width=14) (actual time=51585.514..51951.984 rows=1357506 loops=5)
                    Sort Key: c.concod, cp.conparnum, cp.conpardatven
                    Sort Method: quicksort  Memory: 112999kB
                    ->  Hash Join  (cost=34390.13..756996.76 rows=867642 width=14) (actual time=1087.591..49744.673 rows=1357506 loops=5)
                          Hash Cond: (cp.concod = c.concod)
                          ->  Parallel Seq Scan on contrato_parcela cp  (cost=0.00..714762.89 rows=2988089 width=14) (actual time=0.077..46674.986 rows=2392501 loops=5)
                          ->  Hash  (cost=23462.75..23462.75 rows=874190 width=8) (actual time=1080.189..1080.189 rows=879841 loops=5)
                                Buckets: 1048576  Batches: 1  Memory Usage: 42561kB
                                ->  Index Only Scan using contrato_iu0004 on contrato c  (cost=0.43..23462.75 rows=874190 width=8) (actual time=0.141..663.108 rows=879841 loops=5)
                                      Index Cond: (carcod = 100)
                                      Heap Fetches: 35197
Planning time: 1.045 ms
Execution time: 65734.134 ms

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Parallel Query
Следующее
От: Jesper Pedersen
Дата:
Сообщение: JSON path