Partition Information

Post date: Oct 18, 2014 12:34:41 AM

1. List of partition tables in all schema in your db.

sachi=# select distinct tablename from pg_partitions;

tablename

-----------

cms_part

cms_qlz

cms_zlib

cms_zlib9

sales

(5 rows)

sachi=# select distinct schemaname,tablename from pg_partitions;

schemaname | tablename

------------+-----------

public | cms_part

public | cms_qlz

public | cms_zlib

public | cms_zlib9

public | sales

(5 rows)

sachi=#

sachi=# select distinct tablename,count(partitiontablename) from pg_partitions group by tablename;

tablename | count

-----------+-------

cms_part | 17

cms_qlz | 17

cms_zlib | 17

cms_zlib9 | 17

sales | 132

(5 rows)

sachi=# SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname AS columnname, p.parlevel AS partitionlevel, p.i + 1 AS position_in_partition_key

FROM pg_namespace n, pg_class c, pg_attribute a, ( SELECT p.parrelid, p.parlevel, p.paratts[i.i] AS attnum, i.i

FROM pg_partition p, generate_series(0, ( SELECT max(array_upper(pg_partition.paratts, 1)) AS max

FROM pg_partition)) i(i)

WHERE p.paratts[i.i] IS NOT NULL) p

WHERE p.parrelid = c.oid AND c.relnamespace = n.oid AND p.attnum = a.attnum AND a.attrelid = c.oid;

schemaname | tablename | columnname | partitionlevel | position_in_partition_key

------------+-----------+---------------------------+----------------+---------------------------

public | cms_part | car_line_cms_type_srvc_cd | 0 | 1

public | cms_qlz | car_line_cms_type_srvc_cd | 0 | 1

public | cms_zlib | car_line_cms_type_srvc_cd | 0 | 1

public | cms_zlib9 | car_line_cms_type_srvc_cd | 0 | 1

public | sales | year | 0 | 1

public | sales | month | 1 | 1

public | sales | region | 2 | 1

(9 rows)

sachi=# SELECT p1.schemaname, p1.tablename, p1.partitionname, p1.partitiontype, p1.partitionlevel,

CASE

WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint

WHEN p1.partitionnodefault > 0 THEN p1.partitionrank

WHEN p1.partitionrank = 1 THEN NULL::bigint

ELSE p1.partitionrank - 1

END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart,

CASE

WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive

ELSE NULL::boolean

END AS partitionstartinclusive, p1.partitionrangeend,

CASE

WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive

ELSE NULL::boolean

END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary

FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, pr1.parname AS partitionname, p.parlevel AS partitionlevel, pr1.parruleord AS partitionposition, rank() OVER(

PARTITION BY p.oid, cl.relname, p.parlevel

ORDER BY pr1.parruleord) AS partitionrank, pg_get_expr(pr1.parlistvalues, p.parrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, p.parrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, p.parrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, p.parrelid) AS partitioneveryclause, min(pr1.parruleord) OVER(

PARTITION BY p.oid, cl.relname, p.parlevel

ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault,

CASE

WHEN p.parkind = 'h'::"char" THEN 'hash'::text

WHEN p.parkind = 'r'::"char" THEN 'range'::text

WHEN p.parkind = 'l'::"char" THEN 'list'::text

ELSE NULL::text

END AS partitiontype, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary

FROM pg_namespace n, pg_class cl, pg_partition p, pg_partition_rule pr1

WHERE p.parrelid = cl.oid AND pr1.paroid = p.oid AND cl.relnamespace = n.oid AND p.paristemplate = true) p1;

------------+-----------+---------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+---

----------------------+-------------------+-----------------------+----------------------+--------------------+--------------------------------------

public | sales | other_months | range | 1 | 1 | 1 | | | f

| | f | | t | DEFAULT SUBPARTITION other_months

public | sales | | range | 1 | 2 | 2 | | 1 | t

| 2 | f | 1 | f | START (1) END (2) EVERY (1)

public | sales | | range | 1 | 3 | 3 | | 2 | t

| 3 | f | 1 | f | START (2) END (3) EVERY (1)

public | sales | | range | 1 | 4 | 4 | | 3 | t

| 4 | f | 1 | f | START (3) END (4) EVERY (1)

public | sales | | range | 1 | 5 | 5 | | 4 | t

| 5 | f | 1 | f | START (4) END (5) EVERY (1)

public | sales | | range | 1 | 6 | 6 | | 5 | t

| 6 | f | 1 | f | START (5) END (6) EVERY (1)

public | sales | | range | 1 | 7 | 7 | | 6 | t

| 7 | f | 1 | f | START (6) END (7) EVERY (1)

public | sales | | range | 1 | 8 | 8 | | 7 | t

| 8 | f | 1 | f | START (7) END (8) EVERY (1)

public | sales | | range | 1 | 9 | 9 | | 8 | t

| 9 | f | 1 | f | START (8) END (9) EVERY (1)

public | sales | | range | 1 | 10 | 10 | | 9 | t

| 10 | f | 1 | f | START (9) END (10) EVERY (1)

public | sales | | range | 1 | 11 | 11 | | 10 | t

| 11 | f | 1 | f | START (10) END (11) EVERY (1)

public | sales | | range | 1 | 12 | 12 | | 11 | t

| 12 | f | 1 | f | START (11) END (12) EVERY (1)

public | sales | | range | 1 | 13 | 13 | | 12 | t

| 13 | f | 1 | f | START (12) END (13) EVERY (1)

public | sales | usa | list | 2 | | 1 | 'usa'::text | |

| | | | f | SUBPARTITION usa VALUES('usa')

| | | | f | SUBPARTITION europe VALUES('europe')

| | | | f | SUBPARTITION asia VALUES('asia')

| | | | t | DEFAULT SUBPARTITION other_regions

(17 rows)

sachi=# SELECT p1.schemaname, p1.tablename, p1.partitionschemaname, p1.partitiontablename, p1.partitionname, p1.parentpartitiontablename, p1.parentpartitionname, p1.partitiontype, p1.partitionlevel, CASE

WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint

WHEN p1.partitionnodefault > 0 THEN p1.partitionrank

WHEN p1.partitionrank = 0 THEN NULL::bigint

ELSE p1.partitionrank

END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart,

CASE

WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive

ELSE NULL::boolean

END AS partitionstartinclusive, p1.partitionrangeend,

CASE

WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive

ELSE NULL::boolean

END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary, p1.parentspace AS parenttablespace, p1.partspace AS partitiontablespace

FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, n2.nspname AS partitionschemaname, cl2.relname AS partitiontablename, pr1.parname AS partitionname, cl3.relname AS parentpartitiontablename, pr2.parname AS parentpartitionname,

CASE

WHEN pp.parkind = 'h'::"char" THEN 'hash'::text

WHEN pp.parkind = 'r'::"char" THEN 'range'::text

WHEN pp.parkind = 'l'::"char" THEN 'list'::text

ELSE NULL::text

END AS partitiontype, pp.parlevel AS partitionlevel, pr1.parruleord AS partitionposition,

CASE

WHEN pp.parkind <> 'r'::"char" OR pr1.parisdefault THEN NULL::bigint

ELSE rank() OVER(

PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname

ORDER BY pr1.parisdefault, pr1.parruleord)

END AS partitionrank, pg_get_expr(pr1.parlistvalues, pr1.parchildrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, pr1.parchildrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, pr1.parchildrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, pr1.parchildrelid) AS partitioneveryclause, min(pr1.parruleord) OVER(

PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname

ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary, COALESCE(sp.spcname, d.dfltspcname) AS parentspace, COALESCE(sp3.spcname, d.dfltspcname) AS partspace

FROM pg_namespace n, pg_namespace n2, pg_class cl

LEFT JOIN pg_tablespace sp ON cl.reltablespace = sp.oid, pg_class cl2

LEFT JOIN pg_tablespace sp3 ON cl2.reltablespace = sp3.oid, pg_partition pp, pg_partition_rule pr1

LEFT JOIN pg_partition_rule pr2 ON pr1.parparentrule = pr2.oid

LEFT JOIN pg_class cl3 ON pr2.parchildrelid = cl3.oid, ( SELECT s.spcname

FROM pg_database, pg_tablespace s

WHERE pg_database.datname = current_database() AND pg_database.dattablespace = s.oid) d(dfltspcname)

WHERE pp.paristemplate = false AND pp.parrelid = cl.oid AND pr1.paroid = pp.oid AND cl2.oid = pr1.parchildrelid AND cl.relnamespace = n.oid AND cl2.relnamespace = n2.oid) p1;

ry | parenttablespace | partitiontablespace

------------+-----------+---------------------+-----------------------------------------------------------------+----------------+---------------------------

--------------------+---------------------+---------------+----------------+---------------+-------------------+------------------------+--------------------

-+-------------------------+-------------------+-----------------------+----------------------+--------------------+-----------------------------------------

-----------------------------+------------------+---------------------

| | range | 0 | | 1 | |

| f | | f | | t | DEFAULT PARTITION outlying_years

| pg_default | pg_default

| | range | 0 | 1 | 2 | | 2012

| t | 2013 | f | 1 | f | START (2012) END (2013) EVERY (1)

| pg_default | pg_default

| | range | 1 | | 1 | |

| f | | f | | t | DEFAULT SUBPARTITION other_months

| pg_default | pg_default

| | range | 1 | 1 | 2 | | 1

| t | 2 | f | 1 | f | START (1) END (2) EVERY (1)

| pg_default | pg_default

| | range | 1 | 2 | 3 | | 2

| t | 3 | f | 1 | f | START (2) END (3) EVERY (1)

| pg_default | pg_default

| | range | 1 | 3 | 4 | | 3

| t | 4 | f | 1 | f | START (3) END (4) EVERY (1)

| pg_default | pg_default

| | range | 1 | 4 | 5 | | 4

| t | 5 | f | 1 | f | START (4) END (5) EVERY (1)

| pg_default | pg_default

| | range | 1 | 5 | 6 | | 5

| t | 6 | f | 1 | f | START (5) END (6) EVERY (1)

| pg_default | pg_default

| | range | 1 | 6 | 7 | | 6

| t | 7 | f | 1 | f | START (6) END (7) EVERY (1)

| pg_default | pg_default

| | range | 1 | 7 | 8 | | 7

| t | 8 | f | 1 | f | START (7) END (8) EVERY (1)

| pg_default | pg_default

| | range | 1 | 8 | 9 | | 8

| t | 9 | f | 1 | f | START (8) END (9) EVERY (1)

| pg_default | pg_default

.........

......

on=column, appendonly=true) | pg_default | pg_default

| | list | 0 | | 9 | '8'::character varying |

| | | | | f | PARTITION p9 VALUES('8') WITH (orientati

on=column, appendonly=true) | pg_default | pg_default

| | list | 0 | | 10 | '9'::character varying |

| | | | | f | PARTITION p10 VALUES('9') WITH (orientat