| CREATE OR REPLACE FUNCTION index_info(regclass) |
| RETURNS TABLE(indexname regclass, parentindex regclass, isleaf boolean, level int, tablename regclass, indpred text, indnatts smallint, indnkeyatts smallint, indkey int2vector) |
| AS |
| $$ select t.*, indrelid::regclass as tablename, pg_get_expr(indpred, relid), indnatts, indnkeyatts, indkey |
| from pg_partition_tree($1) as t, pg_index as i where relid = indexrelid |
| $$ |
| LANGUAGE SQL EXECUTE ON COORDINATOR; |
| -- |
| -- ************************************************************ |
| -- * Scenario 1 |
| -- * - single level partition. |
| -- * - no default parts, no dropped columns |
| -- ************************************************************ |
| -- |
| create table part_table1 |
| ( |
| part_key int, |
| content text |
| ) |
| partition by range(part_key) |
| ( |
| partition part1 start ('1') end ('10'), |
| partition part2 start('11') end ('20') |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'part_key' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- physical index on all parts |
| -- output show 1 logical index |
| create index rp_i1 on part_table1(part_key); |
| select * from index_info('rp_i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| --------------------------------------+-------------+--------+-------+-------------------------+---------+----------+-------------+-------- |
| rp_i1 | | f | 0 | part_table1 | | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part1 | | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part2 | | 1 | 1 | 1 |
| (3 rows) |
| |
| -- partial index on all part |
| -- output shows 1 logical index with partial predicate |
| create index parent_partial_ind1 on part_table1(part_key) |
| where part_key >=1 and part_key < 10; |
| select * from index_info('parent_partial_ind1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ---------------------------------------+---------------------+--------+-------+-------------------------+---------------------------------------+----------+-------------+-------- |
| parent_partial_ind1 | | f | 0 | part_table1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part2 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| (3 rows) |
| |
| -- add a new part - index automatically created |
| alter table part_table1 add partition part4 start('31') end ('40'); |
| select * from index_info('rp_i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| --------------------------------------+-------------+--------+-------+-------------------------+---------+----------+-------------+-------- |
| rp_i1 | | f | 0 | part_table1 | | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part1 | | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part2 | | 1 | 1 | 1 |
| part_table1_1_prt_part4_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part4 | | 1 | 1 | 1 |
| (4 rows) |
| |
| select * from index_info('parent_partial_ind1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ---------------------------------------+---------------------+--------+-------+-------------------------+---------------------------------------+----------+-------------+-------- |
| parent_partial_ind1 | | f | 0 | part_table1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part2 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part4_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part4 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| (4 rows) |
| |
| -- create index on just 1 part |
| create index rp_i2 on part_table1_1_prt_part4(part_key, content); |
| -- no result as not partitioned index |
| select * from index_info('rp_i2'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| -----------+-------------+--------+-------+-----------+---------+----------+-------------+-------- |
| (0 rows) |
| |
| select relkind from pg_class where relname = 'rp_i2'; |
| relkind |
| --------- |
| i |
| (1 row) |
| |
| -- split a part into 2 parts - index created on both the parts |
| -- the existing part4 along with indexes is dropped |
| -- and 2 new parts are added - NO new indexes create |
| alter table part_table1 split partition for ('31') at ('35') into (partition part41, partition part42); |
| select * from index_info('rp_i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ---------------------------------------+-------------+--------+-------+--------------------------+---------+----------+-------------+-------- |
| rp_i1 | | f | 0 | part_table1 | | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part1 | | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part2 | | 1 | 1 | 1 |
| part_table1_1_prt_part41_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part41 | | 1 | 1 | 1 |
| part_table1_1_prt_part42_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part42 | | 1 | 1 | 1 |
| (5 rows) |
| |
| select * from index_info('parent_partial_ind1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ----------------------------------------+---------------------+--------+-------+--------------------------+---------------------------------------+----------+-------------+-------- |
| parent_partial_ind1 | | f | 0 | part_table1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part2_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part2 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part41_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part41 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part42_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part42 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| (5 rows) |
| |
| select relkind from pg_class where relname = 'rp_i2'; |
| relkind |
| --------- |
| (0 rows) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 2 |
| -- * - single level partition. |
| -- * - testing exchange |
| -- ************************************************************ |
| -- |
| create table exchange_tab |
| ( |
| part_key int, |
| content text |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'part_key' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- These indexes must exist on the table before we can exchange, because |
| -- they exist as a partitioned index on part_table1 |
| create index like_rp_i1 on exchange_tab(part_key); |
| create index like_parent_partial_ind1 on exchange_tab(part_key) |
| where part_key >=1 and part_key < 10; |
| create index e1 on exchange_tab(lower(content)); |
| -- exchanging with part2 |
| -- the 3 indexs on exchange_tab will move along and become part of partition |
| alter table part_table1 exchange partition for ('11') with table exchange_tab; |
| select relname, relkind from pg_class where oid in (select indexrelid from pg_index where indrelid = 'exchange_tab'::regclass); |
| relname | relkind |
| ---------------------------------------+--------- |
| part_table1_1_prt_part2_part_key_idx | i |
| part_table1_1_prt_part2_part_key_idx1 | i |
| (2 rows) |
| |
| select indexrelid::regclass as indexname, indrelid::regclass as tablename from pg_index where indexrelid = 'e1'::regclass; |
| indexname | tablename |
| -----------+------------------------- |
| e1 | part_table1_1_prt_part2 |
| (1 row) |
| |
| select * from index_info('rp_i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ---------------------------------------+-------------+--------+-------+--------------------------+---------+----------+-------------+-------- |
| rp_i1 | | f | 0 | part_table1 | | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part1 | | 1 | 1 | 1 |
| part_table1_1_prt_part41_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part41 | | 1 | 1 | 1 |
| part_table1_1_prt_part42_part_key_idx | rp_i1 | t | 1 | part_table1_1_prt_part42 | | 1 | 1 | 1 |
| like_rp_i1 | rp_i1 | t | 1 | part_table1_1_prt_part2 | | 1 | 1 | 1 |
| (5 rows) |
| |
| select * from index_info('parent_partial_ind1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ----------------------------------------+---------------------+--------+-------+--------------------------+---------------------------------------+----------+-------------+-------- |
| parent_partial_ind1 | | f | 0 | part_table1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part1_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part1 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part41_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part41 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| part_table1_1_prt_part42_part_key_idx1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part42 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| like_parent_partial_ind1 | parent_partial_ind1 | t | 1 | part_table1_1_prt_part2 | ((part_key >= 1) AND (part_key < 10)) | 1 | 1 | 1 |
| (5 rows) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 3 |
| -- * - multi-level partitions |
| -- ************************************************************ |
| -- |
| create table part_table2 (trans_id int, date date, region text) |
| distributed by (trans_id) |
| partition by range(date) |
| subpartition by list(region) |
| subpartition template |
| ( |
| subpartition usa values ('usa'), |
| subpartition asia values ('asia'), |
| subpartition europe values ('europe'), |
| default subpartition other_regions) |
| (start (date '2008-01-01') end (date '2009-01-01') |
| every (interval '6 month'), |
| default partition outlying_years); |
| -- create indexes (will be created on all parts/subparts) |
| create index first_index on part_table2(trans_id); |
| select * from index_info('first_index'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| -----------------------------------------------------------------+-----------------------------------------------+--------+-------+------------------------------------------------------+---------+----------+-------------+-------- |
| first_index | | f | 0 | part_table2 | | 1 | 1 | 1 |
| part_table2_1_prt_2_trans_id_idx | first_index | f | 1 | part_table2_1_prt_2 | | 1 | 1 | 1 |
| part_table2_1_prt_3_trans_id_idx | first_index | f | 1 | part_table2_1_prt_3 | | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_trans_id_idx | first_index | f | 1 | part_table2_1_prt_outlying_years | | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_asia_trans_id_idx | part_table2_1_prt_2_trans_id_idx | t | 2 | part_table2_1_prt_2_2_prt_asia | | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_europe_trans_id_idx | part_table2_1_prt_2_trans_id_idx | t | 2 | part_table2_1_prt_2_2_prt_europe | | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_usa_trans_id_idx | part_table2_1_prt_2_trans_id_idx | t | 2 | part_table2_1_prt_2_2_prt_usa | | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_other_regions_trans_id_idx | part_table2_1_prt_2_trans_id_idx | t | 2 | part_table2_1_prt_2_2_prt_other_regions | | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_asia_trans_id_idx | part_table2_1_prt_3_trans_id_idx | t | 2 | part_table2_1_prt_3_2_prt_asia | | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_europe_trans_id_idx | part_table2_1_prt_3_trans_id_idx | t | 2 | part_table2_1_prt_3_2_prt_europe | | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_usa_trans_id_idx | part_table2_1_prt_3_trans_id_idx | t | 2 | part_table2_1_prt_3_2_prt_usa | | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_other_regions_trans_id_idx | part_table2_1_prt_3_trans_id_idx | t | 2 | part_table2_1_prt_3_2_prt_other_regions | | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_asia_trans_id_idx | part_table2_1_prt_outlying_years_trans_id_idx | t | 2 | part_table2_1_prt_outlying_years_2_prt_asia | | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_europe_trans_id_idx | part_table2_1_prt_outlying_years_trans_id_idx | t | 2 | part_table2_1_prt_outlying_years_2_prt_europe | | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_usa_trans_id_idx | part_table2_1_prt_outlying_years_trans_id_idx | t | 2 | part_table2_1_prt_outlying_years_2_prt_usa | | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_other_regio_trans_id_idx | part_table2_1_prt_outlying_years_trans_id_idx | t | 2 | part_table2_1_prt_outlying_years_2_prt_other_regions | | 1 | 1 | 1 |
| (16 rows) |
| |
| -- create a partial index on part_table2 (will be created on all parts/subparts) |
| create index second_index on part_table2(trans_id) |
| where trans_id >=1000 and trans_id < 2000; |
| select * from index_info('second_index'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| -----------------------------------------------------------------+------------------------------------------------+--------+-------+------------------------------------------------------+--------------------------------------------+----------+-------------+-------- |
| second_index | | f | 0 | part_table2 | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_2_trans_id_idx1 | second_index | f | 1 | part_table2_1_prt_2 | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_3_trans_id_idx1 | second_index | f | 1 | part_table2_1_prt_3 | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_trans_id_idx1 | second_index | f | 1 | part_table2_1_prt_outlying_years | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_asia_trans_id_idx1 | part_table2_1_prt_2_trans_id_idx1 | t | 2 | part_table2_1_prt_2_2_prt_asia | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_europe_trans_id_idx1 | part_table2_1_prt_2_trans_id_idx1 | t | 2 | part_table2_1_prt_2_2_prt_europe | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_usa_trans_id_idx1 | part_table2_1_prt_2_trans_id_idx1 | t | 2 | part_table2_1_prt_2_2_prt_usa | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_2_2_prt_other_regions_trans_id_idx1 | part_table2_1_prt_2_trans_id_idx1 | t | 2 | part_table2_1_prt_2_2_prt_other_regions | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_asia_trans_id_idx1 | part_table2_1_prt_3_trans_id_idx1 | t | 2 | part_table2_1_prt_3_2_prt_asia | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_europe_trans_id_idx1 | part_table2_1_prt_3_trans_id_idx1 | t | 2 | part_table2_1_prt_3_2_prt_europe | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_usa_trans_id_idx1 | part_table2_1_prt_3_trans_id_idx1 | t | 2 | part_table2_1_prt_3_2_prt_usa | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_3_2_prt_other_regions_trans_id_idx1 | part_table2_1_prt_3_trans_id_idx1 | t | 2 | part_table2_1_prt_3_2_prt_other_regions | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_asia_trans_id_idx1 | part_table2_1_prt_outlying_years_trans_id_idx1 | t | 2 | part_table2_1_prt_outlying_years_2_prt_asia | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_europe_trans_id_idx1 | part_table2_1_prt_outlying_years_trans_id_idx1 | t | 2 | part_table2_1_prt_outlying_years_2_prt_europe | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_usa_trans_id_idx1 | part_table2_1_prt_outlying_years_trans_id_idx1 | t | 2 | part_table2_1_prt_outlying_years_2_prt_usa | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| part_table2_1_prt_outlying_years_2_prt_other_regi_trans_id_idx1 | part_table2_1_prt_outlying_years_trans_id_idx1 | t | 2 | part_table2_1_prt_outlying_years_2_prt_other_regions | ((trans_id >= 1000) AND (trans_id < 2000)) | 1 | 1 | 1 |
| (16 rows) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 3 |
| -- * - multi-level partitions |
| -- * - truncate partition |
| -- * - dropped columns |
| -- ************************************************************ |
| -- |
| create table part_table3 |
| ( |
| date date, |
| region text, |
| region1 text, |
| amount decimal(10,2) |
| ) |
| partition by range(date) |
| subpartition by list(region) |
| ( |
| partition part1 start(date '2008-01-01') end(date '2009-01-01') |
| ( |
| subpartition usa values ('usa'), |
| subpartition asia values ('asia'), |
| default subpartition def |
| ), |
| partition part2 start(date '2009-01-01') end(date '2010-01-01') |
| ( |
| subpartition usa values ('usa'), |
| subpartition asia values ('asia') |
| ) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'date' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- insert some data |
| insert into part_table3 values ('2008-02-02', 'usa', 'Texas', 10.05), ('2008-03-03', 'asia', 'China', 1.01); |
| insert into part_table3 values ('2009-02-02', 'usa', 'Texas', 10.05), ('2009-03-03', 'asia', 'China', 1.01); |
| -- index on atts 1, 4 |
| create index i1 on part_table3(date, amount); |
| select * from index_info('i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ----------------------------------------------------+-----------------------------------------+--------+-------+------------------------------------+---------+----------+-------------+-------- |
| i1 | | f | 0 | part_table3 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_date_amount_idx | i1 | f | 1 | part_table3_1_prt_part1 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_date_amount_idx | i1 | f | 1 | part_table3_1_prt_part2 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_2_prt_asia_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_asia | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_2_prt_usa_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_usa | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_2_prt_def_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_def | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_2_prt_asia_date_amount_idx | part_table3_1_prt_part2_date_amount_idx | t | 2 | part_table3_1_prt_part2_2_prt_asia | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_2_prt_usa_date_amount_idx | part_table3_1_prt_part2_date_amount_idx | t | 2 | part_table3_1_prt_part2_2_prt_usa | | 2 | 2 | 1 4 |
| (8 rows) |
| |
| -- truncate partitions until table is empty |
| select * from part_table3; |
| date | region | region1 | amount |
| ------------+--------+---------+-------- |
| 02-02-2008 | usa | Texas | 10.05 |
| 02-02-2009 | usa | Texas | 10.05 |
| 03-03-2008 | asia | China | 1.01 |
| 03-03-2009 | asia | China | 1.01 |
| (4 rows) |
| |
| truncate part_table3_1_prt_part1_2_prt_asia; |
| select * from part_table3; |
| date | region | region1 | amount |
| ------------+--------+---------+-------- |
| 02-02-2008 | usa | Texas | 10.05 |
| 02-02-2009 | usa | Texas | 10.05 |
| 03-03-2009 | asia | China | 1.01 |
| (3 rows) |
| |
| alter table part_table3 truncate partition part1; |
| select * from part_table3; |
| date | region | region1 | amount |
| ------------+--------+---------+-------- |
| 02-02-2009 | usa | Texas | 10.05 |
| 03-03-2009 | asia | China | 1.01 |
| (2 rows) |
| |
| alter table part_table3 alter partition part2 truncate partition usa; |
| select * from part_table3; |
| date | region | region1 | amount |
| ------------+--------+---------+-------- |
| 03-03-2009 | asia | China | 1.01 |
| (1 row) |
| |
| alter table part_table3 truncate partition part2; |
| select * from part_table3; |
| date | region | region1 | amount |
| ------+--------+---------+-------- |
| (0 rows) |
| |
| -- drop column region1 |
| alter table part_table3 drop column region1; |
| -- the index i1 on this part has atts - 1 3 |
| alter table part_table3 add partition part3 start(date '2010-01-01') end (date '2011-01-01') |
| (subpartition usa values('usa')); |
| select * from index_info('i1'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| ----------------------------------------------------+-----------------------------------------+--------+-------+------------------------------------+---------+----------+-------------+-------- |
| i1 | | f | 0 | part_table3 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_date_amount_idx | i1 | f | 1 | part_table3_1_prt_part1 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_date_amount_idx | i1 | f | 1 | part_table3_1_prt_part2 | | 2 | 2 | 1 4 |
| part_table3_1_prt_part3_date_amount_idx | i1 | f | 1 | part_table3_1_prt_part3 | | 2 | 2 | 1 3 |
| part_table3_1_prt_part1_2_prt_asia_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_asia | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_2_prt_usa_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_usa | | 2 | 2 | 1 4 |
| part_table3_1_prt_part1_2_prt_def_date_amount_idx | part_table3_1_prt_part1_date_amount_idx | t | 2 | part_table3_1_prt_part1_2_prt_def | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_2_prt_asia_date_amount_idx | part_table3_1_prt_part2_date_amount_idx | t | 2 | part_table3_1_prt_part2_2_prt_asia | | 2 | 2 | 1 4 |
| part_table3_1_prt_part2_2_prt_usa_date_amount_idx | part_table3_1_prt_part2_date_amount_idx | t | 2 | part_table3_1_prt_part2_2_prt_usa | | 2 | 2 | 1 4 |
| part_table3_1_prt_part3_2_prt_usa_date_amount_idx | part_table3_1_prt_part3_date_amount_idx | t | 2 | part_table3_1_prt_part3_2_prt_usa | | 2 | 2 | 1 3 |
| (10 rows) |
| |
| -- dropped cols in the ind expr |
| -- index expr varno are different |
| create index i41 on part_table3_1_prt_part3_2_prt_usa(ceil(amount)); |
| create index i42 on part_table3_1_prt_part2_2_prt_usa(ceil(amount)); |
| create index i43 on part_table3_1_prt_part2_2_prt_asia(ceil(amount)); |
| select indrelid::regclass as tablename, indnatts, indnkeyatts, indkey, pg_get_expr(indexprs, indrelid) from pg_index where indexrelid = 'i41'::regclass; |
| tablename | indnatts | indnkeyatts | indkey | pg_get_expr |
| -----------------------------------+----------+-------------+--------+-------------- |
| part_table3_1_prt_part3_2_prt_usa | 1 | 1 | 0 | ceil(amount) |
| (1 row) |
| |
| select indrelid::regclass as tablename, indnatts, indnkeyatts, indkey, pg_get_expr(indexprs, indrelid) from pg_index where indexrelid = 'i42'::regclass; |
| tablename | indnatts | indnkeyatts | indkey | pg_get_expr |
| -----------------------------------+----------+-------------+--------+-------------- |
| part_table3_1_prt_part2_2_prt_usa | 1 | 1 | 0 | ceil(amount) |
| (1 row) |
| |
| select indrelid::regclass as tablename, indnatts, indnkeyatts, indkey, pg_get_expr(indexprs, indrelid) from pg_index where indexrelid = 'i43'::regclass; |
| tablename | indnatts | indnkeyatts | indkey | pg_get_expr |
| ------------------------------------+----------+-------------+--------+-------------- |
| part_table3_1_prt_part2_2_prt_asia | 1 | 1 | 0 | ceil(amount) |
| (1 row) |
| |
| -- ************************************************************ |
| -- * Scenario 8 |
| -- * - open-ended partitions |
| -- ************************************************************ |
| -- AO partitioned tables: Orca treats them as bitmap indexes |
| create table part_table11(a int, b int, c int) with (appendonly=true) partition by list(b) (partition p1 values(1), partition p2 values(2)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create index part_table11_idx on part_table11(c); |
| select * from index_info('part_table11_idx'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| -----------------------------+------------------+--------+-------+-----------------------+---------+----------+-------------+-------- |
| part_table11_idx | | f | 0 | part_table11 | | 1 | 1 | 3 |
| part_table11_1_prt_p1_c_idx | part_table11_idx | t | 1 | part_table11_1_prt_p1 | | 1 | 1 | 3 |
| part_table11_1_prt_p2_c_idx | part_table11_idx | t | 1 | part_table11_1_prt_p2 | | 1 | 1 | 3 |
| (3 rows) |
| |
| -- mixed heap, AO and AOCO tables: Orca treats them as bitmap indexes |
| create table part_table12(a int, b int, c int) partition by list(b) |
| (partition p1 values(1), |
| partition p2 values(2) with (appendonly=true, orientation=column), |
| partition p3 values(3) with (appendonly=true), |
| partition p4 values(4) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create index part_table12_idx on part_table12(c); |
| select * from index_info('part_table12_idx'::regclass); |
| indexname | parentindex | isleaf | level | tablename | indpred | indnatts | indnkeyatts | indkey |
| -----------------------------+------------------+--------+-------+-----------------------+---------+----------+-------------+-------- |
| part_table12_idx | | f | 0 | part_table12 | | 1 | 1 | 3 |
| part_table12_1_prt_p1_c_idx | part_table12_idx | t | 1 | part_table12_1_prt_p1 | | 1 | 1 | 3 |
| part_table12_1_prt_p2_c_idx | part_table12_idx | t | 1 | part_table12_1_prt_p2 | | 1 | 1 | 3 |
| part_table12_1_prt_p3_c_idx | part_table12_idx | t | 1 | part_table12_1_prt_p3 | | 1 | 1 | 3 |
| part_table12_1_prt_p4_c_idx | part_table12_idx | t | 1 | part_table12_1_prt_p4 | | 1 | 1 | 3 |
| (5 rows) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 10 |
| -- * - multi-levels partition exchange with external table |
| -- * |
| -- ************************************************************ |
| -- |
| create table exchange_sub_partition (a int,b int) |
| distributed by (a) |
| partition by range (a) |
| subpartition by range (b) |
| subpartition template |
| (start(1) end(3) every(1)) |
| (start(1) end(3) every(1)); |
| create external web table exchange_external_table(like exchange_sub_partition_1_prt_1_2_prt_1) |
| execute 'printf "2,2\n1,1\n"' on host |
| format 'csv'; |
| alter table exchange_sub_partition |
| alter partition for (integer '2') |
| exchange partition for (integer '2') with table exchange_external_table; |
| NOTICE: partition constraints are not validated when attaching a readable external table |
| select * from exchange_sub_partition; |
| a | b |
| ---+--- |
| 2 | 2 |
| (1 row) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 11 |
| -- * - Test unnamed index creation on all partitions for a |
| -- * single level partitioned table. |
| -- * - no default parts, no dropped columns |
| -- ************************************************************ |
| -- |
| create table unnamed_index_part_table |
| ( |
| part_key int, |
| index_key int |
| ) |
| partition by range(part_key) |
| ( |
| partition part1 end (3), |
| partition part2 start (3) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'part_key' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- physical unnamed index on all parts |
| -- output shows indexes created on all parts |
| create index on unnamed_index_part_table(index_key); |
| SELECT n.nspname as "Schema", c.relname as "Name", c.relkind, c2.relname as "Table" |
| FROM pg_catalog.pg_class c |
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
| LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid |
| LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid |
| WHERE c.relkind IN ('i', 'I') AND c2.relname like 'unnamed_index_part_table%'; |
| Schema | Name | relkind | Table |
| --------+----------------------------------------------------+---------+-------------------------------------- |
| public | unnamed_index_part_table_index_key_idx | I | unnamed_index_part_table |
| public | unnamed_index_part_table_1_prt_part1_index_key_idx | i | unnamed_index_part_table_1_prt_part1 |
| public | unnamed_index_part_table_1_prt_part2_index_key_idx | i | unnamed_index_part_table_1_prt_part2 |
| (3 rows) |
| |
| -- |
| -- ************************************************************ |
| -- * Scenario 12 |
| -- * - unnamed indexes on multi-level partitions |
| -- ************************************************************ |
| -- |
| create table unnamed_index_multi_part_table |
| ( |
| date date, |
| region text, |
| region1 text, |
| amount decimal(10,2) |
| ) |
| partition by range(date) |
| subpartition by list(region) |
| ( |
| partition part1 start(date '2008-01-01') end(date '2009-01-01') |
| ( |
| subpartition usa values ('usa'), |
| subpartition asia values ('asia'), |
| default subpartition def |
| ), |
| partition part2 start(date '2009-01-01') end(date '2010-01-01') |
| ( |
| subpartition usa values ('usa'), |
| subpartition asia values ('asia') |
| ) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'date' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- create unnamed indexe (will be created on all parts/subparts) |
| create index on unnamed_index_multi_part_table(date, amount); |
| SELECT n.nspname as "Schema", c.relname as "Name", c.relkind, c2.relname as "Table" |
| FROM pg_catalog.pg_class c |
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
| LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid |
| LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid |
| WHERE c.relkind IN ('i','I') AND c2.relname like 'unnamed_index_multi_part_table%'; |
| Schema | Name | relkind | Table |
| --------+-----------------------------------------------------------------+---------+------------------------------------------------------- |
| public | unnamed_index_multi_part_table_date_amount_idx | I | unnamed_index_multi_part_table |
| public | unnamed_index_multi_part_table_1_prt_part1_2_pr_date_amount_idx | i | unnamed_index_multi_part_table_1_prt_part1_2_prt_asia |
| public | unnamed_index_multi_part_table_1_prt_part1_date_amount_idx | I | unnamed_index_multi_part_table_1_prt_part1 |
| public | unnamed_index_multi_part_table_1_prt_part1_2_p_date_amount_idx1 | i | unnamed_index_multi_part_table_1_prt_part1_2_prt_usa |
| public | unnamed_index_multi_part_table_1_prt_part1_2_p_date_amount_idx2 | i | unnamed_index_multi_part_table_1_prt_part1_2_prt_def |
| public | unnamed_index_multi_part_table_1_prt_part2_2_pr_date_amount_idx | i | unnamed_index_multi_part_table_1_prt_part2_2_prt_asia |
| public | unnamed_index_multi_part_table_1_prt_part2_date_amount_idx | I | unnamed_index_multi_part_table_1_prt_part2 |
| public | unnamed_index_multi_part_table_1_prt_part2_2_p_date_amount_idx1 | i | unnamed_index_multi_part_table_1_prt_part2_2_prt_usa |
| (8 rows) |
| |
| drop table exchange_tab; |
| drop table part_table1; |
| drop table part_table2; |
| drop table part_table3; |
| drop table part_table11; |
| drop table part_table12; |
| drop table exchange_sub_partition; |
| drop table exchange_external_table; |
