ePQ支持并行写入及查询OSS外部表 - 云原生数据库 PolarDB

PolarDB PostgreSQL版的弹性跨机并行查询（ePQ）支持并行写入及查询OSS外部表。

前提条件

支持的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（内核小版本14.9.13.0及以上）
PostgreSQL 11（内核小版本1.1.35及以上）

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号：

PostgreSQL 14
```
select version();
```
PostgreSQL 11
```
show polar_version;
```

背景信息

PolarDB PostgreSQL版支持通过oss_fdw插件创建OSS外部表，其物理存储位于阿里云对象存储OSS上，在数据库内仅保存表的元信息。数据库中不常使用的历史数据、冷数据可以以外部表的形式归档到OSS上，从而节省数据库的存储成本。PolarDB PostgreSQL版的FDW接口标准保证了OSS外部表依旧支持通过标准SQL进行受限的读写访问。

将本地表的数据通过写入OSS外部表创建归档时，PolarDB PostgreSQL版默认将启动一个进程写入OSS外部表，其本质上是单进程上传的网络访问模式。在归档数据量非常大的场景中，单进程写入OSS外部表无法有效利用OSS的网络高带宽，导致非常低效。

在查询OSS外部表中的历史归档数据时，PolarDB PostgreSQL版默认将启动一个进程查询OSS外表对应的全量数据，其本质上也是单进程下载的网络访问模式。当归档数据量非常大时，单进程查询OSS外部表无法有效利用OSS的网络高带宽，也相对低效。

PolarDB PostgreSQL版的弹性跨机并行查询（ePQ）支持并行写入及查询OSS外部表：

ePQ优化器能够产生多进程并行写入OSS外部表的执行计划，ePQ执行器将在读写节点上启动多个进程并行写入OSS外部表。
ePQ优化器能够产生多进程并行查询OSS外部表的执行计划，ePQ执行器将在多个计算节点上启动多个进程并行查询OSS外部表。

ePQ对OSS外部表的并行化读写访问能力将数据库对OSS的网络访问模式从单进程上传/下载模式优化为多进程上传/下载模式，从而能够充分利用OSS的网络带宽资源，提升冷数据归档和冷数据查询的性能。

使用指南

准备数据

创建一张本地表t_local，并插入一定规模的数据。

CREATE TABLE t_local (id INT, age INT, msg TEXT, detail TEXT);

INSERT INTO t_local
SELECT
    random() * 1000000,
    random() * 10000,
    md5(random()::TEXT),
    md5(random()::TEXT)
FROM generate_series(1, 2000000);

创建一张与本地表t_local结构相同的OSS外部表t_oss。

CREATE EXTENSION oss_fdw;

CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw
OPTIONS (host 'oss-cn-xxx.aliyuncs.com', bucket 'mybucket', id 'xxx', key 'xxx');

CREATE FOREIGN TABLE t_oss (id INT, age INT, msg TEXT, detail TEXT)
SERVER ossserver OPTIONS (dir 'archive/');

并行写入OSS外部表

执行以下命令，禁用ePQ。
```
SET polar_enable_px TO OFF;
```
执行以下命令，将本地表t_local中的数据导入到OSS外部表t_oss中。执行计划与时间如下。
```
EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
        QUERY PLAN
---------------------------
 Insert on t_oss
   ->  Seq Scan on t_local
(2 rows)

INSERT INTO t_oss SELECT * FROM t_local;
INSERT 0 2000000
Time: 8861.708 ms (00:08.862)
```
根据上述执行计划显示，执行器将启动一个进程来扫描本地表并同时写入OSS外部表。总计用时为8861.708ms。
执行以下命令，启用ePQ以及ePQ并行INSERT功能，设置并行查询本地表的并行度为16，设置并行写入OSS外部表的并行度为16。
```
SET polar_enable_px TO ON;
SET polar_px_enable_insert_select TO ON;
SET polar_px_dop_per_node TO 16;
SET polar_px_insert_dop_num TO 16;
```

再次执行以下命令，执行计划与时间如下。

EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
                    QUERY PLAN
---------------------------------------------------
 Insert on t_oss
   ->  Result
         ->  PX Hash 32:16  (slice1; segments: 32)
               ->  Partial Seq Scan on t_local
 Optimizer: PolarDB PX Optimizer
(5 rows)

INSERT INTO t_oss SELECT * FROM t_local;
INSERT 0 2000000
Time: 1321.212 ms (00:01.321)

根据上述执行计划显示，ePQ执行框架将会启动32个进程（segments: 32）执行计划分片slice1，并行扫描本地表（Partial Seq Scan）；然后通过Motion算子将数据重分布（PX Hash 32:16）到并行写入OSS外部表的16个进程中。总计用时为1321.212ms，较单进程写入有了明显的提升。

并行查询OSS外部表

执行以下命令，禁用ePQ。
```
SET polar_enable_px TO OFF;
```

执行以下命令，查询OSS外部表t_oss的全量数据行数。执行计划与结果如下。

EXPLAIN SELECT COUNT(*) FROM t_oss;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (cost=1366687.96..1366687.97 rows=1 width=8)
   ->  Foreign Scan on t_oss  (cost=0.00..1334280.40 rows=12963024 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 17
         Total size of OSS file: 297 MB
(5 rows)

SELECT COUNT(*) FROM t_oss;
  count
---------
 4000000
(1 row)

Time: 36230.325 ms (00:36.230)

根据上述执行计划显示，执行器将启动一个进程来扫描OSS外部表对应的17个OSS文件，总计297 MB，用时为36230.325ms。

执行以下命令，启用ePQ并设置查询并行度为8。
```
SET polar_enable_px TO ON;
SET polar_px_dop_per_node TO 8;
```

再次执行以下命令，执行计划与结果如下。

EXPLAIN SELECT COUNT(*) FROM t_oss;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  PX Coordinator 16:1  (slice1; segments: 16)  (cost=0.00..431.00 rows=1 width=1)
         ->  Partial Foreign Scan on t_oss  (cost=0.00..431.00 rows=1 width=1)
               Directory on OSS: archive/
               Number Of OSS file: 17
               Total size of OSS file: 297 MB
 Optimizer: PolarDB PX Optimizer
(7 rows)

SELECT COUNT(*) FROM t_oss;
  count
---------
 4000000
(1 row)

Time: 18100.894 ms (00:18.101)

根据上述执行计划显示，ePQ执行框架将在两个计算节点上总共启动16个进程（segments: 16）执行计划分片slice1，并行扫描OSS外部表（Partial Foreign Scan）。这16个进程将会以OSS上的文件为粒度，各自对查询任务进行分工。所有进程的查询结果将会通过Motion算子（PX Coordinator 16:1）汇聚到查询发起进程并返回。总计用时为18100.894ms，较单进程查询有了明显的提升。