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
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 |