How to determine which tables are missing indexes

Every once in a while - particularly if you are using inherited tables, you forget to put an important index on one of your tables which bogs down critical queries. Its sometimes convenient to inspect the index catalog to see what tables are missing indexes or what tables are missing a critical index. Normally we try to stick with querying the information_schema because queries against that schema work pretty much the same in PostgreSQL as they do in SQL Server and MySQL. For most of the examples below we had to delve into pg_catalog schema territory since there was no view we could find in information_schema that would give us enough detail about indexes.

Problem: Return all non-system tables that are missing primary keys

Solution:

This will actually work equally well on SQL Server, MySQL and any other database that supports the Information_Schema standard. It won't check for unique indexes though.

SELECT c.table_schema, c.table_name, c.table_type
FROM information_schema.tables c
WHERE c.table_type = 'BASE TABLE' AND c.table_schema NOT IN('information_schema', 'pg_catalog') 
AND
NOT EXISTS (SELECT cu.table_name 
				FROM information_schema.key_column_usage cu
				WHERE cu.table_schema = c.table_schema AND
					cu.table_name = c.table_name)
ORDER BY c.table_schema, c.table_name;

Problem: Return all non-system tables that are missing primary keys and have no unique indexes

Solution - this one is not quite as portable. We had to delve into the pg_catalog since we couldn't find a table in information schema that would tell us anything about any indexes but primary keys and foreign keys. Even though in theory primary keys and unique indexes are the same, they are not from a meta data standpoint.

SELECT c.table_schema, c.table_name, c.table_type
FROM information_schema.tables c
WHERE  c.table_schema NOT IN('information_schema', 'pg_catalog') AND c.table_type = 'BASE TABLE' 
AND NOT EXISTS(SELECT i.tablename  
				FROM pg_catalog.pg_indexes i 
			WHERE i.schemaname = c.table_schema 
				AND i.tablename = c.table_name AND indexdef LIKE '%UNIQUE%')
AND
NOT EXISTS (SELECT cu.table_name 
				FROM information_schema.key_column_usage cu
				WHERE cu.table_schema = c.table_schema AND
					cu.table_name = c.table_name)
ORDER BY c.table_schema, c.table_name;

Problem - List all tables with geometry fields that have no index on the geometry field.

Solution -

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;