--From http://www.postgresonline.com/journal/archives/65-How-to-determine-which-tables-are-missing-indexes.html

SELECT c.table_schema, c.table_name, c.column_name FROM (SELECT * FROM	information_schema.tables WHERE table_type = 'BASE TABLE') As t  INNER JOIN	(SELECT * FROM information_schema.columns WHERE udt_name = 'geometry') c		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)		LEFT JOIN pg_catalog.pg_indexes i ON			(i.tablename = c.table_name AND i.schemaname = c.table_schema				AND  indexdef LIKE '%' || c.column_name || '%') WHERE i.tablename IS NULL ORDER BY c.table_schema, c.table_name;

CREATE OR REPLACE VIEW pg_table_nonindex_x AS
SELECT
x1.table_in_trouble,
pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
x1.seq_scan, x1.idx_scan,
CASE
WHEN pg_relation_size(x1.table_in_trouble) > 500000000
THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
ELSE count(x1.table_in_trouble)::text
END AS tbl_rec_count,
x1.priority
FROM
(
SELECT
(schemaname::text || '.'::text) || relname::text AS table_in_trouble,
seq_scan,
idx_scan,
CASE
WHEN (seq_scan - idx_scan) < 500 THEN 'Minor Problem'::text
WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500 THEN 'Major Problem'::text
WHEN (seq_scan - idx_scan) >= 2500 THEN 'Extreme Problem'::text
ELSE NULL::text
END AS priority
FROM
pg_stat_all_tables
WHERE
seq_scan > idx_scan
AND schemaname != 'pg_catalog'::name
AND seq_scan > 100) x1
GROUP BY
x1.table_in_trouble,
x1.seq_scan,
x1.idx_scan,
x1.priority
ORDER BY
x1.priority DESC,
x1.seq_scan
;
SELECT * FROM pg_table_nonindex_x;