Re: Disabling/Enabling index before bulk loading

On 07/10/2018 07:08 AM, Ravi Krishna wrote:
>
> 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.

Did you include the time to CREATE INDEX after the COPY or is the 1:14
only for the COPY stage?

>
> 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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com