合 PG查询数据库中前几张大表
👉 本文共约10个字，系统预计阅读时间或需1分钟。
目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | SELECT t.table_catalog as db, n.nspname AS schemaname, c.relname, c.reltuples::numeric as rowcount, pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS indexes_size, pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS total_size --,pg_relation_filepath(table_name) filepath FROM pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" ) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND relkind in ('r','p') ORDER BY reltuples DESC LIMIT 20; sbtest=# SELECT sbtest-# t.table_catalog as db, sbtest-# n.nspname AS schemaname, sbtest-# c.relname, sbtest-# c.reltuples::numeric as rowcount, sbtest-# pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, sbtest-# pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS indexes_size, sbtest-# pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS total_size --,pg_relation_filepath(table_name) filepath sbtest-# FROM pg_class C sbtest-# LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) sbtest-# left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" ) sbtest-# WHERE sbtest-# nspname NOT IN ( 'pg_catalog', 'information_schema' ) sbtest-# AND relkind in ('r','p') sbtest-# ORDER BY sbtest-# reltuples DESC sbtest-# LIMIT 20; db | schemaname | relname | rowcount | table_size | indexes_size | total_size --------+------------+----------+----------+------------+--------------+------------ sbtest | public | sbtest7 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest1 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | t1 | 100000 | 21 MB | 0 bytes | 21 MB sbtest | public | sbtest3 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest8 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest4 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest9 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest6 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest10 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | sbtest5 | 100000 | 21 MB | 4432 kB | 25 MB sbtest | public | t2 | 100000 | 21 MB | 0 bytes | 21 MB sbtest | public | t3 | 100000 | 21 MB | 0 bytes | 21 MB sbtest | public | sbtest2 | 100000 | 21 MB | 4432 kB | 25 MB (13 rows) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
