1. 查看当前数据库大小以及记录行数
select trim(pgdb.datname) as database, sum(b.mbytes) as mbytes, sum(a.rows) as rows from (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name) as a join pg_class as pgc on pgc.oid = a.id join pg_namespace as pgn on pgn.oid = pgc.relnamespace join pg_database as pgdb on pgdb.oid = a.db_id join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl group by pgdb.datname order by 1; database | mbytes | rows -----------+---------+------------ analytics | 1074998 | 5030398009 (1 row)
2. 查看当前数据库各schema大小以及每个schema下的记录行数
select trim(pgdb.datname) as database, trim(pgn.nspname) as schema, sum(b.mbytes) as mbytes, sum(a.rows) as rows from (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name) as a join pg_class as pgc on pgc.oid = a.id join pg_namespace as pgn on pgn.oid = pgc.relnamespace join pg_database as pgdb on pgdb.oid = a.db_id join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl group by pgdb.datname, pgn.nspname order by 1, 2; database | schema | mbytes | rows -----------+-------------+--------+------------ analytics | datascience | 168 | 196128 analytics | dba | 15852 | 43752350 analytics | dimensions | 28223 | 225275059 analytics | facts | 265457 | 1382762113 analytics | public | 50235 | 104688442 analytics | search_data | 696799 | 3235794562 analytics | staging | 18264 | 37929355 (7 rows)
3. 查看当前数据库下每张表的大小
方法一
SELECT TRIM(pgdb.datname) AS Database, TRIM(a.name) AS Table, ((b.mbytes/part.total::decimal)*100)::decimal(5,2) AS pct_of_total, b.mbytes, b.unsorted_mbytes FROM stv_tbl_perm a JOIN pg_database AS pgdb ON pgdb.oid = a.db_id JOIN ( SELECT tbl, SUM( DECODE(unsorted, 1, 1, 0)) AS unsorted_mbytes, COUNT(*) AS mbytes FROM stv_blocklist GROUP BY tbl ) AS b ON a.id = b.tbl JOIN ( SELECT SUM(capacity) AS total FROM stv_partitions WHERE part_begin = 0 ) AS part ON 1 = 1 WHERE a.slice = 0 ORDER BY 4 desc, db_id, name; database | table | pct_of_total | mbytes | unsorted_mbytes -----------+-----------------------------------------------+--------------+--------+----------------- analytics | es_entitysvc_response_logshed | 39.42 | 450948 | 449820 analytics | es_entitysvc_logshed | 18.06 | 206630 | 206054 analytics | auto_events | 9.99 | 114379 | 113395 analytics | auto_events_realtime | 4.11 | 47029 | 47020 analytics | auto_events_rt | 2.20 | 25251 | 25242 analytics | entity | 1.87 | 21485 | 16553 analytics | unified_events_dev | 1.27 | 14604 | 14592 analytics | logshedevents_processed | 0.65 | 7504 | 7504 analytics | client_events_stg | 0.60 | 6912 | 6912 analytics | search_autocomplete_response_processed | 0.58 | 6672 | 6660 analytics | entity_gen3 | 0.51 | 5940 | 5796 analytics | staging_auto_events_stg | 0.47 | 5436 | 5436 analytics | es_denaliusage_logshed | 0.45 | 5224 | 5212 analytics | scout4cars_events | 0.38 | 4430 | 4430 analytics | search_autocomplete_request_processed | 0.35 | 4080 | 4068 analytics | osm_metrics | 0.32 | 3718 | 3708 analytics | gm_auto_events | 0.32 | 3715 | 1970 analytics | client_events_raj | 0.32 | 3707 | 1584 analytics | scout_events_tmp | 0.29 | 3384 | 1716 analytics | client_events_sessionmap_stg_loadtest | 0.28 | 3288 | 3288 analytics | unified_events_for_scout_dev | 0.27 | 3192 | 3180 analytics | client_events_vlad | 0.27 | 3144 | 1572 analytics | client_events_backup_till_1010 | 0.27 | 3120 | 1584 (25 rows)
方法二
select trim(pgdb.datname) as database, trim(pgn.nspname) as schema, trim(a.name) as Table, b.mbytes, a.rows from (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name) as a join pg_class as pgc on pgc.oid = a.id join pg_namespace as pgn on pgn.oid = pgc.relnamespace join pg_database as pgdb on pgdb.oid = a.db_id join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl order by 1, 2, 4 desc; database | schema | table | mbytes | rows -----------+-------------+-----------------------------------------------+--------+------------ analytics | datascience | clusterwithstartstop | 168 | 196128 analytics | dba | staging_auto_events_stg | 5436 | 42524253 analytics | dba | client_events | 1680 | 8623 analytics | dba | client_events_hive | 1680 | 2742 analytics | dba | client_events_stg | 1656 | 3690 analytics | dba | client_events_stg_hive | 1548 | 2537 analytics | dba | facts_auto_events | 1452 | 1053537 analytics | dba | auto_events | 1200 | 79584 analytics | dba | facts_auto_events_hive | 1200 | 77384 analytics | dimensions | entity | 21485 | 178665073 analytics | dimensions | entity_gen3 | 5940 | 46499810 analytics | dimensions | date | 216 | 39444 analytics | dimensions | location | 192 | 65921 analytics | dimensions | product | 132 | 2292 analytics | dimensions | carrier | 96 | 1128 analytics | dimensions | application_info | 90 | 1248 analytics | dimensions | event_type_classification | 72 | 143 analytics | facts | auto_events | 114379 | 893071197 analytics | facts | auto_events_realtime | 47029 | 78054568 (21 rows)
方法三
select cast(use2.usename as varchar(50)) as owner, pgc.oid, trim(pgdb.datname) as Database, trim(pgn.nspname) as Schema, trim(a.name) as Table, b.mbytes, a.rows from (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name ) as a join pg_class as pgc on pgc.oid = a.id left join pg_user use2 on (pgc.relowner = use2.usesysid) join pg_namespace as pgn on pgn.oid = pgc.relnamespace and pgn.nspowner > 1 join pg_database as pgdb on pgdb.oid = a.db_id join (select tbl, count(*) as mbytes from stv_blocklist group by tbl ) b on a.id = b.tbl order by mbytes desc, a.db_id, a.name; owner | oid | database | schema | table | mbytes | rows ------------------------+---------+-----------+-------------+-----------------------------------------------+--------+------------ search_data_writer | 780702 | analytics | search_data | es_entitysvc_response_logshed | 450948 | 1983660186 search_data_writer | 780704 | analytics | search_data | es_entitysvc_logshed | 206630 | 870298752 tnadmin | 868711 | analytics | facts | auto_events | 114379 | 893071197 client_events_etl_user | 680119 | analytics | facts | auto_events_realtime | 47029 | 78054568 tnadmin | 868715 | analytics | facts | auto_events_rt | 25251 | 184784513 sheena | 119412 | analytics | dimensions | entity | 21485 | 178665073 client_events_etl_user | 1080972 | analytics | facts | unified_events_dev | 14604 | 104578129 search_data_writer | 225115 | analytics | search_data | logshedevents_processed | 7504 | 112599927 tnadmin | 148013 | analytics | staging | client_events_stg | 6912 | 9145782 search_data_writer | 218921 | analytics | search_data | search_autocomplete_response_processed | 6672 | 116412380 tnadmin | 950671 | analytics | dimensions | entity_gen3 | 5940 | 46499810 tnadmin | 252547 | analytics | dba | staging_auto_events_stg | 5436 | 42524253 search_data_writer | 958865 | analytics | search_data | es_denaliusage_logshed | 5224 | 11127230 tnadmin | 754088 | analytics | facts | scout4cars_events | 4430 | 17981548 search_data_writer | 218919 | analytics | search_data | search_autocomplete_request_processed | 4080 | 42130637 matthieu | 157597 | analytics | facts | osm_metrics | 3718 | 33749875 tnadmin | 689962 | analytics | facts | gm_auto_events | 3715 | 12066340 tnadmin | 158221 | analytics | facts | client_events_raj | 3707 | 1362676 krishna | 138765 | analytics | staging | client_events_sessionmap_stg_loadtest | 3288 | 18799978 client_events_etl_user | 1070400 | analytics | facts | unified_events_for_scout_dev | 3192 | 2961636 tnadmin | 128436 | analytics | facts | client_events_backup_till_1010 | 3120 | 7046 tnadmin | 147602 | analytics | facts | client_events_logshed_temp | 3120 | 44265 (24 rows)