PostgreSQL-常用系统表及SQL

PostgreSQL常用系统表及SQL笔记

添加表分区

alter table rms_webinfo add partition "201801" VALUES LESS THAN ('2018-02-01 00:00:00');

pg_stat_activity视图

查看指定ip的sql语句

select client_addr,query_start,state_change,waiting,state,query
from pg_stat_activity
where client_addr in('10.8.215.15','10.8.215.16')

查看指定数据库中sql执行时间最长的几条语句

SELECT
    pid,
    datname,
    query_start,
    now() - nvl(query_start,xact_start) AS usetime,
    client_addr,
    STATE,
    waiting,
    query
FROM
    pg_stat_activity
WHERE
    datname = 'ais2'
ORDER BY
    4 DESC;

数据库对象大小

查看指定表及索引大小

SELECT
    pg_size_pretty (
        pg_relation_size ('ras_agent_psgbook')
    ) 表大小,
    pg_size_pretty (
        pg_indexes_size ('ras_agent_psgbook')
    ) 索引大小,
    COUNT (*) 行数
FROM
    ras_agent_psgbook;

查看数据库大小

SELECT
    pg_database.datname,
    pg_size_pretty (
        pg_database_size (pg_database.datname)
    ) AS SIZE
FROM pg_database;

查看指定schema下所有表大小

SELECT
    relname,
    pg_size_pretty (pg_relation_size(relid))
FROM
    pg_stat_user_tables
WHERE
    schemaname = 'nrise2'
ORDER BY
    pg_relation_size (relid) DESC;

查看指定schema下所有索引大小

SELECT
    indexrelname,
    pg_size_pretty (pg_relation_size(relid))
FROM
    pg_stat_user_indexes
WHERE
    schemaname = 'nrise2'
ORDER BY
    pg_relation_size (relid) DESC;

查看指定schema下表及索引大小

SELECT
    schemaname,relname,
pg_size_pretty (pg_relation_size(relid)),
indexrelname,
pg_size_pretty (pg_relation_size(indexrelid))

FROM
    pg_stat_user_indexes
WHERE
    schemaname = 'nrise2'
ORDER BY
pg_relation_size(relid) desc ,relname,pg_relation_size(indexrelid) desc

参考

PostgreSQL 9.6.0 手册 28.2. 统计收集器(官方文档) 表 28-3. pg_stat_activity 视图 http://www.postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
PostgreSQL 9.6.0 手册 28.2. 统计收集器表 28-12. pg_stat_all_tables视图 http://www.postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW
PostgreSQL 9.6.0 手册 28.2. 统计收集器表 28-13. pg_stat_all_indexes视图 http://www.postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW
PostgreSQL 9.6.0 手册 9.26. 系统管理函数表 9-83. 数据库对象尺寸函数 http://www.postgres.cn/docs/9.6/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE