INSERT INTO table SELECT Redshift super slow

Question

We have a large table, that we need to do a DEEP COPY on it. Since we don't have enough empty disk space to make it in one statements I've tried to make it in batches. But the batches seem to run very very slowly.

I'm running something like this:

   INSERT INTO new_table 
   SELECT * FROM old_table 
    WHERE creation_date between '2018-01-01' AND '2018-02-01'

Even though the query returns small amount of lines ~ 1K

SELECT * FROM old_table 
WHERE creation_date between '2018-01-01' AND '2018-02-01'

• The INSERT query take around 50 minutes to complete.

• The old_table has ~286M rows and ~400 columns

• creation_date is one of the SORTKEYs

Explain plan looks like:

XN Seq Scan on old_table  (cost=0.00..4543811.52 rows=178152 width=136883)
      Filter: ((creation_date <= '2018-02-01'::date) AND (creation_date >= '2018 01-01'::date))

My question is:

• What may be the reason for INSERT query to take this long?

Answer 1

In my opinion, following are two possibilities--- though if you could add more details to your question will be great.

1. As @John stated in comments, your SORTKEY matters a lot in RedShift, is creation_date sortkey?
2. Did you do lot of updates to your old_table, if so, you must to vacuum first do VACUUM DELETE Only old_table then, do select queries.

Other option, you might be doing S3 way, but not sure do you want to do it.