如何最有效地获取数据库中创建的分区数?
我正在使用C++的PostgreSQL API。
如何最有效地获取数据库中创建的分区数?
我正在使用C++的PostgreSQL API。
这是如何选择表分区所有名称的方法:
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child,
child.relname AS child_schema
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
它还可以用来计数:
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
COUNT(*)
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
GROUP BY
parent_schema,
parent;
传统的分区通过继承实现。每个继承表在系统目录pg_inherits中都有一个条目。给定父表的分区数可以简单地计算:
SELECT count(*) AS partitions
FROM pg_catalog.pg_inherits
WHERE inhparent = 'schema.parent_tbl'::regclass;
有关dba.SE的相关问题:各个分区与分区表之间采用继承方式进行链接;