PostgreSQL 与 Oracle 访问分区表执行计划差异
熟悉Oracle 的DBA都知道，Oracle 访问分区表时，对于没有提供分区条件的，也就是在无法使用分区剪枝情况下，优化器会根据全局的统计信息制定执行计划，该执行计划针对所有分区适用。在分析利弊之前，我们先来看个例子：
一、Oracle
构建数据：
create table part_tab01(part_key char(1),state char(1),desc_content varchar(4000)) partition by range(part_key) ( partition part_0 values less than(1), partition part_1 values less than(2) ); insert into part_tab01 select '0','0',rpad('a',1000,'a') from dba_objects where rownum<10001; insert into part_tab01 select '1','1',rpad('a',1000,'a') from dba_objects where rownum<10001; insert into part_tab01 select * from part_tab01; insert into part_tab01 select * from part_tab01; insert into part_tab01 select * from part_tab01; insert into part_tab01 select * from part_tab01; insert into part_tab01 select * from part_tab01; insert into part_tab01 select * from part_tab01; insert into part_tab01 select '1','0',rpad('a',1000,'a') from dba_objects where rownum<11; insert into part_tab01 select '0','1',rpad('a',1000,'a') from dba_objects where rownum<11; create index idx_part_tab01_state on part_tab01(state) local;
从数据的分布可以得出结论，最优的访问方法：对于不同的分区，访问不同的state 值，应采用不同的表访问方法。
执行计划：
SQL> select * from part_tab01 where state='1'; 640010 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4116343635 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 640K| 613M| 49576 (1)| 00:00:02 | | | | 1 | PARTITION RANGE ALL| | 640K| 613M| 49576 (1)| 00:00:02 | 1 | 2 | |* 2 | TABLE ACCESS FULL | PART_TAB01 | 640K| 613M| 49576 (1)| 00:00:02 | 1 | 2 | -------------------------------------------------------------------------------------------------- SQL> select * from part_tab01 where state='1' and part_key='0'; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1952449058 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 10050 | 5 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 10 | 10050 | 5 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PART_TAB01 | 10 | 10050 | 5 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | IDX_PART_TAB01_STATE | 10 | | 3 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------------------------------------------- SQL> select * from part_tab01 where state='1' and part_key='1'; 640000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4278184147 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 640K| 613M| 24793 (1)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 640K| 613M| 24793 (1)| 00:00:01 | 2 | 2 | |* 2 | TABLE ACCESS FULL | PART_TAB01 | 640K| 613M| 24793 (1)| 00:00:01 | 2 | 2 | -----------------------------------------------------------------------------------------------------
可以看到，在没有分区条件的情况下，Oracle 是针对全表采用统一的执行。实际针对该SQL，最好的访问方法应该是：part_0 全表，part_1 索引
二、PostgreSQL 执行计划
构建数据：
create table part_tab01(part_key char(1),state char(1),desc_content text) partition by range(part_key) ( partition part_0 values less than(1), partition part_1 values less than(2) ); insert into part_tab01 select '0','0',repeat('a',1000) from generate_series(1,1000000); insert into part_tab01 select '0','1',repeat('b',1000) from generate_series(1,10); insert into part_tab01 select '1','1',repeat('a',1000) from generate_series(1,1000000); insert into part_tab01 select '1','0',repeat('b',1000) from generate_series(1,10); create index idx_part_tab01_state on part_tab01(state);
执行计划：针对不同分区，有不同的执行计划。
test=# explain analyze select * from part_tab01 where state='1';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.42..160363.43 rows=1000000 width=1008) (actual time=0.022..484.005 rows=1000010 loops=1)
-> Index Scan using part_tab01_part_0_state_idx on part_tab01_part_0 (cost=0.42..4.44 rows=1 width=1008) (actual time=0.022..0.024 rows=10 loops=1)
Index Cond: (state = '1'::bpchar)
-> Seq Scan on part_tab01_part_1 (cost=0.00..155358.99 rows=999999 width=1008) (actual time=0.011..424.713 rows=1000000 loops=1)
Filter: (state = '1'::bpchar)
Rows Removed by Filter: 10
Planning Time: 0.293 ms
Execution Time: 515.549 ms
(8 rows)
test=# explain analyze select * from part_tab01 where state='0';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..160363.68 rows=1000014 width=1008) (actual time=0.022..517.127 rows=1000010 loops=1)
-> Seq Scan on part_tab01_part_0 (cost=0.00..155359.16 rows=1000013 width=1008) (actual time=0.022..451.523 rows=1000000 loops=1)
Filter: (state = '0'::bpchar)
Rows Removed by Filter: 10
-> Index Scan using part_tab01_part_1_state_idx on part_tab01_part_1 (cost=0.42..4.44 rows=1 width=1008) (actual time=0.032..0.035 rows=10 loops=1)
Index Cond: (state = '0'::bpchar)
Planning Time: 0.090 ms
Execution Time: 547.486 ms
(8 rows)
三、结论
从本例可以看出，在不同分区数据分布不同的场景下，PostgreSQL针对不同分区有独立的执行计划是更优方法。现实中典型的场景，如：按时间分区的工单表，历史分区可能大部分工单是结束状态，而当前分区工单可能大部分是非结束状态。
