分区表的查询仍在扫描所有分区[英] Partitioned table query still scanning all partitions

本文是小编为大家收集整理的关于分区表的查询仍在扫描所有分区的处理方法,想解了分区表的查询仍在扫描所有分区的问题怎么解决?分区表的查询仍在扫描所有分区问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一张桌子,记录了超过十亿.为了提高性能,我将其划分为30个分区.最常见的查询在其Where子句中具有(id = ...),因此我决定在id列上划分表.

基本上,分区是通过这种方式创建的:

CREATE TABLE foo_0 (CHECK (id % 30 = 0)) INHERITS (foo);
CREATE TABLE foo_1 (CHECK (id % 30 = 1)) INHERITS (foo);
CREATE TABLE foo_2 (CHECK (id % 30 = 2)) INHERITS (foo);
CREATE TABLE foo_3 (CHECK (id % 30 = 3)) INHERITS (foo);
.
.
.

i ran ANALYZE对于整个数据库,尤其是我通过运行来收集此表的id列的额外统计信息:

ALTER TABLE foo ALTER COLUMN id SET STATISTICS 10000;

但是,当我运行在id列上过滤的查询时,计划器仍显示其仍在扫描所有分区. constraint_exclusion设置为partition,所以这不是问题.

EXPLAIN ANALYZE SELECT * FROM foo WHERE (id = 2);


                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..8106617.40 rows=3620981 width=54) (actual time=30.544..215.540 rows=171477 loops=1)
   ->  Append  (cost=0.00..8106617.40 rows=3620981 width=54) (actual time=30.539..106.446 rows=171477 loops=1)
         ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=203) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (id = 2)
         ->  Bitmap Heap Scan on foo_0 foo  (cost=3293.44..281055.75 rows=122479 width=52) (actual time=0.020..0.020 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_0_idx_1  (cost=0.00..3262.82 rows=122479 width=0) (actual time=0.018..0.018 rows=0 loops=1)
                     Index Cond: (id = 2)
         ->  Bitmap Heap Scan on foo_1 foo  (cost=3312.59..274769.09 rows=122968 width=56) (actual time=0.012..0.012 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_1_idx_1  (cost=0.00..3281.85 rows=122968 width=0) (actual time=0.010..0.010 rows=0 loops=1)
                     Index Cond: (id = 2)
         ->  Bitmap Heap Scan on foo_2 foo  (cost=3280.30..272541.10 rows=121903 width=56) (actual time=30.504..77.033 rows=171477 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_2_idx_1  (cost=0.00..3249.82 rows=121903 width=0) (actual time=29.825..29.825 rows=171477 loops=1)
                     Index Cond: (id = 2)
.
.
.

我该怎么做才能使刨床有更好的计划?我还需要为所有分区运行ALTER TABLE foo ALTER COLUMN id SET STATISTICS 10000;吗?

编辑

使用Erwin建议对查询的建议更改后,计划器仅扫描正确的分区,但是执行时间实际上比完整的扫描更糟(至少是索引的).

EXPLAIN ANALYZE select * from foo where (id % 30 = 2) and (id = 2);
                                                                         QUERY PLAN
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..8106617.40 rows=3620981 width=54) (actual time=32.611..224.934 rows=171477 loops=1)
   ->  Append  (cost=0.00..8106617.40 rows=3620981 width=54) (actual time=32.606..116.565 rows=171477 loops=1)
         ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=203) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (id = 2)
         ->  Bitmap Heap Scan on foo_0 foo  (cost=3293.44..281055.75 rows=122479 width=52) (actual time=0.046..0.046 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_0_idx_1  (cost=0.00..3262.82 rows=122479 width=0) (actual time=0.044..0.044 rows=0 loops=1)
                     Index Cond: (id = 2)
         ->  Bitmap Heap Scan on foo_1 foo  (cost=3312.59..274769.09 rows=122968 width=56) (actual time=0.021..0.021 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_1_idx_1  (cost=0.00..3281.85 rows=122968 width=0) (actual time=0.020..0.020 rows=0 loops=1)
                     Index Cond: (id = 2)
         ->  Bitmap Heap Scan on foo_2 foo  (cost=3280.30..272541.10 rows=121903 width=56) (actual time=32.536..86.730 rows=171477 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_2_idx_1  (cost=0.00..3249.82 rows=121903 width=0) (actual time=31.842..31.842 rows=171477 loops=1)
                     Index Cond: (id = 2)
         ->  Bitmap Heap Scan on foo_3 foo  (cost=3475.87..285574.05 rows=129032 width=52) (actual time=0.035..0.035 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_3_idx_1  (cost=0.00..3443.61 rows=129032 width=0) (actual time=0.031..0.031 rows=0 loops=1)
.
.
.
         ->  Bitmap Heap Scan on foo_29 foo  (cost=3401.84..276569.90 rows=126245 width=56) (actual time=0.019..0.019 rows=0 loops=1)
               Recheck Cond: (id = 2)
               ->  Bitmap Index Scan on foo_29_idx_1  (cost=0.00..3370.28 rows=126245 width=0) (actual time=0.018..0.018 rows=0 loops=1)
                     Index Cond: (id = 2)
 Total runtime: 238.790 ms

versus:

EXPLAIN ANALYZE select * from foo where (id % 30 = 2) and (id = 2);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..273120.30 rows=611 width=56) (actual time=31.519..257.051 rows=171477 loops=1)
   ->  Append  (cost=0.00..273120.30 rows=611 width=56) (actual time=31.516..153.356 rows=171477 loops=1)
         ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=203) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: ((id = 2) AND ((id % 30) = 2))
         ->  Bitmap Heap Scan on foo_2 foo  (cost=3249.97..273120.30 rows=610 width=56) (actual time=31.512..124.177 rows=171477 loops=1)
               Recheck Cond: (id = 2)
               Filter: ((id % 30) = 2)
               ->  Bitmap Index Scan on foo_2_idx_1  (cost=0.00..3249.82 rows=121903 width=0) (actual time=30.816..30.816 rows=171477 loops=1)
                     Index Cond: (id = 2)
 Total runtime: 270.384 ms

推荐答案

对于非平凡表达式,您必须在查询中重复或多或少的逐字条件,以使Postgres查询计划者了解它可以依靠CHECK约束.即使似乎是多余的!

perimand documentation :

启用了约束排除,计划者将检查 每个分区的约束,并试图证明该分区需要 不能扫描,因为它不能包含任何符合的行 查询的WHERE子句. 当计划者可以证明这一点时,它不包括 查询计划的分区.

大胆的重点是我的.计划者不了解复杂的表达方式. 当然,这也必须满足:

确保约束_EXCLAINT_EXCLAINT 配置参数不是 在postgresql.conf中禁用.如果是这样,则不会根据需要优化查询.

而不是

SELECT * FROM foo WHERE (id = 2);

尝试:

SELECT * FROM foo WHERE id % 30 = 2 AND id = 2;

和:

constraint_exclusion 是 其实on和off> partition,这使技术仅适用于查询 可能正在划分表.设置 使计划者在所有查询中检查CHECK的约束,甚至 简单不太可能受益的简单.

您可以尝试constraint_exclusion = on来查看计划者是否没有冗余的逐字条件.但是您必须权衡此设置的成本和利益.

替代方案将是您的分区的更简单条件,如 @harmic .

概述

否,在这种情况下,增加STATISTICS的数字将无济于事.在查询事项中仅CHECK约束和您的WHERE条件.

其他推荐答案

不幸的是,PostgreSQL中的分区是相当原始的.它仅适用于基于范围和列表的约束.您的分区约束太复杂了,无法使用查询计划者决定排除某些分区.

手册它说:

保持分区约束简单,否则计划者可能不是 能够证明该分区无需访问.使用简单 列表分区或简单范围测试的平等条件 范围分区,如前所述所示.一个好的 经验法则是分区约束仅应包含 使用分区列的比较与常数使用 b-tree-nodexable操作员.

您可能会摆脱更改您的子句,以便明确提到模量表达式,如Erwin所建议.过去,我对此并没有太多运气,尽管我最近没有尝试过,正如他所说,计划者有所改善.这可能是第一件事.

否则,您将必须重新排列分区以使用ID值范围,而不是现在使用的模量方法.我知道这不是一个很好的解决方案.

另一个解决方案是将ID的模量存储在单独的列中,然后您可以为分区约束使用简单的值相等性检查.不过,有点浪费磁盘空间,您还需要在启动的Where子句中添加一个术语.

其他推荐答案

除了埃尔温(Erwin

分区不是一个神奇的子弹.对于哪些分区非常有用,有一些非常具体的东西.如果这些非常具体的事情都不适用于您,那么您不能指望分区的性能提高,很可能会减少.

要正确进行分区,您需要对使用模式或数据加载和卸载模式有深入的了解.

本文地址:https://www.itbaoku.cn/post/1763797.html