postgres 常用SQL

1:查找最大的表和索引

SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;

 

2:查看索引大小

SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'xxxxx' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;

 

3：查看任意表的磁盘用量

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'xxxxx';

 

4：查看正在执行的SQL

SELECT datname,procpid,query_start, current_query,waiting,client_addr FROM pg_stat_activity WHERE waiting='t';

 

5：杀掉SQL

（select only）SELECT pg_cancel_backend(PID);

（kill all kind of..）  SELECT pg_terminate_backend(PID);