WITH index_op_class AS (SELECT indexrelid, indrelid, indisunique, op_class, ordin
FROM pg_index, unnest(indclass) with ordinality AS k(op_class, ordin)
WHERE indexprs IS NULL
AND indisunique='f'),
indexes AS (SELECT 
  n.nspname AS table_schema,
  c2.relname AS table_name,
  a.attname AS column_name,
  c.relname AS index_name,
  ordin AS ordinal_position,  
  opcname AS operator_class
FROM 
  pg_class AS c INNER JOIN index_op_class AS i ON i.indexrelid = c.oid
  INNER JOIN pg_class AS c2  ON i.indrelid = c2.oid
  INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid
  INNER JOIN pg_authid AS u ON  n.nspowner = u.oid
  INNER JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attnum=i.ordin
  INNER JOIN pg_opclass AS oc ON i.op_class=oc.oid
WHERE c.relkind = 'i'
AND a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')
AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid)),
fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select 
(select nspname from pg_namespace where oid=m.relnamespace) as target_schema,
m.relname as target_table, 
m.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype='f'
and o.conrelid in (select oid from pg_class c where c.relkind = 'r')) t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false),
textual_columns AS (SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE data_type IN ('text', 'character','character varying') AND 
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT table_schema, table_name, column_name, index_name, operator_class
FROM indexes AS i
WHERE (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name
FROM textual_columns)
AND  (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name
FROM fk_columns)
AND operator_class NOT LIKE '%\_pattern_ops'
AND NOT EXISTS (SELECT *
FROM indexes AS i2
WHERE i.table_schema=i2.table_schema
AND i.table_name=i2.table_name
AND i.column_name=i2.column_name
AND i2.operator_class LIKE '%\_pattern_ops')
ORDER BY table_schema, index_name, ordinal_position;