0

We have a queries(likely complex and big) generated by our BI application.

When I am running the query for the first time it takes around 8 to 9 mins to execute but when I am executing it second time it is taking less time(15 secs). I am not sure it is because of complexity of query or the red-shift spending more time in preparing the query execution plan.

I have tested the same query in two different environments, whenever it is running for the very first time takes around 8-9 mins of time and when I run it next time, it returns the result set in less than 10 secs.

I was suspecting that red shift is spending considerably good amount of time in preparing the query plan and executable segments. to clarify this I have checked the svl_compile table, for first time execution compile field was set to 1 and for other subsequent run it was set to 0 and whenever any changes in SELECT clause it is re-compiling the query and taking minutes to return the result set.

Is it safe to assume that : First run is taking more time because of compilation of query and preparing the query plan and it is taking more time only in collecting metadata.?

If yes then is there any way to reduce the compile time? Even if underlying table doesn't have any data it will take same amount of time in compilation?

Note : I have disabled the result cache before running the query.
SET enable_result_cache_for_session TO OFF;
Instance type: dc2.large

SELECT 
replace(dim_product.dow_days,';','\n') AS "dim_product.dow_days",
dim_market.market_name,
replace(dim_product.dow_time,';','\n')  AS "dim_product.dow_time",
dim_product.product_name ||'-'|| dim_product.station_name  AS 
"dim_product.product",
DATE(dim_product.first_telecast_date ) AS "dim_product.first_telecast",
DATE(dim_product.last_telecast_date ) AS "dim_product.last_telecast",
dim_product.hiatus_date  AS "dim_product.hiatus_date",
dim_book.book_name  AS "dim_book.book_name",
  sum(prn.a1214_rating)   AS "prn.a1214_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1214_rating)) DESC)  AS 
"prn.a1214_rating_rank",
  sum(prn.a1217_rating)   AS "prn.a1217_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1217_rating)) DESC)  AS 
"prn.a1217_rating_rank",
  sum(prn.a1220_rating)   AS "prn.a1220_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1220_rating)) DESC)  AS 
"prn.a1220_rating_rank",
  sum(prn.a1224_rating)   AS "prn.a1224_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1224_rating)) DESC)  AS 
"prn.a1224_rating_rank",
  sum(prn.a1234_rating)   AS "prn.a1234_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1234_rating)) DESC)  AS 
"prn.a1234_rating_rank",
  sum(prn.a1249_rating)   AS "prn.a1249_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1249_rating)) DESC)  AS 
"prn.a1249_rating_rank",
  sum(prn.a1254_rating)   AS "prn.a1254_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1254_rating)) DESC)  AS 
"prn.a1254_rating_rank",
  sum(prn.a1264_rating)   AS "prn.a1264_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1264_rating)) DESC)  AS 
"prn.a1264_rating_rank",
  sum(prn.a12plus_rating)   AS "prn.a12plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a12plus_rating)) DESC)  AS 
"prn.a12plus_rating_rank",
sum(prn.a1517_rating)   AS "prn.a1517_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1517_rating)) DESC)  AS 
"prn.a1517_rating_rank",
  sum(prn.a1520_rating)   AS "prn.a1520_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1520_rating)) DESC)  AS 
"prn.a1520_rating_rank",
  sum(prn.a1524_rating)   AS "prn.a1524_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1524_rating)) DESC)  AS 
"prn.a1524_rating_rank",
  sum(prn.a1534_rating)   AS "prn.a1534_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1534_rating)) DESC)  AS 
"prn.a1534_rating_rank",
  sum(prn.a1549_rating)   AS "prn.a1549_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1549_rating)) DESC)  AS 
"prn.a1549_rating_rank",
  sum(prn.a1554_rating)   AS "prn.a1554_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1554_rating)) DESC)  AS 
"prn.a1554_rating_rank",
  sum(prn.a1564_rating)   AS "prn.a1564_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1564_rating)) DESC)  AS 
"prn.a1564_rating_rank",
  sum(prn.a15plus_rating)   AS "prn.a15plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a15plus_rating)) DESC)  AS 
"prn.a15plus_rating_rank",
  sum(prn.a1820_rating)   AS "prn.a1820_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1820_rating)) DESC)  AS 
"prn.a1820_rating_rank",
  sum(prn.a1824_rating)   AS "prn.a1824_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1824_rating)) DESC)  AS 
"prn.a1824_rating_rank",
  sum(prn.a1834_rating)   AS "prn.a1834_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1834_rating)) DESC)  AS "prn.a1834_rating_rank",
  sum(prn.a1849_rating)   AS "prn.a1849_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1849_rating)) DESC)  AS "prn.a1849_rating_rank",
  sum(prn.a1854_rating)   AS "prn.a1854_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1854_rating)) DESC)  AS "prn.a1854_rating_rank",
  sum(prn.a1864_rating)   AS "prn.a1864_rating",
dense_rank() OVER (ORDER BY (sum(prn.a1864_rating)) DESC)  AS "prn.a1864_rating_rank",
  sum(prn.a18plus_rating)   AS "prn.a18plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a18plus_rating)) DESC)  AS "prn.a18plus_rating_rank",
  sum(prn.a2124_rating)   AS "prn.a2124_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2124_rating)) DESC)  AS "prn.a2124_rating_rank",
  sum(prn.a2134_rating)   AS "prn.a2134_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2134_rating)) DESC)  AS "prn.a2134_rating_rank",
  sum(prn.a2149_rating)   AS "prn.a2149_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2149_rating)) DESC)  AS "prn.a2149_rating_rank",
  sum(prn.a2154_rating)   AS "prn.a2154_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2154_rating)) DESC)  AS "prn.a2154_rating_rank",
  sum(prn.a2164_rating)   AS "prn.a2164_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2164_rating)) DESC)  AS "prn.a2164_rating_rank",
  sum(prn.a21plus_rating)   AS "prn.a21plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a21plus_rating)) DESC)  AS "prn.a21plus_rating_rank",
  sum(prn.a2534_rating)   AS "prn.a2534_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2534_rating)) DESC)  AS "prn.a2534_rating_rank",
  sum(prn.a2549_rating)   AS "prn.a2549_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2549_rating)) DESC)  AS "prn.a2549_rating_rank",
  sum(prn.a2554_rating)   AS "prn.a2554_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2554_rating)) DESC)  AS "prn.a2554_rating_rank",
  sum(prn.a2564_rating)   AS "prn.a2564_rating",
dense_rank() OVER (ORDER BY (sum(prn.a2564_rating)) DESC)  AS "prn.a2564_rating_rank",
  sum(prn.a25plus_rating)   AS "prn.a25plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a25plus_rating)) DESC)  AS "prn.a25plus_rating_rank",
  sum(prn.a3549_rating)   AS "prn.a3549_rating",
dense_rank() OVER (ORDER BY (sum(prn.a3549_rating)) DESC)  AS "prn.a3549_rating_rank",
  sum(prn.a3554_rating)   AS "prn.a3554_rating",
dense_rank() OVER (ORDER BY (sum(prn.a3554_rating)) DESC)  AS "prn.a3554_rating_rank",
  sum(prn.a3564_rating)   AS "prn.a3564_rating",
dense_rank() OVER (ORDER BY (sum(prn.a3564_rating)) DESC)  AS "prn.a3564_rating_rank",
  sum(prn.a35plus_rating)   AS "prn.a35plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a35plus_rating)) DESC)  AS "prn.a35plus_rating_rank",
  sum(prn.a5054_rating)   AS "prn.a5054_rating",
dense_rank() OVER (ORDER BY (sum(prn.a5054_rating)) DESC)  AS "prn.a5054_rating_rank",
  sum(prn.a5064_rating)   AS "prn.a5064_rating",
dense_rank() OVER (ORDER BY (sum(prn.a5064_rating)) DESC)  AS "prn.a5064_rating_rank",
  sum(prn.a50plus_rating)   AS "prn.a50plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a50plus_rating)) DESC)  AS "prn.a50plus_rating_rank",
  sum(prn.a5564_rating)   AS "prn.a5564_rating",
dense_rank() OVER (ORDER BY (sum(prn.a5564_rating)) DESC)  AS "prn.a5564_rating_rank",
  sum(prn.a55plus_rating)   AS "prn.a55plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a55plus_rating)) DESC)  AS "prn.a55plus_rating_rank",
  sum(prn.a65plus_rating)   AS "prn.a65plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.a65plus_rating)) DESC)  AS "prn.a65plus_rating_rank",
  sum(prn.c211_rating)   AS "prn.c211_rating",
dense_rank() OVER (ORDER BY (sum(prn.c211_rating)) DESC)  AS "prn.c211_rating_rank",
  sum(prn.c25_rating)   AS "prn.c25_rating",
dense_rank() OVER (ORDER BY (sum(prn.c25_rating)) DESC)  AS "prn.c25_rating_rank",
  sum(prn.c611_rating)   AS "prn.c611_rating",
dense_rank() OVER (ORDER BY (sum(prn.c611_rating)) DESC)  AS "prn.c611_rating_rank",
  sum(prn.f1214_rating)   AS "prn.f1214_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1214_rating)) DESC)  AS "prn.f1214_rating_rank",
  sum(prn.f1217_rating)   AS "prn.f1217_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1217_rating)) DESC)  AS "prn.f1217_rating_rank",
  sum(prn.f1220_rating)   AS "prn.f1220_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1220_rating)) DESC)  AS "prn.f1220_rating_rank",
  sum(prn.f1224_rating)   AS "prn.f1224_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1224_rating)) DESC)  AS "prn.f1224_rating_rank",
  sum(prn.f1234_rating)   AS "prn.f1234_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1234_rating)) DESC)  AS "prn.f1234_rating_rank",
  sum(prn.f1249_rating)   AS "prn.f1249_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1249_rating)) DESC)  AS "prn.f1249_rating_rank",
  sum(prn.f1254_rating)   AS "prn.f1254_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1254_rating)) DESC)  AS "prn.f1254_rating_rank",
  sum(prn.f1264_rating)   AS "prn.f1264_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1264_rating)) DESC)  AS "prn.f1264_rating_rank",
  sum(prn.f12plus_rating)   AS "prn.f12plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f12plus_rating)) DESC)  AS "prn.f12plus_rating_rank",
  sum(prn.f1517_rating)   AS "prn.f1517_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1517_rating)) DESC)  AS "prn.f1517_rating_rank",
  sum(prn.f1520_rating)   AS "prn.f1520_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1520_rating)) DESC)  AS "prn.f1520_rating_rank",
  sum(prn.f1524_rating)   AS "prn.f1524_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1524_rating)) DESC)  AS "prn.f1524_rating_rank",
  sum(prn.f1534_rating)   AS "prn.f1534_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1534_rating)) DESC)  AS "prn.f1534_rating_rank",
  sum(prn.f1549_rating)   AS "prn.f1549_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1549_rating)) DESC)  AS "prn.f1549_rating_rank",
  sum(prn.f1554_rating)   AS "prn.f1554_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1554_rating)) DESC)  AS "prn.f1554_rating_rank",
  sum(prn.f1564_rating)   AS "prn.f1564_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1564_rating)) DESC)  AS "prn.f1564_rating_rank",
  sum(prn.f15plus_rating)   AS "prn.f15plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f15plus_rating)) DESC)  AS "prn.f15plus_rating_rank",
  sum(prn.f1820_rating)   AS "prn.f1820_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1820_rating)) DESC)  AS "prn.f1820_rating_rank",
  sum(prn.f1824_rating)   AS "prn.f1824_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1824_rating)) DESC)  AS "prn.f1824_rating_rank",
  sum(prn.f1834_rating)   AS "prn.f1834_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1834_rating)) DESC)  AS "prn.f1834_rating_rank",
  sum(prn.f1849_rating)   AS "prn.f1849_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1849_rating)) DESC)  AS "prn.f1849_rating_rank",
  sum(prn.f1854_rating)   AS "prn.f1854_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1854_rating)) DESC)  AS "prn.f1854_rating_rank",
  sum(prn.f1864_rating)   AS "prn.f1864_rating",
dense_rank() OVER (ORDER BY (sum(prn.f1864_rating)) DESC)  AS "prn.f1864_rating_rank",
  sum(prn.f18plus_rating)   AS "prn.f18plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f18plus_rating)) DESC)  AS "prn.f18plus_rating_rank",
  sum(prn.f2124_rating)   AS "prn.f2124_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2124_rating)) DESC)  AS "prn.f2124_rating_rank",
  sum(prn.f2134_rating)   AS "prn.f2134_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2134_rating)) DESC)  AS "prn.f2134_rating_rank",
  sum(prn.f2149_rating)   AS "prn.f2149_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2149_rating)) DESC)  AS "prn.f2149_rating_rank",
  sum(prn.f2154_rating)   AS "prn.f2154_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2154_rating)) DESC)  AS "prn.f2154_rating_rank",
  sum(prn.f2164_rating)   AS "prn.f2164_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2164_rating)) DESC)  AS "prn.f2164_rating_rank",
  sum(prn.f21plus_rating)   AS "prn.f21plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f21plus_rating)) DESC)  AS "prn.f21plus_rating_rank",
  sum(prn.f2534_rating)   AS "prn.f2534_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2534_rating)) DESC)  AS "prn.f2534_rating_rank",
  sum(prn.f2549_rating)   AS "prn.f2549_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2549_rating)) DESC)  AS "prn.f2549_rating_rank",
  sum(prn.f2554_rating)   AS "prn.f2554_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2554_rating)) DESC)  AS "prn.f2554_rating_rank",
  sum(prn.f2564_rating)   AS "prn.f2564_rating",
dense_rank() OVER (ORDER BY (sum(prn.f2564_rating)) DESC)  AS "prn.f2564_rating_rank",
  sum(prn.f25plus_rating)   AS "prn.f25plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f25plus_rating)) DESC)  AS "prn.f25plus_rating_rank",
  sum(prn.f3549_rating)   AS "prn.f3549_rating",
dense_rank() OVER (ORDER BY (sum(prn.f3549_rating)) DESC)  AS "prn.f3549_rating_rank",
  sum(prn.f3554_rating)   AS "prn.f3554_rating",
dense_rank() OVER (ORDER BY (sum(prn.f3554_rating)) DESC)  AS "prn.f3554_rating_rank",
  sum(prn.f3564_rating)   AS "prn.f3564_rating",
dense_rank() OVER (ORDER BY (sum(prn.f3564_rating)) DESC)  AS "prn.f3564_rating_rank",
  sum(prn.f35plus_rating)   AS "prn.f35plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f35plus_rating)) DESC)  AS "prn.f35plus_rating_rank",
  sum(prn.f5054_rating)   AS "prn.f5054_rating",
dense_rank() OVER (ORDER BY (sum(prn.f5054_rating)) DESC)  AS "prn.f5054_rating_rank",
  sum(prn.f5064_rating)   AS "prn.f5064_rating",
dense_rank() OVER (ORDER BY (sum(prn.f5064_rating)) DESC)  AS "prn.f5064_rating_rank",
  sum(prn.f50plus_rating)   AS "prn.f50plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f50plus_rating)) DESC)  AS "prn.f50plus_rating_rank",
  sum(prn.f5564_rating)   AS "prn.f5564_rating",
dense_rank() OVER (ORDER BY (sum(prn.f5564_rating)) DESC)  AS "prn.f5564_rating_rank",
  sum(prn.f55plus_rating)   AS "prn.f55plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f55plus_rating)) DESC)  AS "prn.f55plus_rating_rank",
  sum(prn.f65plus_rating)   AS "prn.f65plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.f65plus_rating)) DESC)  AS "prn.f65plus_rating_rank",
  sum(prn.hh_rating)   AS "prn.hh_rating",
dense_rank() OVER (ORDER BY (sum(prn.hh_rating)) DESC)  AS "prn.hh_rating_rank",
  sum(prn.m1214_rating)   AS "prn.m1214_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1214_rating)) DESC)  AS "prn.m1214_rating_rank",
  sum(prn.m1217_rating)   AS "prn.m1217_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1217_rating)) DESC)  AS "prn.m1217_rating_rank",
  sum(prn.m1220_rating)   AS "prn.m1220_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1220_rating)) DESC)  AS "prn.m1220_rating_rank",
  sum(prn.m1224_rating)   AS "prn.m1224_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1224_rating)) DESC)  AS "prn.m1224_rating_rank",
  sum(prn.m1234_rating)   AS "prn.m1234_rating",
  sum(prn.m1249_rating)   AS "prn.m1249_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1249_rating)) DESC)  AS "prn.m1249_rating_rank",
  sum(prn.m1254_rating)   AS "prn.m1254_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1254_rating)) DESC)  AS "prn.m1254_rating_rank",
  sum(prn.m1264_rating)   AS "prn.m1264_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1264_rating)) DESC)  AS "prn.m1264_rating_rank",
  sum(prn.m12plus_rating)   AS "prn.m12plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m12plus_rating)) DESC)  AS "prn.m12plus_rating_rank",
  sum(prn.m1517_rating)   AS "prn.m1517_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1517_rating)) DESC)  AS "prn.m1517_rating_rank",
  sum(prn.m1520_rating)   AS "prn.m1520_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1520_rating)) DESC)  AS "prn.m1520_rating_rank",
  sum(prn.m1524_rating)   AS "prn.m1524_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1524_rating)) DESC)  AS "prn.m1524_rating_rank",
  sum(prn.m1534_rating)   AS "prn.m1534_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1534_rating)) DESC)  AS "prn.m1534_rating_rank",
  sum(prn.m1549_rating)   AS "prn.m1549_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1549_rating)) DESC)  AS "prn.m1549_rating_rank",
  sum(prn.m1554_rating)   AS "prn.m1554_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1554_rating)) DESC)  AS "prn.m1554_rating_rank",
  sum(prn.m1564_rating)   AS "prn.m1564_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1564_rating)) DESC)  AS "prn.m1564_rating_rank",
  sum(prn.m15plus_rating)   AS "prn.m15plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m15plus_rating)) DESC)  AS "prn.m15plus_rating_rank",
  sum(prn.m1820_rating)   AS "prn.m1820_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1820_rating)) DESC)  AS "prn.m1820_rating_rank",
  sum(prn.m1824_rating)   AS "prn.m1824_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1824_rating)) DESC)  AS "prn.m1824_rating_rank",
  sum(prn.m1834_rating)   AS "prn.m1834_rating",
  sum(prn.m1849_rating)   AS "prn.m1849_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1849_rating)) DESC)  AS "prn.m1849_rating_rank",
  sum(prn.m1854_rating)   AS "prn.m1854_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1854_rating)) DESC)  AS "prn.m1854_rating_rank",
  sum(prn.m1864_rating)   AS "prn.m1864_rating",
dense_rank() OVER (ORDER BY (sum(prn.m1864_rating)) DESC)  AS "prn.m1864_rating_rank",
  sum(prn.m18plus_rating)   AS "prn.m18plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m18plus_rating)) DESC)  AS "prn.m18plus_rating_rank",
  sum(prn.m2124_rating)   AS "prn.m2124_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2124_rating)) DESC)  AS "prn.m2124_rating_rank",
  sum(prn.m2134_rating)   AS "prn.m2134_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2134_rating)) DESC)  AS "prn.m2134_rating_rank",
  sum(prn.m2149_rating)   AS "prn.m2149_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2149_rating)) DESC)  AS "prn.m2149_rating_rank",
  sum(prn.m2154_rating)   AS "prn.m2154_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2154_rating)) DESC)  AS "prn.m2154_rating_rank",
  sum(prn.m2164_rating)   AS "prn.m2164_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2164_rating)) DESC)  AS "prn.m2164_rating_rank",
  sum(prn.m21plus_rating)   AS "prn.m21plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m21plus_rating)) DESC)  AS "prn.m21plus_rating_rank",
  sum(prn.m2534_rating)   AS "prn.m2534_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2534_rating)) DESC)  AS "prn.m2534_rating_rank",
  sum(prn.m2549_rating)   AS "prn.m2549_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2549_rating)) DESC)  AS "prn.m2549_rating_rank",
  sum(prn.m2554_rating)   AS "prn.m2554_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2554_rating)) DESC)  AS "prn.m2554_rating_rank",
  sum(prn.m2564_rating)   AS "prn.m2564_rating",
dense_rank() OVER (ORDER BY (sum(prn.m2564_rating)) DESC)  AS "prn.m2564_rating_rank",
  sum(prn.m25plus_rating)   AS "prn.m25plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m25plus_rating)) DESC)  AS "prn.m25plus_rating_rank",
  sum(prn.m3549_rating)   AS "prn.m3549_rating",
dense_rank() OVER (ORDER BY (sum(prn.m3549_rating)) DESC)  AS "prn.m3549_rating_rank",
  sum(prn.m3554_rating)   AS "prn.m3554_rating",
dense_rank() OVER (ORDER BY (sum(prn.m3554_rating)) DESC)  AS "prn.m3554_rating_rank",
  sum(prn.m3564_rating)   AS "prn.m3564_rating",
dense_rank() OVER (ORDER BY (sum(prn.m3564_rating)) DESC)  AS "prn.m3564_rating_rank",
  sum(prn.m35plus_rating)   AS "prn.m35plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m35plus_rating)) DESC)  AS "prn.m35plus_rating_rank",
  sum(prn.m5054_rating)   AS "prn.m5054_rating",
dense_rank() OVER (ORDER BY (sum(prn.m5054_rating)) DESC)  AS "prn.m5054_rating_rank",
  sum(prn.m5064_rating)   AS "prn.m5064_rating",
dense_rank() OVER (ORDER BY (sum(prn.m5064_rating)) DESC)  AS "prn.m5064_rating_rank",
  sum(prn.m50plus_rating)   AS "prn.m50plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m50plus_rating)) DESC)  AS "prn.m50plus_rating_rank",
  sum(prn.m5564_rating)   AS "prn.m5564_rating",
dense_rank() OVER (ORDER BY (sum(prn.m5564_rating)) DESC)  AS "prn.m5564_rating_rank",
  sum(prn.m55plus_rating)   AS "prn.m55plus_rating",
dense_rank() OVER (ORDER BY (sum(prn.m55plus_rating)) DESC)  AS "prn.m55plus_rating_rank",
  sum(prn.m65plus_rating)   AS "prn.m65plus_rating"
FROM demoschema.fact_r_s_n AS prn
INNER JOIN demp.dim_bk  AS dim_book ON prn.dim_book_id = dim_book.dim_book_id 
INNER JOIN demoschema.dim_mark  AS dim_market ON prn.market_code = dim_market.market_code  and dim_market.provider='NIELSEN'
INNER JOIN demoschema.dim_stat AS dim_station ON prn.station_code = dim_station.station_code
  AND prn.market_code = dim_station.market_code  and dim_station.provider='NIELSEN'
INNER JOIN demoschema.dim_product  AS dim_product ON prn.dim_product_id = dim_product.dim_product_id 
WHERE ((dim_book.book_id  IN ('5c515f8b9949bd3fb3756fd8', '5c5a9f609949bd276b662269', '5c503cca9949bd3cce6eb107', '5c53e8999949bd11fc70c91a',
'5c6275de9949bd22a1261b39', '5c516af09949bd3fb3756fdf', '5c503d239949bd3cce6eb108', '5c5290439949bd69392c416d', '5c62abeaff96879c94284c02',
'5c514baf9949bd3fb3756fce', '5c62b712ff96878660bf4568', '5c515400aa760f3ba803119f', '5c61e9fc9949bd04eaa7c441', '5c61a1159949bd04eaa7c43f',
'5c5d2c679949bd2eba645214', '5c5b02559949bd75b4ac037a', '5c5d33e99949bd2eba645217', '5c5c07439949bd75b4ac039f', '5c750b569949bd2c475b18b7', 
'5c62b4faff96878660bf454a'))) 
GROUP BY 1,2,3,4,5,6,7,8
ORDER BY 11 ,1 ,2 ,3 ,4 ,6 ,7 ,8 
LIMIT 5000`
mt_leo
  • 67
  • 1
  • 12
  • Have you had a look at the query plan? It should tell you how long each operation took. Also Redshift knows exactly when the data has changed, and maybe it will use the cache anyways it it's sure data has not changed. – botchniaque Apr 12 '19 at 04:42
  • Yes , I have looked and analyzed the query plan and the query summary , according to it last segment which includes (merge, project,limit and return ) operations took the max time(1.42 sec) which is minor part of the over all time taken by the query. Is there a way we can check the over all compile time taken by leader node before sending the executable segments to the compute nodes.I am not sure about svl_compile, does it give us the over all time which includes (compilation +execution ) or only compile time. – mt_leo Apr 12 '19 at 06:28
  • I cannot help you here - sorry – botchniaque Apr 12 '19 at 10:26
  • How many nodes are in your cluster? If more than one, redshift does copy data around to designated worker nodes that is otherwise spread around. That can accelerate subsequent queries. – systemjack Apr 18 '19 at 07:19
  • @systemjack it is 2 node cluster Cluster Type: Multi Node Node Type: dc2.large Nodes: 2 – mt_leo Apr 18 '19 at 07:29

0 Answers0