| 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: | Whole Thread | Raw Message | 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
| 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 |