例 - PolarDB - Alibaba Cloud ドキュメントセンター

EXPLAIN を使用して、パーティションプルーニングが期待通りに動作していることを確認します。プルーニングが有効な場合、実行計画には一致する行を含むパーティションのみが表示され、関係のないパーティションは完全にスキップされます。

SELECT

以下の例では、リストパーティション方式で作成された sales テーブルを使用し、クエリプランナーがパーティションキー列と非パーティションキー列のいずれに対してクエリを実行した場合に異なる振る舞いを示します。

リストパーティション方式のテーブルを作成

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

パーティションキー列に対するクエリ

パーティションキーである country 列に対して制約付きクエリを実行します：

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';

期待される出力：

postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
                        QUERY PLAN
---------------------------------------------------
 Append
   ->  Seq Scan on sales
         Filter: ((country)::text = 'INDIA'::text)
   ->  Seq Scan on sales_asia
         Filter: ((country)::text = 'INDIA'::text)
(5 rows)

プランナーは、sales_asia パーティションのみをスキャンします。これは、country が INDIA である行を格納するパーティションです。europe および americas の各パーティションは、検索パスからプルーニングされます。

非パーティションキー列に対するクエリ

パーティションキーではない dept_no 列に対してクエリを実行します：

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';

期待される出力：

postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';
               QUERY PLAN
-----------------------------------------
 Append
   ->  Seq Scan on sales
         Filter: (dept_no = 30::numeric)
   ->  Seq Scan on sales_europe
         Filter: (dept_no = 30::numeric)
   ->  Seq Scan on sales_asia
         Filter: (dept_no = 30::numeric)
   ->  Seq Scan on sales_americas
         Filter: (dept_no = 30::numeric)
(9 rows)

dept_no はパーティションキーではないため、プランナーは一致する行を含む可能性のあるパーティションを特定できません。そのため、すべてのパーティションをスキャンします。

サブパーティション化されたテーブルに対するクエリ

制約除外（Constraint exclusion）は、サブパーティション化されたテーブルにも適用されます。date 列（範囲パーティション）でパーティション化し、さらに country 列（リストパーティション）でサブパーティション化されたテーブルを作成します：

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date) SUBPARTITION BY LIST (country)
(
  PARTITION "2011" VALUES LESS THAN('01-JAN-2012')
  (
    SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),
    SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),
    SUBPARTITION americas_2011 VALUES ('US', 'CANADA')
  ),
  PARTITION "2012" VALUES LESS THAN('01-JAN-2013')
  (
    SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),
    SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),
    SUBPARTITION americas_2012 VALUES ('US', 'CANADA')
  ),
  PARTITION "2013" VALUES LESS THAN('01-JAN-2014')
  (
    SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
    SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
    SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
  )
);

パーティションキーおよびサブパーティションキーの両方に対して条件を指定してテーブルをクエリします：

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';

期待される出力：

postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';
                                         QUERY PLAN
-----------------------------------------------------------------------------
 Append
   ->  Seq Scan on sales
         Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
   ->  Seq Scan on sales_2012
         Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
   ->  Seq Scan on sales_americas_2012
         Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
(7 rows)

クエリプランナーは、一致する行を含む可能性がないパーティションおよびサブパーティションを両方ともプルーニングします。3 年分の合計 9 個のサブパーティションのうち、検索パスに残るのは sales_2012 および sales_americas_2012 のみです。

UPDATE および DELETE

パーティションプルーニングは、パーティションテーブル内の行を更新または削除する際のランタイムでも適用されます。

ハッシュパーティション方式のテーブルを作成

create table t1_hash (id int , value int) partition by hash(id) partitions 4;

UPDATE 操作におけるプルーニングの確認

postgres=# explain update t1_hash set value = value+1 where id = least(1,2);

期待される出力：

                                       QUERY PLAN
-------------------------------------------------------------------------
 Update on t1_hash  (cost=0.00..92.18 rows=24 width=14)
   Update on t1_hash_p1
   Update on t1_hash_p2 t1_hash
   ->  Append  (cost=0.00..92.18 rows=24 width=14)
         Subplans Removed: 1
         ->  Seq Scan on t1_hash_p1  (cost=0.00..46.03 rows=12 width=14)
               Filter: (id = LEAST(1, 2))
(7 rows)

DELETE 操作におけるプルーニングの確認

postgres=# explain delete from t1_hash where id = least(1,2);

期待される出力：

                                       QUERY PLAN
-------------------------------------------------------------------------
 Delete on t1_hash  (cost=0.00..92.12 rows=24 width=10)
   Delete on t1_hash_p1
   Delete on t1_hash_p2 t1_hash
   ->  Append  (cost=0.00..92.12 rows=24 width=10)
         Subplans Removed: 1
         ->  Seq Scan on t1_hash_p1  (cost=0.00..46.00 rows=12 width=10)
               Filter: (id = LEAST(1, 2))
(7 rows)

どちらの実行計画においても、Subplans Removed: 1 という記述は、実行時に 1 つのパーティションがプルーニングされたことを示しています。LEAST(1, 2) は実行時まで結果が確定しない関数呼び出しであるため、プランナーは計画段階ではパーティションを除外できず、代わりに実行時にプルーニングを行います。UPDATE および DELETE 操作は、t1_hash_p1 および t1_hash_p2 のみを対象とし、残りのパーティションには影響を与えません。