Re: Parallel Query

****
Em 13/11/2019 17:47, Tomas Vondra escreveu:
> On Wed, Nov 13, 2019 at 05:16:44PM -0300, Luís Roberto Weck wrote:
>> Hi!
>>
>> Is there a reason query 3 can't use parallel workers? Using q1 and q2
>> they seem very similar but can use up to 4 workers to run faster:
>>
>> q1: https://pastebin.com/ufkbSmfB
>> q2: https://pastebin.com/Yt32zRNX
>> q3: https://pastebin.com/dqh7yKPb
>>
>> The sort node on q3 takes almost 12 seconds, making the query run on
>> 68  if I had set enough work_mem to make it all in memory.
>>
>
> Most likely because it'd be actually slower. The trouble is the
> aggregation does not actually reduce the cardinality, or at least the
> planner does not expect that - the Sort and GroupAggregate are expected
> to produce 3454539 rows. The last step of the aggregation has to receive
> and merge data from all workers, which is not exactly free, and if there
> is no reduction of cardinality it's likely cheaper to just do everything
> in a single process serially.
>
> How does the explain analyze output look like without the HAVING clause?
>
> Try setting parallel_setup_cost and parallel_tuple_cost to 0. That might
> trigger parallel query.
>
> regards
>
Tomas,

EXPLAIN:
Group  (cost=1245130.37..1279676.46 rows=3454609 width=14)
  Group Key: c.concod, cp.conparnum, cp.conpardatven
  ->  Sort  (cost=1245130.37..1253766.89 rows=3454609 width=14)
        Sort Key: c.concod, cp.conparnum, cp.conpardatven
        ->  Hash Join  (cost=34366.64..869958.26 rows=3454609 width=14)
              Hash Cond: (cp.concod = c.concod)
              ->  Seq Scan on contrato_parcela cp (cost=0.00..804245.73
rows=11941273 width=14)
              ->  Hash  (cost=23436.55..23436.55 rows=874407 width=8)
                    ->  Index Only Scan using contrato_iu0004 on
contrato c  (cost=0.43..23436.55 rows=874407 width=8)
                          Index Cond: (carcod = 100)

EXPLAIN ANALYZE:

Group  (cost=1245132.29..1279678.44 rows=3454615 width=14) (actual
time=61860.985..64852.579 rows=6787445 loops=1)
  Group Key: c.concod, cp.conparnum, cp.conpardatven
  ->  Sort  (cost=1245132.29..1253768.83 rows=3454615 width=14) (actual
time=61860.980..63128.557 rows=6787531 loops=1)
        Sort Key: c.concod, cp.conparnum, cp.conpardatven
        Sort Method: external merge  Disk: 172688kB
        ->  Hash Join  (cost=34366.64..869959.48 rows=3454615 width=14)
(actual time=876.428..52675.140 rows=6787531 loops=1)
              Hash Cond: (cp.concod = c.concod)
              ->  Seq Scan on contrato_parcela cp (cost=0.00..804246.91
rows=11941291 width=14) (actual time=0.010..44860.242 rows=11962505 loops=1)
              ->  Hash  (cost=23436.55..23436.55 rows=874407 width=8)
(actual time=874.791..874.791 rows=879841 loops=1)
                    Buckets: 1048576  Batches: 1  Memory Usage: 42561kB
                    ->  Index Only Scan using contrato_iu0004 on
contrato c  (cost=0.43..23436.55 rows=874407 width=8) (actual
time=0.036..535.897 rows=879841 loops=1)
                          Index Cond: (carcod = 100)
                          Heap Fetches: 144438
Planning time: 1.252 ms
Execution time: 65214.007 ms

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