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針對不同分區有獨立的執行計劃是更優方法。現實中典型的場景，如：按時間分區的工單表，歷史分區可能大部分工單是結束狀態，而當前分區工單可能大部分是非結束狀態。
