【openGauss】openGauss查库、schema、对象大小

---

一、查库大小

-- 查库
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
			 d.datacl AS "Access privileges",
       --pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
-- where d.datname = 'database_name'
ORDER BY 1;

二、查schema大小

-- 查schema
SELECT 
  schema_name, 
  round((sum(table_size)::bigint)/1024/1024/1024,2) as "disk space/GB", 
  round((sum(table_size)/pg_database_size(current_database()))* 100,2)  as "percent" 
FROM 
  (
    SELECT 
      pg_catalog.pg_namespace.nspname as schema_name, 
      pg_relation_size(pg_catalog.pg_class.oid) as table_size 
    FROM 
      pg_catalog.pg_class 
      JOIN pg_catalog.pg_namespace 
      ON relnamespace = pg_catalog.pg_namespace.oid
  ) t 
-- where t.schema_name = 'schema_name'
GROUP BY schema_name 
ORDER BY schema_name;

三、查对象大小

要在OpenGauss中查看数据库对象的大小，可以使用以下SQL语句来获取表、索引等对象的大小信息。

3.1、查询所有表的大小：

SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS table_size FROM information_schema.tables WHERE table_type = 'BASE TABLE';

将table_name替换为需要查询的表名称。

3.2、查询指定模式（Schema）下所有表的大小：

SELECT pg_size_pretty(pg_total_relation_size('schema_name.' || table_name)) AS table_size FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'schema_name';

将schema_name替换为需要查询的模式名称。

3.3、查询特定类型的对象（如索引）的大小：

SELECT pg_size_pretty(pg_indexes_size('table_name', index_name)) AS object_size FROM pg_indexes WHERE tablename = 'table_name' AND indexname = 'index_name';

将table_name和index_name分别替换为相应的表名和索引名称。

注意：上述示例仅适用于基本的表和索引，其他复杂的对象（如视图、函数等）也可能存在不同的查询方法。