当前位置 : 首页 » 文章分类 :  开发  »  PostgreSQL-常用系统表及SQL

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

参考


上一篇 Apache-Tomcat

下一篇 Java-JPDA

阅读
392
阅读预计2分钟
创建日期 2017-01-10
修改日期 2018-06-22
类别
百度推荐