Disabling/Enabling index before bulk loading

From: Ravi Krishna <srkrishna(at)yahoo(dot)com>
To: PG mailing List <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Disabling/Enabling index before bulk loading
Date: 2018-07-10 14:08:32
Message-ID: 67D408BB-5BF7-4607-A730-6F262F65811A@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2018-07-10 14:24:59 Re: Disabling/Enabling index before bulk loading
Previous Message a 2018-07-10 14:01:01 Re: Reporting bug on pgAdmin 4.3