[大数据]PostgreSQL里实用sql

1、统计一个库里面含有某个类型的的全部表信息

select c.relname, ATTNAME, ATTTYPID, TYPNAME from sys_attribute a, sys_class c, sys_namespace n, sys_type t where a.attrelid = c.oid and a.attnum > （长度，根据实际指定） and c.relnamespace = n.oid and nspname = 'public' and a.atttypid = t.oid and typname = '需要统计的类型' and c.relkind = 'r';

2、批量统计表数量
SELECT 'select count(*) from '||table_name||' union all' FROM information_schema.tables WHERE table_schema='cma_am' order by table_name asc;

3、统计重复索引
SELECT relname,(array_agg(idx))[1] idx1,pg_get_indexdef((array_agg(idx))[1]) idx1_def,(array_agg(idx))[2] idx2,pg_get_indexdef((array_agg(idx))[2]) idx2_def,(array_agg(idx))[3] idx3,pg_get_indexdef((array_agg(idx))[3]) idx3_def FROM (SELECT indrelid::regclass AS relname,indexrelid::regclass AS idx,(indrelid::text || indclass::text || indkey::text || COALESCE(indexprs::text, '') || COALESCE(indpred::text, '')) AS KEY FROM pg_index) sub GROUP BY relname, KEY HAVING count(*) > 1;

4、查询单个索引占据空间大小

select indexrelname,

pg_size_pretty(pg_relation_size(indexrelid))

from pg_stat_user_indexes

where schemaname='public'? order by pg_relation_size(indexrelid) desc;

5、查询某个表上所有索引大小总和

select pg_size_pretty(pg_indexes_size('索引所在的表名'));

6、查看某个模式下表的数量

SELECT count(tablename) FROM sys_tables WHERE tablename NOT LIKE 'sys%' AND tablename NOT LIKE 'sql_%';

7、查看特定表中索引数量

select count(1) from user_indexes t where t.table_name like 'DT%' or t.table_name like 'BUDGET%';

8、查看本地连接数：
ksql中执行语句select count(*) from sys_stat_activity where client_addr='127.0.0.1';