Postgresql何时使用JOIN列进行分区修剪？

Question

Postgresql何时使用JOIN列进行分区修剪？

postgresqlpartitioningdatabase-partitioningpruning

3

我在一个Postgres 11数据库中有两个表：

client table
--------
client_id    integer
client_name  character_varying

file table
--------
file_id      integer
client_id    integer
file_name    character_varying

客户端表未分区，文件表按client_id（按列表分区）进行了分区。当将新客户插入客户端表时，触发器会为文件表创建一个新的分区。文件表具有外键约束，引用客户端表上的client_id。

当我执行以下SQL语句（其中c.client_id = 1）时，一切似乎都正常：

explain  
select *
from client c
join file f using (client_id)
where c.client_id = 1;

使用分区剪枝，只扫描分区文件file_p1：

Nested Loop  (cost=0.00..3685.05 rows=100001 width=82)
  ->  Seq Scan on client c  (cost=0.00..1.02 rows=1 width=29)
        Filter: (client_id = 1)
  ->  Append  (cost=0.00..2684.02 rows=100001 width=57)
        ->  Seq Scan on file_p1 f  (cost=0.00..2184.01 rows=100001 width=57)
              Filter: (client_id = 1)

但是当我使用类似“where c.client_name = 'test'”这样的where子句时，数据库会扫描所有分区，并且无法识别client_name“test”等于client_id 1：

explain  
select *
from client c
join file f using (client_id)
where c.client_name = 'test';

执行计划：

Hash Join  (cost=1.04..6507.57 rows=100001 width=82)
  Hash Cond: (f.client_id = c.client_id)
  ->  Append  (cost=0.00..4869.02 rows=200002 width=57)
        ->  Seq Scan on file_p1 f  (cost=0.00..1934.01 rows=100001 width=57)
        ->  Seq Scan on file_p4 f_1  (cost=0.00..1934.00 rows=100000 width=57)
        ->  Seq Scan on file_pdefault f_2  (cost=0.00..1.00 rows=1 width=556)
  ->  Hash  (cost=1.02..1.02 rows=1 width=29)
        ->  Seq Scan on client c  (cost=0.00..1.02 rows=1 width=29)
              Filter: ((name)::text = 'test'::text)

因此，对于此SQL，将扫描文件表中的所有分区。

那么每个选择操作都应该使用表进行分区的列吗？数据库不能偏离分区修剪标准吗？

编辑： 添加一些信息：

过去，我大部分时间都在使用Oracle数据库。

执行计划可能如下：

使用客户端名称对客户端表进行全表扫描，以查找client_id。
对“PARTITION LIST”访问文件表，其中SQL Developer指定PARTITION_START = KEY和PARTITION_STOP = KEY，以表示在计算执行计划时不知道确切的分区，但是访问仅会在计算出的客户端表中进行。

这也是我在Postgresql中预期的。

- BrooklynFFM

查询执行前会创建一个查询计划，因此它不能基于查询结果。换句话说，在查询运行之前，规划程序无法知道client_name = 'test'意味着client_id = 1。 - klin

感谢您的评论，我已经添加了一些额外的信息，这些信息是我期望从Oracle数据库中得到的。 - BrooklynFFM

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- klin · Accepted Answer

“文档”指出动态分区修剪是可能的。

在查询计划的实际执行过程中，可以执行分区修剪以删除仅在实际查询执行期间已知的值使用的分区。这包括来自子查询的值和来自执行时参数（例如来自参数化嵌套循环连接的参数）的值。

如果我理解正确，它适用于提供分区键值作为参数的预处理语句或具有子查询的查询。使用explain analyse查看动态修剪（我的示例数据包含三个分区中的一百万行）。

explain analyze
select *
from file
where client_id = (
    select client_id
    from client
    where client_name = 'test');

Append  (cost=25.88..22931.88 rows=1000000 width=14) (actual time=0.091..96.139 rows=333333 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on client  (cost=0.00..25.88 rows=6 width=4) (actual time=0.040..0.042 rows=1 loops=1)
          Filter: (client_name = 'test'::text)
          Rows Removed by Filter: 2
  ->  Seq Scan on file_p1  (cost=0.00..5968.66 rows=333333 width=14) (actual time=0.039..70.026 rows=333333 loops=1)
        Filter: (client_id = $0)
  ->  Seq Scan on file_p2  (cost=0.00..5968.68 rows=333334 width=14) (never executed)
        Filter: (client_id = $0)
  ->  Seq Scan on file_p3  (cost=0.00..5968.66 rows=333333 width=14) (never executed)
        Filter: (client_id = $0)
Planning Time: 0.423 ms
Execution Time: 109.189 ms

请注意，分区p2和p3的扫描从未执行。

回答您的确切问题，描述问题中连接查询中的分区剪枝在Postgres中尚未实现（还没有？）。