Re: Parallel Hash take II

On Tue, Oct 24, 2017 at 10:10 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> Here is an updated patch set that does that ^.

It's a bit hard to understand what's going on with the v21 patch set I
posted yesterday because EXPLAIN ANALYZE doesn't tell you anything
interesting. Also, if you apply the multiplex_gather patch[1] I
posted recently and set multiplex_gather to off then it doesn't tell
you anything at all, because the leader has no hash table (I suppose
that could happen with unpatched master given sufficiently bad
timing). Here's a new version with an extra patch that adds some
basic information about load balancing to EXPLAIN ANALYZE, inspired by
what commit bf11e7ee did for Sort.

Example output:

enable_parallel_hash = on, multiplex_gather = on:

-> Parallel Hash (actual rows=1000000 loops=3)
Buckets: 131072 Batches: 16
Leader: Shared Memory Usage: 3552kB Hashed: 396120 Batches Probed: 7
Worker 0: Shared Memory Usage: 3552kB Hashed: 276640 Batches Probed: 6
Worker 1: Shared Memory Usage: 3552kB Hashed: 327240 Batches Probed: 6
-> Parallel Seq Scan on simple s (actual rows=333333 loops=3)

-> Parallel Hash (actual rows=10000000 loops=8)
Buckets: 131072 Batches: 256
Leader: Shared Memory Usage: 2688kB Hashed: 1347720
Batches Probed: 36
Worker 0: Shared Memory Usage: 2688kB Hashed: 1131360
Batches Probed: 33
Worker 1: Shared Memory Usage: 2688kB Hashed: 1123560
Batches Probed: 38
Worker 2: Shared Memory Usage: 2688kB Hashed: 1231920
Batches Probed: 38
Worker 3: Shared Memory Usage: 2688kB Hashed: 1272720
Batches Probed: 34
Worker 4: Shared Memory Usage: 2688kB Hashed: 1234800
Batches Probed: 33
Worker 5: Shared Memory Usage: 2688kB Hashed: 1294680
Batches Probed: 37
Worker 6: Shared Memory Usage: 2688kB Hashed: 1363240
Batches Probed: 35
-> Parallel Seq Scan on big s2 (actual rows=1250000 loops=8)

enable_parallel_hash = on, multiplex_gather = off (ie no leader participation):

-> Parallel Hash (actual rows=1000000 loops=2)
Buckets: 131072 Batches: 16
Worker 0: Shared Memory Usage: 3520kB Hashed: 475920 Batches Probed: 9
Worker 1: Shared Memory Usage: 3520kB Hashed: 524080 Batches Probed: 8
-> Parallel Seq Scan on simple s (actual rows=500000 loops=2)

enable_parallel_hash = off, multiplex_gather = on:

-> Hash (actual rows=1000000 loops=3)
Buckets: 131072 Batches: 16
Leader: Memory Usage: 3227kB
Worker 0: Memory Usage: 3227kB
Worker 1: Memory Usage: 3227kB
-> Seq Scan on simple s (actual rows=1000000 loops=3)

enable_parallel_hash = off, multiplex_gather = off:

-> Hash (actual rows=1000000 loops=2)
Buckets: 131072 Batches: 16
Worker 0: Memory Usage: 3227kB
Worker 1: Memory Usage: 3227kB
-> Seq Scan on simple s (actual rows=1000000 loops=2)

parallelism disabled (traditional single-line output, unchanged):

-> Hash (actual rows=1000000 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 3227kB
-> Seq Scan on simple s (actual rows=1000000 loops=1)

(It actually says "Tuples Hashed", not "Hashed" but I edited the above
to fit on a standard punchcard.) Thoughts?

[1] https://www.postgresql.org/message-id/CAEepm%3D2U%2B%2BLp3bNTv2Bv_kkr5NE2pOyHhxU%3DG0YTa4ZhSYhHiw%40mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com