pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: etsuro.fujita@gmail.com
Cc: movead.li@highgo.ca
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Asynchronous Append on postgres_fdw nodes.
Date: Mon, 15 Jun 2020 08:51:23 +0500
Message-ID: <dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru> (raw )
In-Reply-To: <20200610.120510.2075667454883130505.horikyota.ntt@gmail.com >
References: <c5b4ca1e-5550-a405-4199-191143f4e8c2@postgrespro.ru >
<20200604.150015.2305436578805492574.horikyota.ntt@gmail.com >
<90cced83-9061-0cac-0012-84e5faac8c78@postgrespro.ru >
<20200610.120510.2075667454883130505.horikyota.ntt@gmail.com >
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
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)
Attachments:
[application/x-shellscript] frgn2n.sh (3.4K, ../dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru/2-frgn2n.sh)
download
[text/plain] explain_partial_aggregates_problem.txt (6.2K, ../dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru/3-explain_partial_aggregates_problem.txt)
download | inline:
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)
view thread (130+ messages) latest in thread
Message-ID: <dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru>
Permalink: ../dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru/
Also on: postgresql.org/message-id/dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, horikyota.ntt@gmail.com, etsuro.fujita@gmail.com, movead.li@highgo.ca, pgsql-hackers@lists.postgresql.org
Subject: Re: Asynchronous Append on postgres_fdw nodes.
In-Reply-To: <dc214e8f-96bc-a6c0-19ba-0dffc2051b34@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox