enable_async_append

• 19
• 18
• 17
• 16
• 15
• 14

给定一个按如下方式设置的分区表

postgres=# \d+ parttest
                                   Partitioned table "public.parttest"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer |           | not null |         | plain    |             |              | 
 val1   | text    |           |          |         | extended |             |              | 
 val2   | text    |           |          |         | extended |             |              | 
Partition key: HASH (id)
Partitions: parttest_4_0 FOR VALUES WITH (modulus 4, remainder 0),
            parttest_4_1 FOR VALUES WITH (modulus 4, remainder 1),
            parttest_4_2 FOR VALUES WITH (modulus 4, remainder 2),
            parttest_4_3 FOR VALUES WITH (modulus 4, remainder 3)

其中各个分区是位于不同服务器上的 外部表，类似以下的查询应该会生成如下计划

postgres=# EXPLAIN (verbose, analyze) select * FROM parttest;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=100.00..4345.00 rows=100000 width=68) (actual time=2.067..145.652 rows=100000 loops=1)
   ->  Async Foreign Scan on public.parttest_4_0 parttest_1  (cost=100.00..965.78 rows=25126 width=68) (actual time=1.104..29.017 rows=25126 loops=1)
         Output: parttest_1.id, parttest_1.val1, parttest_1.val2
         Remote SQL: SELECT id, val1, val2 FROM public.parttest_4_0
   ->  Async Foreign Scan on public.parttest_4_1 parttest_2  (cost=100.00..960.34 rows=24978 width=68) (actual time=1.057..26.778 rows=24978 loops=1)
         Output: parttest_2.id, parttest_2.val1, parttest_2.val2
         Remote SQL: SELECT id, val1, val2 FROM public.parttest_4_1
   ->  Async Foreign Scan on public.parttest_4_2 parttest_3  (cost=100.00..960.13 rows=24971 width=68) (actual time=0.319..15.856 rows=24971 loops=1)
         Output: parttest_3.id, parttest_3.val1, parttest_3.val2
         Remote SQL: SELECT id, val1, val2 FROM public.parttest_4_2
   ->  Async Foreign Scan on public.parttest_4_3 parttest_4  (cost=100.00..958.75 rows=24925 width=68) (actual time=0.284..17.054 rows=24925 loops=1)
         Output: parttest_4.id, parttest_4.val1, parttest_4.val2
         Remote SQL: SELECT id, val1, val2 FROM public.parttest_4_3
 Query Identifier: 5644017674074468972
 Planning Time: 0.642 ms
 Execution Time: 152.617 ms
(16 rows)

• PostgreSQL 文档： enable_async_append

• PG-14 中 postgres_fdw 扫描的并行执行 - Ahsan Hadi / HighGo 于 2021 年 6 月的博客文章