I have a problem in migrating data from MS SQL 2008 into Greenplum (PosgresSql).. I have a condition where the pg_catalog size is bloated until more than 50GB.It has 36 schema, and have 2000+ tables inside each schema.

I'm using SSIS (Import Export Data Wizard) to move the data.it takes a long time for loading the table list for mapping. If I edit the parameters via Visual Studio it will be "validating data flow task" and caused Visual Studio hang.If I managed to get Visual Studio to run the package, the validating step will take a long time also (aprox 1 hour till the package actually start copying the data).

If I capture the script that fired to Greenplum at the validation steps, it returns this query : select * from (select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME", A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION", A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE", TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL", A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as "COLUMN_DEFAULT" from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_tables T on (C.relname=T.tablename) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum>0 and A.attisdropped='f'

There are different ways to speed-up queries like that. One way is to use "Query Optimizer" (currently undocumented). You will need to create special views for fast metadata retrieval, and configure optimizer hints in the PGNP_OPTIMIZER table. Please communicate to support for more details.