--查出所有表(包含索引)并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20
查正在运行的sql:
SELECT now() as now,pid,query_start,now()-query_start as running_time,query FROM pg_stat_activity WHERE datname = 'boss'
AND STATE != 'idle' AND CURRENT_TIMESTAMP :: TIMESTAMP + INTERVAL '-10 seconds' >= query_start
ORDER BY running_time desc LIMIT 10
查看表和索引大小:
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
查pid慢的sql
SELECT
procpid,
START,
now() - START AS lap,
current_query
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,
pg_stat_get_backend_activity (S.backendid) AS current_query
FROM
(
SELECT
pg_stat_get_backend_idset () AS backendid
) AS S
) AS S
WHERE procpid=30200
查pgsql正在执行的sql 按照运行时间排序
SELECT
procpid,
START,
now() - START AS lap,
current_query
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,
pg_stat_get_backend_activity (S.backendid) AS current_query
FROM
(
SELECT
pg_stat_get_backend_idset () AS backendid
) AS S
) AS S
WHERE
current_query <> '<IDLE>'
ORDER BY
lap DESC;
查正在运行的sql:
select pid,datname,query,query_start from pg_stat_activity where state != 'idle' order by query_start asc;
1、查询表名和表注释
select relname as tabname,
cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relname ='table_name' ;
2、查询字段名、字段类型及字段长度和字段注释
select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d
where c.relname = 'table_name' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
查询某个字段属于哪个表
SELECT *
FROM information_schema. COLUMNS
WHERE COLUMN_NAME = 'storename';
SELECT c.relname AS "表名",a.attname AS "字段名",c.oid
FROM pg_class c
LEFT JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname NOT LIKE 'pg_%'
-- AND c.relname = 'kx_store'
AND a.attname = 'storename';