kingbase常用SQL总结之统计大小

概述

数据库运维中，我们需要总结一些常用的SQL语句，无论是日常巡检、故障排查或是死锁分析，都可以随时拿来用，提升工作效率，下面是一些常见的经典SQL或者是笔者自己工作过程中用到的常用的SQL,整理记录以备不时之需。
小建议：还可以将不同的SQL进行分类整理，通过一个shell脚本去调用不同的SQL脚本，拿到想要的信息和数据。这是笔者在某基金项目想他们DBA学到的很好用。

kingbase基于pg那个版本的SQL语句

test=# select name,setting from sys_settings where name='server_version';
-[ RECORD 1 ]-----------
name    | server_version
setting | 12.1

获取统计信息常用SQL

查询表大小SQL

函数解析
pg_relation_size ( relation regclass [, fork text ] ) → bigint,只查询表数据大小
   Computes the disk space used by one “fork” of the specified relation
pg_table_size ( regclass ) → bigint，统计表大小+空闲映射空间文件大小+可见性文件大小
  Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).
pg_total_relation_size ( regclass ) → bigint: pg_table_size +和表相关的索引大小
  Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.
  
test=# select pg_size_pretty(pg_total_relation_size(oid::regclass)) from pg_class where relname='fb_example';
 pg_size_pretty
----------------
 56 kB

test=# select pg_size_pretty(pg_relation_size(oid::regclass)) from pg_class where relname='fb_example'; --常用SQL
 pg_size_pretty
----------------
 8192 bytes

test=# select pg_size_pretty(pg_table_size(oid::regclass)) from pg_class where relname='fb_example';
 pg_size_pretty
----------------
 40 kB

查询模式大小SQL

了解了pg_relation_size、pg_table_size和pg_total_relation_size 就可以计算模式大小了。

SELECT 
    pg_size_pretty(
        sum(pg_relation_size(C.oid))
    ) as schema_size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname ='your schema name';

查询库大小SQL

select pg_size_pretty(pg_database_size(oid::regclass)) from pg_database;

查询top10表大小

    SELECT 
    nspname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname = 'your schema name'  and c.relkind='r' -- 替换为你要查询的模式名称
ORDER BY 
    pg_relation_size(c.oid::regclass) DESC
LIMIT 10;

查询top10索引大小

    SELECT 
    nspname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname = 'your schema name' and c.relkind='i' -- 替换为你要查询的模式名称
ORDER BY 
    pg_relation_size(c.oid::regclass) DESC
LIMIT 10;

如有不正确之处请评论指出，感谢感谢。

我是东山