SQL to monitor partition information in Greenplum
-- List of partition tables in all schema in your db.
select distinct tablename from pg_partitions;
select distinct schemaname,tablename from pg_partitions;
select distinct tablename,count(partitiontablename) as total_no_of_partitions from pg_partitions group by tablename;
- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.
SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
CASE
WHEN p.tolpart IS NULL THEN 0
ELSE p.tolpart
END AS "Total Parition",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpartitions"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p
ON p.schemaname=m.schemaname AND p.tablename=m.tablename
LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
ORDER BY 1;
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;
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;
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;
SELECT pso.classname, pso.objname, pso.objid, pso.schemaname, pso.usestatus, pso.usename, pso.actionname, pso.subtype, pso.statime,
CASE
WHEN pr.parlevel IS NOT NULL THEN pr.parlevel
ELSE pr2.parlevel
END AS partitionlevel, pcns.relname AS parenttablename, pcns.nspname AS parentschemaname, pr.parrelid AS parent_relid
FROM pg_stat_operations pso
LEFT JOIN pg_partition_rule ppr ON pso.objid = ppr.parchildrelid
LEFT JOIN pg_partition pr ON pr.oid = ppr.paroid
LEFT JOIN ( SELECT min(prx.parlevel) AS parlevel, prx.parrelid
FROM pg_partition prx
GROUP BY prx.parrelid) pr2 ON pr2.parrelid = pso.objid
LEFT JOIN ( SELECT pc.oid, pc.relname, pc.relnamespace, pc.reltype, pc.relowner, pc.relam, pc.relfilenode, pc.reltablespace, pc.relpages, pc.reltuples, pc.reltoastrelid, pc.reltoastidxid, pc.relaosegrelid, pc.relaosegidxid, pc.relhasindex, pc.relisshared, pc.relkind, pc.relstorage, pc.relnatts, pc.relchecks, pc.reltriggers, pc.relukeys, pc.relfkeys, pc.relrefs, pc.relhasoids, pc.relhaspkey, pc.relhasrules, pc.relhassubclass, pc.relfrozenxid, pc.relacl, pc.reloptions, ns.nspname, ns.nspowner, ns.nspacl
FROM pg_class pc
FULL JOIN pg_namespace ns ON ns.oid = pc.relnamespace) pcns ON pcns.oid = pr.parrelid;
-- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.
SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
CASE
WHEN p.tolpart IS NULL THEN 0
ELSE p.tolpart
END AS "Total Parition",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpartitions"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p
ON p.schemaname=m.schemaname AND p.tablename=m.tablename
LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
ORDER BY 1;
Tables without sub-partitions
If the table you want to query does not have subpartitions, perform the following queries:
NOTES:Ensure that you replace the <schemaname> and <tablename> with the schema and table names you want to query.
The queries below assume that you are interested in partition information for a specific table.
-- List all partition information for the table specified and provide its structural information.
SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlevel "Level",
p.partitionrank "Rank",
p.partitionposition "Position",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
-- If the partition is of the type "Range", use the query below to access its partition criteria.
SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionrangestart "Start Range",
CASE
WHEN p.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
p.partitionrangeend "End Range",
CASE
WHEN p.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p, pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
-- If the partition is of the type "List", use the query below to access its partition criteria.
SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlistvalues "List Values",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
Tables with sub-partitions
If the table you want to query does have subpartitions, use the queries below.
NOTES:Ensure that you replace the <schemaname> and <tablename> with the schema and table names you want to query.
For queries that request for <schemaname> and <partition tablename>, provide the schema name and partition name of the parent partition that holds the sub-partition.
The queries below assume that you are interested in partition information and subpartitions for a specific table.
-- List the partitions in the table, their partition criteria and the total counts of sub-partitions.
SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
m.partitionschemaname||'.'||m.partitiontablename "Partition Table",
m.partitiontype "Type",
c.columnname "Column",
m.partitionrangestart "Start Range",
CASE
WHEN m.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
m.partitionrangeend "End Range",
CASE
WHEN m.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
m.partitionlistvalues "List Values",
CASE
WHEN m.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpart's"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,parentpartitiontablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename,parentpartitiontablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
AND s.parentpartitiontablename=m.partitiontablename
LEFT JOIN pg_partition_columns c
ON c.schemaname=m.schemaname
AND c.tablename=m.tablename
AND c.partitionlevel=m.partitionlevel
WHERE m.parentpartitiontablename is NULL
AND (m.schemaname,m.tablename)=('<schemaname>','<tablename>')
ORDER BY 5;
-- Subpartition parent partitions structure information
SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlevel "Level",
p.partitionrank "Rank",
p.partitionposition "Position",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE p.parentpartitiontablename is NULL
AND c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
-- Subpartition structure information
SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionlevel "Level",
s.partitionrank "Rank",
s.partitionposition "Position",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionrank,
partitionposition,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 8;
-- If subpartition is of the type "Range", use the query below to access its partition criteria.
SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionrangestart "Start Range",
CASE
WHEN s.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
s.partitionrangeend "End Range",
CASE
WHEN s.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionrangestart,
partitionstartinclusive,
partitionrangeend,
partitionendinclusive,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 3;
-- If subpartition is of the type "List", use the query below to access its partition criteria
SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionlistvalues "List Values",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionlistvalues,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 3;