Re: Parallel Query - Mailing list pgsql-performance

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