Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing.
Notes
The query uses information that is collected as the result of statistics collection process (see the ANALYZE statement). The side effect of running the test is that the statistics of the entire database will be refreshed.
Type
Problem detection (Each row in the result could represent a flaw in the design)
ANALYZE;
SELECT ps.schemaname, ps.tablename, ps.attname, psat.n_live_tup AS num_rows
FROM pg_catalog.pg_stats AS ps INNER JOIN pg_catalog.pg_stat_all_tables AS psat
ON ps.schemaname=psat.schemaname AND ps.tablename=psat.relname
WHERE ps.schemaname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND most_common_freqs = '{1}' AND psat.n_live_tup>1
AND psat.last_analyze IS NOT NULL
ORDER BY psat.n_live_tup DESC, ps.schemaname, ps.tablename;
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Data at the database logical level
Queries of this category provide information about data in base tables.
Missing data
Queries of this category provide information about missing data (NULLs) in a database.
Unused implementation elements
Queries of this catergory provide information about the database objects that are not used.