PolarDB:Contoh

Buat tabel yang dipartisi berdasarkan daftar

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')
);

Kueri pada kolom kunci partisi

Jalankan kueri yang dibatasi pada kolom country, yaitu kunci partisi:

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

Output yang diharapkan:

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)

Perencana hanya memindai partisi sales_asia, yang menyimpan baris dengan nilai country sebesar INDIA. Partisi europe dan americas dipangkas dari jalur pencarian.

Kueri pada kolom non-kunci partisi

Jalankan kueri pada dept_no, yang bukan kunci partisi:

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

Output yang diharapkan:

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)

Karena dept_no bukan kunci partisi, perencana tidak dapat menentukan partisi mana yang mungkin berisi baris yang cocok. Akibatnya, semua partisi dipindai.

Kueri pada tabel bersubpartisi

Eksklusi kendala (constraint exclusion) juga berlaku untuk tabel bersubpartisi. Buat tabel yang dipartisi berdasarkan date (rentang/range) dengan subpartisi berdasarkan country (daftar/list):

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')
  )
);

Lakukan kueri pada tabel tersebut dengan kondisi pada kunci partisi maupun kunci subpartisi:

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

Output yang diharapkan:

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)

Perencana kueri memangkas partisi dan subpartisi yang tidak mungkin berisi baris yang cocok. Dari sembilan subpartisi yang tersebar dalam tiga tahun, hanya sales_2012 dan sales_americas_2012 yang tetap berada dalam jalur pencarian.

Buat tabel yang dipartisi berdasarkan hash

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

Verifikasi pemangkasan untuk UPDATE

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

Output yang diharapkan:

                                       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)

Verifikasi pemangkasan untuk DELETE

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

Output yang diharapkan:

                                       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)

Pada kedua rencana tersebut, Subplans Removed: 1 menunjukkan bahwa satu partisi dipangkas pada waktu proses. Karena LEAST(1, 2) adalah panggilan fungsi yang hasilnya hanya diketahui pada waktu proses, perencana tidak dapat mengeliminasi partisi tersebut pada tahap perencanaan—melainkan melakukannya selama eksekusi. Operasi UPDATE dan DELETE hanya menargetkan t1_hash_p1 dan t1_hash_p2, sehingga partisi lainnya tidak tersentuh.