未使用Aurora Postgresql按列表分区

在Aurora PostgreSQL数据库中，我有两个表：

表A，未分区

CREATE TABLE tableA (
    a_index text NOT NULL,
    a_description text NOT NULL,
    a_client text NULL,
    CONSTRAINT a_pk PRIMARY KEY (a_index)
);
CREATE INDEX a_index_pk_idx ON tableA USING hash (a_index);

表B，按列表分区

CREATE TABLE tableB (
    b_id serial4 NOT NULL,
    b_a_index text NOT NULL,
    b_stindex text NOT NULL,
    b_status text NOT NULL,
    CONSTRAINT b_pk PRIMARY KEY (b_id, b_a_index)
)
PARTITION BY LIST (b_a_index);

ALTER TABLE tableB ADD CONSTRAINT b_a_fk FOREIGN KEY (b_a_index) REFERENCES tableA(a_index) MATCH FULL;

当我尝试在两个表之间进行简单连接时，在执行计划中我注意到来自tableB的所有分区都被扫描（Parallel Seq Scan）

SELECT * 
FROM tableA
INNER JOIN tableB
ON b_a_index = a_index
WHERE a_client='ABC';

有人能告诉我为什么不像我期待的那样只扫描相应的分区吗？谢谢！
L.E：查询计划：

Gather  (cost=1016.54..1263414.50 rows=402743 width=176) (actual time=9108.282..27685.157 rows=9670372 loops=1)
  Output: tableB.b_id, tableB.b_a_index, tableB.b_stindex, tableB.b_status, tableA.a_index, tableA.a_description, tableA.a_client
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=19716 read=688292
  I/O Timings: read=19374676.659
  ->  Hash Join  (cost=16.54..1222140.20 rows=167810 width=176) (actual time=17745.407..23661.627 rows=3223457 loops=3)
        Output: tableB.b_id, tableB.b_a_index, tableB.b_stindex, tableB.b_status, tableA.a_index, tableA.a_description, tableA.a_client
        Inner Unique: true
        Hash Cond: (tableB.b_a_index = tableA.a_index)
        Buffers: shared hit=19716 read=688292
        I/O Timings: read=19374676.659
        Worker 0:  actual time=28921.541..28921.545 rows=0 loops=1
          Buffers: shared hit=7748 read=311076
          I/O Timings: read=6964320.875
        Worker 1:  actual time=15206.799..17833.363 rows=1421435 loops=1
          Buffers: shared hit=4540 read=155998
          I/O Timings: read=5568948.894
        ->  Parallel Append  (cost=0.00..1145148.10 rows=29086961 width=48) (actual time=1.798..15132.937 rows=23276854 loops=3)
              Buffers: shared hit=19585 read=688292
              I/O Timings: read=19374676.659
              Worker 0:  actual time=0.978..19849.500 rows=31198966 loops=1
                Buffers: shared hit=7683 read=311076
                I/O Timings: read=6964320.875
              Worker 1:  actual time=4.408..11818.087 rows=16000668 loops=1
                Buffers: shared hit=4475 read=155998
                I/O Timings: read=5568948.894
              ->  Parallel Seq Scan on tableB_p1 tableB_5  (cost=0.00..548244.96 rows=15856596 width=48) (actual time=1.070..7624.836 rows=19030874 loops=2)
                    Output: tableB_5.b_id, tableB_5.b_a_index, tableB_5.b_stindex, tableB_5.b_status
                    Buffers: shared hit=7683 read=381484
                    I/O Timings: read=8189818.169
                    Worker 0:  actual time=0.977..13108.626 rows=31198966 loops=1
                      Buffers: shared hit=7683 read=311076
                      I/O Timings: read=6964320.875
              ->  Parallel Seq Scan on tableB_p2 tableB_1  (cost=0.00..206850.64 rows=6072264 width=48) (actual time=4.406..7034.632 rows=14579233 loops=1)
                    Output: tableB_1.b_id, tableB_1.b_a_index, tableB_1.b_stindex, tableB_1.b_status
                    Buffers: shared hit=2933 read=143195
                    I/O Timings: read=3630231.053
                    Worker 1:  actual time=4.406..7034.632 rows=14579233 loops=1
                      Buffers: shared hit=2933 read=143195
                      I/O Timings: read=3630231.053
              ->  Parallel Seq Scan on tableB_p3 tableB_3  (cost=0.00..137807.15 rows=4025415 width=48) (actual time=2.213..1730.014 rows=4835186 loops=2)
                    Output: tableB_3.b_id, tableB_3.b_a_index, tableB_3.b_stindex, tableB_3.b_status
                    Buffers: shared hit=7930 read=89359
                    I/O Timings: read=5369919.724
                    Worker 1:  actual time=0.969..735.713 rows=1421435 loops=1
                      Buffers: shared hit=1542 read=12803
                      I/O Timings: read=1938717.840
              ->  Parallel Seq Scan on tableB_p4 tableB_4  (cost=0.00..105144.03 rows=3088803 width=48) (actual time=2.136..3916.330 rows=7414429 loops=1)
                    Output: tableB_4.b_id, tableB_4.b_a_index, tableB_4.b_stindex, tableB_4.b_status
                    Buffers: shared hit=3 read=74253
                    I/O Timings: read=2184699.048
              ->  Parallel Seq Scan on tableB_p5 tableB_6  (cost=0.00..1653.41 rows=61641 width=47) (actual time=0.007..11.395 rows=104780 loops=1)
                    Output: tableB_6.b_id, tableB_6.b_a_index, tableB_6.b_stindex, tableB_6.b_status
                    Buffers: shared hit=1036 read=1
                    I/O Timings: read=8.664
              ->  Parallel Seq Scan on tableB_p6 tableB_2  (cost=0.00..13.12 rows=312 width=124) (actual time=0.002..0.002 rows=0 loops=1)
                    Output: tableB_2.b_id, tableB_2.b_a_index, tableB_2.b_stindex, tableB_2.b_status
        ->  Hash  (cost=16.50..16.50 rows=3 width=128) (actual time=0.019..0.020 rows=1 loops=3)
              Output: tableA.a_index, tableA.a_description, tableA.a_client
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              Buffers: shared hit=3
              Worker 0:  actual time=0.021..0.022 rows=1 loops=1
                Buffers: shared hit=1
              Worker 1:  actual time=0.024..0.024 rows=1 loops=1
                Buffers: shared hit=1
              ->  Seq Scan on tableA  (cost=0.00..16.50 rows=3 width=128) (actual time=0.011..0.013 rows=1 loops=3)
                    Output: tableA.a_index, tableA.a_description, tableA.a_client
                    Filter: (tableA.a_client = 'ABC'::text)
                    Rows Removed by Filter: 4
                    Buffers: shared hit=3
                    Worker 0:  actual time=0.012..0.013 rows=1 loops=1
                      Buffers: shared hit=1
                    Worker 1:  actual time=0.014..0.015 rows=1 loops=1
                      Buffers: shared hit=1
Planning:
  Buffers: shared read=5
  I/O Timings: read=4.690
Planning Time: 5.357 ms
Execution Time: 28976.177 ms

分区修剪似乎不适用于散列和合并连接。由于某种原因，查询优化器为您的查询选择散列连接。如果您想使用分区修剪功能，您需要强制查询优化器使用嵌套循环连接。为此，您可以使用Aurora PostgreSQL query plan management或调优Server Configuration - Query Planning。我认为第一种选择比第二种更好。
关键问题是使用分区修剪和嵌套循环是否是您的情况下的最佳解决方案，您需要检查。当您强制嵌套循环连接时，查询计划如下所示（其中提到：“（从未执行）”）。

"Nested Loop  (cost=0.42..491542.61 rows=59 width=148) (actual time=0.047..1863.185 rows=6000000 loops=1)"
"  ->  Index Scan using a_client_a_index_idx on tablea  (cost=0.42..8.44 rows=1 width=99) (actual time=0.034..0.043 rows=2 loops=1)"
"        Index Cond: (a_client = 'ABC'::text)"
"  ->  Append  (cost=0.00..361534.15 rows=13000003 width=49) (actual time=0.012..592.062 rows=3000000 loops=2)"
"        ->  Seq Scan on tableb_1  (cost=0.00..68428.00 rows=3000000 width=49) (actual time=0.006..452.645 rows=3000000 loops=1)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_2  (cost=0.00..45619.00 rows=2000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_3  (cost=0.00..22810.00 rows=1000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_4  (cost=0.00..22810.00 rows=1000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_5  (cost=0.00..68428.00 rows=3000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_6  (cost=0.00..68428.00 rows=3000000 width=49) (actual time=0.007..351.330 rows=3000000 loops=1)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Bitmap Heap Scan on tableb_7  (cost=4.02..11.13 rows=3 width=100) (never executed)"
"              Recheck Cond: (b_a_index = tablea.a_index)"
"              ->  Bitmap Index Scan on tableb_7_b_a_index_idx  (cost=0.00..4.02 rows=3 width=0) (never executed)"
"                    Index Cond: (b_a_index = tablea.a_index)"
"Planning Time: 0.372 ms"
"Execution Time: 2070.149 ms"

未使用Aurora Postgresql按列表分区

1条答案

相关问题

热门标签

最新问答