SQL to generate CREATE INDEX statements for Mirth Connect metadata columns for Postgres

create-mc-metadata-indexes.postgres.sql

--see https://github.com/nextgenhealthcare/connect/issues/4320
-- this generates create index statements for the metadata columns in MC excluding the default columns
-- the user SHOULD NOT BLINDLY CREATE ALL INDEXES
-- MC is update/insert heavy and having indexes can slow that performance. 
-- These indexes should be used only on metadata colums which are searched frequently, on large tables, and where constraining by other indexed columns is not practical
-- Further, if you're searching on large tables you should run the damn pruner. see https://gist.github.com/MichaelLeeHobbs/40b4b7cf70ecbe30b73eed763367e626
SELECT t.table_name, c.column_name, format('CREATE INDEX CONCURRENTLY metadata_hax_%1$s_%2$s ON %1$s("%2$s");', t.table_name, c.column_name) as create_stmt                                                     
FROM information_schema.tables t   
INNER JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = c.table_schema                         
WHERE t.table_schema='public'                                                  
AND t.table_type='BASE TABLE' 
AND t.table_name LIKE 'd_mcm%'
AND c.column_name NOT IN ('metadata_id', 'message_id', 'SOURCE', 'TYPE')
;