查找无用列

管理片段

查找无用列

适用于 PostgreSQL

8.2+

用

SQL

依赖于

无

此查询使用规划器的估计值，查找整个数据库中其表中唯一值不超过 1 个的列。

这对于查找冗余和未使用的列很有用。

select nspname, relname, attname, typname,
    (stanullfrac*100)::int as null_percent,
    case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end as "distinct",
    case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end as "values"
from pg_class c
join pg_namespace ns on (ns.oid=relnamespace)
join pg_attribute on (c.oid=attrelid)
join pg_type t on (t.oid=atttypid)
join pg_statistic on (c.oid=starelid and staattnum=attnum)
where nspname not like E'pg\\_%' and nspname != 'information_schema'
  and relkind='r' and not attisdropped and attstattarget != 0
  and reltuples >= 100              -- ignore tables with fewer than 100 rows
  and stadistinct between 0 and 1   -- 0 to 1 distinct values
order by nspname, relname, attname
;

另请参阅：pg_statistic 目录文档