On Fri, Mar 31, 2017 at 5:13 PM, Amit Kapila <amit.kapil...@gmail.com> wrote:
> On Thu, Mar 30, 2017 at 8:24 AM, Robert Haas <robertmh...@gmail.com> wrote:
>> On Wed, Mar 29, 2017 at 8:00 PM, Tomas Vondra
>> <tomas.von...@2ndquadrant.com> wrote:
>>> What is however strange is that changing max_parallel_workers_per_gather
>>> affects row estimates *above* the Gather node. That seems a bit, um,
>>> suspicious, no? See the parallel-estimates.log.
>>
>> Thanks for looking at this! Comparing the parallel plan vs. the
>> non-parallel plan:
>>
>> part: parallel rows (after Gather) 20202, non-parallel rows 20202
>> partsupp: parallel rows 18, non-parallel rows 18
>> part-partsupp join: parallel rows 88988, non-parallel rows 355951
>> lineitem: parallel rows 59986112, non-parallel rows 59986112
>> lineitem after aggregation: parallel rows 5998611, non-parallel rows 5998611
>> final join: parallel rows 131, non-parallel rows 524
>>
>> I agree with you that that looks mighty suspicious. Both the
>> part-partsupp join and the final join have exactly 4x as many
>> estimated rows in the non-parallel plan as in the parallel plan, and
>> it just so happens that the parallel divisor here will be 4.
>>
>> Hmm... it looks like the parallel_workers value from the Gather node
>> is being erroneously propagated up to the higher levels of the plan
>> tree. Wow. Somehow, Gather Merge managed to get the logic correct
>> here, but Gather is totally wrong. Argh. Attached is a draft patch,
>> which I haven't really tested beyond checking that it passes 'make
>> check'.
>>
>
> Your patch looks good to me. I have verified some join cases as well
> where the behaviour is sane after patch. I have also done testing
> with force_parallel_mode=regress (ran make check-world) and everything
> seems good.
>
> --
I tried checking the plan of Q20 with this patch, and got the following results,
with patch,
-> Merge Join (cost=3025719.98..3499235.22 rows=6 width=16) (actual
time=176440.801..245903.143 rows=118124 loops=1)
Merge Cond: ((lineitem.l_partkey =
partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
Join Filter:
((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity))))
and without patch,
-> Merge Join (cost=3014830.12..3511637.54 rows=2 width=16) (actual
time=130994.237..208887.149 rows=118124 loops=1)
Merge Cond: ((partsupp.ps_partkey =
lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey))
Join Filter:
((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity))))
So, it looks like in the problematic area, it is not improving much.
Please find the attached file for the query plan of Q20 with and
without patch. I haven't evaluated the performance of this query with
patch.
--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/
with patch:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3500605.72..3500605.73 rows=1 width=52) (actual
time=572114.740..572114.740 rows=1 loops=1)
-> Sort (cost=3500605.72..3500605.73 rows=1 width=52) (actual
time=572114.738..572114.738 rows=1 loops=1)
Sort Key: supplier.s_name
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop Semi Join (cost=3025720.40..3500605.71 rows=1
width=52) (actual time=178675.638..572096.079 rows=3600 loops=1)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
Rows Removed by Join Filter: 723939540
-> Nested Loop (cost=0.42..650.46 rows=8000 width=56) (actual
time=5.210..128.099 rows=8090 loops=1)
-> Seq Scan on nation (cost=0.00..0.41 rows=1 width=4)
(actual time=0.016..0.039 rows=1 loops=1)
Filter: (n_name = 'EGYPT'::bpchar)
Rows Removed by Filter: 24
-> Index Scan using idx_supplier_nation_key on supplier
(cost=0.42..570.04 rows=8000 width=64) (actual time=5.189..123.582 rows=8090
loops=1)
Index Cond: (s_nationkey = nation.n_nationkey)
-> Materialize (cost=3025719.98..3499235.25 rows=6 width=16)
(actual time=21.810..40.888 rows=89486 loops=8090)
-> Merge Join (cost=3025719.98..3499235.22 rows=6
width=16) (actual time=176440.801..245903.143 rows=118124 loops=1)
Merge Cond: ((lineitem.l_partkey =
partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
Join Filter: ((partsupp.ps_availqty)::numeric >
((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 242
-> GroupAggregate (cost=2921830.80..3248925.16
rows=9680570 width=48) (actual time=174001.209..239395.275 rows=10890067
loops=1)
Group Key: lineitem.l_partkey,
lineitem.l_suppkey
-> Sort (cost=2921830.80..2967302.26
rows=18188581 width=21) (actual time=174001.170..191792.420 rows=18194084
loops=1)
Sort Key: lineitem.l_partkey,
lineitem.l_suppkey
Sort Method: external merge Disk:
551928kB
-> Bitmap Heap Scan on lineitem
(cost=191420.72..707288.74 rows=18188581 width=21) (actual
time=16502.428..142386.748 rows=18195269 loops=1)
Recheck Cond: ((l_shipdate >=
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without
time zone))
Rows Removed by Index Recheck:
64500318
Heap Blocks: exact=643068
lossy=1586943
-> Bitmap Index Scan on
idx_l_shipdate (cost=0.00..186873.58 rows=18188581 width=0) (actual
time=15788.414..15788.414 rows=18195269 loops=1)
Index Cond: ((l_shipdate >=
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without
time zone))
-> Sort (cost=103889.17..104293.20 rows=161611
width=24) (actual time=2438.874..2492.173 rows=173936 loops=1)
Sort Key: partsupp.ps_partkey,
partsupp.ps_suppkey
Sort Method: quicksort Memory: 19733kB
-> Nested Loop (cost=1000.43..89908.07
rows=161611 width=24) (actual time=0.492..2098.288 rows=173936 loops=1)
-> Gather (cost=1000.00..25736.47
rows=40404 width=4) (actual time=0.441..43.207 rows=43484 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on part
(cost=0.00..20696.07 rows=10101 width=4) (actual time=0.101..568.375 rows=8697
loops=5)
Filter: ((p_name)::text ~~
'beige%'::text)
Rows Removed by Filter:
791303
-> Index Scan using
idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=36 width=20) (actual
time=0.040..0.044 rows=4 loops=43484)
Index Cond: (ps_partkey =
part.p_partkey)
Planning time: 4.366 ms
Execution time: 572175.608 ms
(43 rows)
without patch:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3511640.58..3511640.58 rows=1 width=52) (actual
time=210752.754..210752.754 rows=1 loops=1)
-> Sort (cost=3511640.58..3511640.58 rows=0 width=52) (actual
time=210752.752..210752.752 rows=1 loops=1)
Sort Key: supplier.s_name
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=3511638.11..3511640.57 rows=0 width=52) (actual
time=209079.102..210746.190 rows=3600 loops=1)
-> Nested Loop (cost=3511637.98..3511640.32 rows=2 width=60)
(actual time=209048.920..210175.846 rows=89512 loops=1)
-> HashAggregate (cost=3511637.56..3511637.62 rows=6
width=16) (actual time=209036.754..209097.302 rows=89512 loops=1)
Group Key: partsupp.ps_suppkey
-> Merge Join (cost=3014830.12..3511637.54 rows=2
width=16) (actual time=130994.237..208887.149 rows=118124 loops=1)
Merge Cond: ((partsupp.ps_partkey =
lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey))
Join Filter: ((partsupp.ps_availqty)::numeric
> ((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 242
-> Sort (cost=92999.32..93100.33 rows=40403
width=24) (actual time=1307.666..1368.686 rows=173936 loops=1)
Sort Key: partsupp.ps_partkey,
partsupp.ps_suppkey
Sort Method: quicksort Memory: 19733kB
-> Nested Loop (cost=1000.43..89908.07
rows=40403 width=24) (actual time=0.298..1069.290 rows=173936 loops=1)
-> Gather
(cost=1000.00..25736.47 rows=40404 width=4) (actual time=0.254..36.733
rows=43484 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on
part (cost=0.00..20696.07 rows=10101 width=4) (actual time=0.059..738.015
rows=8697 loops=5)
Filter:
((p_name)::text ~~ 'beige%'::text)
Rows Removed by
Filter: 791303
-> Index Scan using
idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=36 width=20) (actual
time=0.018..0.021 rows=4 loops=43484)
Index Cond: (ps_partkey =
part.p_partkey)
-> Materialize (cost=2921830.80..3369932.29
rows=9680570 width=48) (actual time=129686.054..201794.618 rows=10890067
loops=1)
-> GroupAggregate
(cost=2921830.80..3248925.16 rows=9680570 width=48) (actual
time=129686.045..195197.222 rows=10890067 loops=1)
Group Key: lineitem.l_partkey,
lineitem.l_suppkey
-> Sort
(cost=2921830.80..2967302.26 rows=18188581 width=21) (actual
time=129686.013..147644.109 rows=18194084 loops=1)
Sort Key:
lineitem.l_partkey, lineitem.l_suppkey
Sort Method: external merge
Disk: 551928kB
-> Bitmap Heap Scan on
lineitem (cost=191420.72..707288.74 rows=18188581 width=21) (actual
time=12280.877..97715.337 rows=18195269 loops=1)
Recheck Cond:
((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01
00:00:00'::timestamp without time zone))
Rows Removed by Index
Recheck: 64500318
Heap Blocks:
exact=643068 lossy=1586943
-> Bitmap Index Scan
on idx_l_shipdate (cost=0.00..186873.58 rows=18188581 width=0) (actual
time=11605.457..11605.457 rows=18195269 loops=1)
Index Cond:
((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01
00:00:00'::timestamp without time zone))
-> Index Scan using supplier_pkey on supplier
(cost=0.42..0.44 rows=1 width=64) (actual time=0.010..0.011 rows=1 loops=89512)
Index Cond: (s_suppkey = lineitem.l_suppkey)
-> Index Scan using nation_pkey on nation (cost=0.14..0.16
rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=89512)
Index Cond: (n_nationkey = supplier.s_nationkey)
Filter: (n_name = 'EGYPT'::bpchar)
Rows Removed by Filter: 1
Planning time: 5.461 ms
Execution time: 210810.480 ms
(44 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
