問題

我在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(按列表分割槽)分割槽.當將新客戶端插入到客戶端表中時,觸發器為檔案表建立新分割槽. 該檔案表有一個引用客戶端_id上的客戶端表的外來鍵約束。

當我執行此SQL(其中c.client_id = 1)時,一切似乎都很好:

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

使用分割槽修剪,只掃描分割槽檔案_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,掃描了 file-table 中的 alle 分割槽。

那麼,每個選擇應該使用表被分割槽的列嗎?資料庫是否無法偏離分割槽修剪標準?


編輯: 增加一些資訊:

過去,我一直在使用Oracle資料庫.

執行計劃會有類似的東西

  1. 使用客戶端名在客戶端表上進行完整的表掃描,以找出client_id.
  2. 對檔案表進行“PARTION LIST”訪問,其中SQL Developer說明PARTION_START = KEY和PARTION_STOP = KEY,以表明在計算執行計劃時不知道確切的分割槽,但訪問將只完成一個解析列表,該列表在客戶端表中找到的client_id上計算.

這也是我在Postgresql中預期的.

  最佳答案

Documentation 指出動態分割槽修剪是可能的

(...)在查詢計劃的實際執行過程中.也可以在此執行分割槽修剪以刪除分割槽,使用實際查詢執行期間只知道的值.這包括來自可執行時引數的值和值,例如來自引數化巢狀迴圈連線的值.

如果我正確理解它,它適用於帶有子查詢的已準備語句或查詢,它們提供分割槽鍵值作為引數.使用explain analyse來檢視動態修剪(我的示例資料包含三個分割槽中的100萬行):

 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 的掃描是 never executed

在回答您的確切問題時,該分割槽修剪問題中描述的帶有連線的查詢在Postgres中沒有實現(尚未?)

  相同標籤的其他問題

postgresqlpartitioningdatabase-partitioningpruning