We recently did a test on COPY and found that on large tables (47 million rows , 20GB of raw data) the
difference in COPY with 16 indexes and COPY without any index is 1:14. That is, COPY is 14 times slower
when data is ingested with all indexes as opposed to COPY first without index and then create all index.
I googled for earlier posting on this and it looks like this has been asked before too.
This is what I am thinking to do:
1 - Extract index definition and save it as a SQL somewhere, either a file or a table.
2 - Drop all indexes.
3 - Ingest data via COPY
4 - Recreate all indexes saved in (1).
Is there a generic sql or script or tool to accomplish (1).
thanks