On 2018/06/26 14:08, Amit Langote wrote: > Hi. > > As discussed a little while back [1] and also recently mentioned [2], here > is a patch that adds a set of functions to inspect the details of a > partition tree. There are three functions: > > pg_partition_parent(regclass) returns regclass > pg_partition_root_parent(regclass) returns regclass > pg_partition_tree_tables(regclass) returns setof regclass > > Here is an example showing how one may want to use them. > > create table p (a int, b int) partition by range (a); > create table p0 partition of p for values from (minvalue) to (0) partition > by hash (b); > create table p00 partition of p0 for values with (modulus 2, remainder 0); > create table p01 partition of p0 for values with (modulus 2, remainder 1); > create table p1 partition of p for values from (0) to (maxvalue) partition > by hash (b); > create table p10 partition of p1 for values with (modulus 2, remainder 0); > create table p11 partition of p1 for values with (modulus 2, remainder 1); > insert into p select i, i from generate_series(-5, 5) i; > > select pg_partition_parent('p0') as parent; > parent > -------- > p > (1 row) > > Time: 1.469 ms > select pg_partition_parent('p01') as parent; > parent > -------- > p0 > (1 row) > > Time: 1.330 ms > select pg_partition_root_parent('p01') as root_parent; > root_parent > ------------- > p > (1 row) > > select p as relname, > pg_partition_parent(p) as parent, > pg_partition_root_parent(p) as root_parent > from pg_partition_tree_tables('p') p; > relname | parent | root_parent > ---------+--------+------------- > p | | p > p0 | p | p > p1 | p | p > p00 | p0 | p > p01 | p0 | p > p10 | p1 | p > p11 | p1 | p > (7 rows) > > select p as relname, > pg_partition_parent(p) as parent, > pg_partition_root_parent(p) as root_parent, > pg_relation_size(p) as size > from pg_partition_tree_tables('p') p; > relname | parent | root_parent | size > ---------+--------+-------------+------ > p | | p | 0 > p0 | p | p | 0 > p1 | p | p | 0 > p00 | p0 | p | 8192 > p01 | p0 | p | 8192 > p10 | p1 | p | 8192 > p11 | p1 | p | 8192 > (7 rows) > > > select sum(pg_relation_size(p)) as total_size > from pg_partition_tree_tables('p') p; > total_size > ------------- > 32768 > (1 row) > > Feedback is welcome!
Added this to July CF. Thanks, Amit