Finding useless columns

Administrative Snippets

Finding useless columns

Works with PostgreSQL

8.2+

Written in

SQL

Depends on

Nothing

This query finds columns in the whole database that have no more than 1 distinct value in its table, using planner's estimates.

This is useful for finding redundant and unused columns.

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
;

See also: pg_statistic catalog documentation