postgresql表大小和数据库大小显示

创建两张表，表大小和数据库大小

 

删除一部分数据，继续观察表大小和数据库大小

进行vaccum继续观察表大小和数据库大小，大小不变

创建索引，观察表大小和数据库大小，发现显示的表大小不变，数据库大小变大

删除一部分数据发现表大小和数据库大小都不变

再创建一个索引，进行vacuum full 发现空间得到回收

psql -E 查看完整的查询语句

\l+的查询语句

\d+的查询语句

显示表的语句不显示索引

小小的修改一下

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','i','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

显示带索引的大小