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
