CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
AlexV
AlexV

Reputation: 3896

INSERT INTO table SELECT Redshift super slow

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'

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:

Upvotes: 7

Views: 7292

Answers (1)

Red Boy
Red Boy

Reputation: 5729

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.

Upvotes: 4

Related Questions