CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
bramm3s
bramm3s

Reputation: 1

Redshift: experiencing slow query performance between 2 segments

We’re experiencing slow query performance on AWS Redshift. Frequently we see that queries can take ±12 seconds to run, but only very little time (<500ms) is spent actually executing the query (according to the AWS Redshift console for an individual query).

Querying from svl_compile we can confirm that the query compilation plan is already compiled.

In svl_query_report we see a long time delay between the start times of 2 segments accounting for the majority of the run time, although the segments themselves all execute very quickly (milliseconds)

Upvotes: 0

Views: 1137

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

There are a number of things that could be going on but I suspect network distribution is involved. Check STL_DIST.

Another possibility is that Redshift broke the query up and a subquery is running during that window. This can happen with very complex queries. Review the plan and see if there are any references to computer generated table names (I think they begin with't' but this is just from memory).

Spilling to disk could be happening but this seems unlikely given what you have said so far. Also queuing delays doesn't seem like a match. Both are possible but not likely.

If you post more info about how the query is running things will narrow down. Actual execution report, explain plan, and/or logging table info would help hone in on what is happening during this time window.

Upvotes: 1

Related Questions