The patch has a problem with partitionwise aggregates.
Asynchronous append do not allow the planner to use partial aggregates. Example you can see in attachment. I can't understand why: costs of partitionwise join are less. Initial script and explains of the query with and without the patch you can see in attachment.
-- Andrey Lepikhov Postgres Professional https://postgrespro.com
frgn2n.sh
Description: application/shellscript
Execution without asynchronous append
=====================================
explain analyze SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a
AND second.b < 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=2144.36..2144.37 rows=1 width=8) (actual
time=25.821..25.821 rows=1 loops=1)
-> Append (cost=463.39..2144.35 rows=4 width=8) (actual time=9.495..25.816
rows=4 loops=1)
-> Partial Aggregate (cost=463.39..463.40 rows=1 width=8) (actual
time=9.495..9.495 rows=1 loops=1)
-> Hash Join (cost=5.58..463.33 rows=27 width=4) (actual
time=0.109..9.486 rows=27 loops=1)
Hash Cond: (parts.a = second.a)
-> Seq Scan on part_0 parts (cost=0.00..363.26
rows=25126 width=8) (actual time=0.018..5.901 rows=25126 loops=1)
-> Hash (cost=5.24..5.24 rows=27 width=4) (actual
time=0.084..0.084 rows=27 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on second_0 second (cost=0.00..5.24
rows=27 width=4) (actual time=0.014..0.071 rows=27 loops=1)
Filter: (b < 100)
Rows Removed by Filter: 232
-> Partial Aggregate (cost=560.68..560.69 rows=1 width=8) (actual
time=6.017..6.017 rows=1 loops=1)
-> Foreign Scan (cost=105.29..560.61 rows=29 width=4) (actual
time=6.008..6.011 rows=30 loops=1)
Relations: (part_1 parts_1) INNER JOIN (second_1)
-> Partial Aggregate (cost=560.88..560.89 rows=1 width=8) (actual
time=5.920..5.920 rows=1 loops=1)
-> Foreign Scan (cost=105.75..560.82 rows=24 width=4) (actual
time=5.908..5.912 rows=25 loops=1)
Relations: (part_2 parts_2) INNER JOIN (second_2)
-> Partial Aggregate (cost=559.33..559.34 rows=1 width=8) (actual
time=4.380..4.381 rows=1 loops=1)
-> Foreign Scan (cost=105.09..559.29 rows=16 width=4) (actual
time=4.371..4.373 rows=17 loops=1)
Relations: (part_3 parts_3) INNER JOIN (second_3)
Planning Time: 6.734 ms
Execution Time: 26.079 ms
Execution with asynchronous append
==================================
explain analyze SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a
AND second.b < 100;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=5758.83..5758.84 rows=1 width=8) (actual
time=184.849..184.849 rows=1 loops=1)
-> Append (cost=727.82..5758.82 rows=4 width=8) (actual
time=11.735..184.843 rows=4 loops=1)
-> Partial Aggregate (cost=727.82..727.83 rows=1 width=8) (actual
time=11.735..11.735 rows=1 loops=1)
-> Hash Join (cost=677.34..725.94 rows=753 width=4) (actual
time=11.693..11.729 rows=27 loops=1)
Hash Cond: (second.a = parts.a)
-> Seq Scan on second_0 second (cost=0.00..38.25
rows=753 width=4) (actual time=0.024..0.052 rows=27 loops=1)
Filter: (b < 100)
Rows Removed by Filter: 232
-> Hash (cost=363.26..363.26 rows=25126 width=8) (actual
time=11.644..11.644 rows=25126 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1238kB
-> Seq Scan on part_0 parts (cost=0.00..363.26
rows=25126 width=8) (actual time=0.013..5.595 rows=25126 loops=1)
-> Partial Aggregate (cost=1676.97..1676.98 rows=1 width=8) (actual
time=58.958..58.958 rows=1 loops=1)
-> Hash Join (cost=1377.15..1440.85 rows=94449 width=4)
(actual time=58.922..58.948 rows=30 loops=1)
Hash Cond: (second_1.a = parts_1.a)
-> Foreign Scan on second_1 (cost=100.00..153.31
rows=753 width=4) (actual time=0.366..0.374 rows=30 loops=1)
-> Hash (cost=963.58..963.58 rows=25086 width=8) (actual
time=58.534..58.534 rows=24978 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1232kB
-> Foreign Scan on part_1 parts_1
(cost=100.00..963.58 rows=25086 width=8) (actual time=0.355..51.993 rows=24978
loops=1)
-> Partial Aggregate (cost=1676.97..1676.98 rows=1 width=8) (actual
time=57.126..57.126 rows=1 loops=1)
-> Hash Join (cost=1377.15..1440.85 rows=94449 width=4)
(actual time=57.105..57.120 rows=25 loops=1)
Hash Cond: (second_2.a = parts_2.a)
-> Foreign Scan on second_2 (cost=100.00..153.31
rows=753 width=4) (actual time=0.329..0.333 rows=25 loops=1)
-> Hash (cost=963.58..963.58 rows=25086 width=8) (actual
time=56.750..56.750 rows=24971 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1232kB
-> Foreign Scan on part_2 parts_2
(cost=100.00..963.58 rows=25086 width=8) (actual time=0.362..50.083 rows=24971
loops=1)
-> Partial Aggregate (cost=1676.99..1677.00 rows=1 width=8) (actual
time=57.020..57.020 rows=1 loops=1)
-> Hash Join (cost=1377.15..1440.87 rows=94449 width=4)
(actual time=57.005..57.015 rows=17 loops=1)
Hash Cond: (second_3.a = parts_3.a)
-> Foreign Scan on second_3 (cost=100.00..153.31
rows=753 width=4) (actual time=0.280..0.283 rows=17 loops=1)
-> Hash (cost=963.58..963.58 rows=25086 width=8) (actual
time=56.656..56.656 rows=24925 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1230kB
-> Foreign Scan on part_3 parts_3
(cost=100.00..963.58 rows=25086 width=8) (actual time=0.310..50.014 rows=24925
loops=1)
Planning Time: 5.809 ms
Execution Time: 185.640 ms
(34 rows)
