Re: partition tree inspection functions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: partition tree inspection functions
Date: 2018-06-26 07:37:28
Message-ID: 5a560c9c-3722-1914-f34b-1de0ff3310d1@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Lætitia Avrot 2018-06-26 07:49:41 Re: Constraint documentation
Previous Message Kyotaro HORIGUCHI 2018-06-26 07:26:59 Re: [HACKERS] Restricting maximum keep segments by repslots