Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Ravi Krishna
> > I am very suspicious of why you need 16 indexes. Are you sure all those > indexes are actually being utilized? > Try executing the attached query, You may find find some are really not > needed. This is a DATAMART application and the indexes are to satisfy a large number of queries

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Melvin Davidson
> 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... *I am very suspicious of why you need 16 indexes. Are you sure all those indexes are actually being utilized?* *Try executing the attached query, You

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Tim Cross
Ravi Krishna writes: > 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

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Nikolay Samokhvalov
On Tue, Jul 10, 2018 at 12:26 PM Ravi Krishna wrote: > > > > https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > > > > > This does not work in RDS. In order to update system catalog tables > (pg_index), one needs privileges which is > denied in RDS. In RDS

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Ravi Krishna
> > > https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > > > This does not work in RDS. In order to update system catalog tables (pg_index), one needs privileges which is

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Vick Khera
On Tue, Jul 10, 2018 at 1:13 PM, Ravi Krishna wrote: > > > > Did you include the time to CREATE INDEX after the COPY or is the 1:14 > only for the COPY stage? > > Yes. > > Time taken to load 47 mil rows with all 16 indexes intact: 14+ hrs > Time taken to load the same after dropping index and

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Ravi Krishna
> > Did you include the time to CREATE INDEX after the COPY or is the 1:14 only > for the COPY stage? Yes. Time taken to load 47 mil rows with all 16 indexes intact: 14+ hrs Time taken to load the same after dropping index and then loading and finally creating 16 indexes: 1 hr 40 min

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Adrian Klaver
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

Re: Disabling/Enabling index before bulk loading

2018-07-10 Thread Thomas Kellerer
Ravi Krishna schrieb am 10.07.2018 um 16:08: > > 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

Disabling/Enabling index before bulk loading

2018-07-10 Thread Ravi Krishna
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