Supported Data Types and Values - Amazon QuickSight says Quicksight has “date” as data type which supports both date and timestamp; if data source is a date field, it defaults hours, mins, seconds with 0.
I noticed when there is a date filter applied on the dataset, Quicksight sends the query with date_trunc for the date column. (ie. where date_trunc(‘day’, date) = date_trunc(‘day’, TIMESTAMP ‘2021-11-19 00:00:00’)). When such queries are sent to Redshift, it takes more time to process. I tested a simple select with “where date = 2021-11-19” vs “date_trunc(‘day’, date) = date_trunc(‘day’, TIMESTAMP ‘2021-11-19 00:00:00’)” and noticed significant difference in performance.
Is there something I can do to avoid wrapping the date_trunc function from quicksight? Are there alternative or am I missing something?
Hi @jianxiu, this is an interesting case and I will bring it back to the team for evaluation. Typically when you run a select in Redshift with dates though, you wrap the date in single quotes, otherwise unpredictable behavior can occur. I suspect that might be why you see a faster result with your original query. You can also experiment with manipulating the data type via a calculated field to see if there is any difference in behavior.
Thanks @chet for your response. It was a typo regarding the single quote.
Below are the scripts I just tested which gave same conclusion compared to what I tested before. My “dt” column is a partitioned field (sorted by field); I see exactly same execution plan (same steps and # of data scanned) for both queries, just different execution times. I also tried with different date filters, different execution orders, different sessions etc.
-- set cache to be off for the session
set enable_result_cache_for_session = false;
-- Takes about 300 ms
SELECT *
FROM public.host
WHERE dt = '2021-10-05'
LIMIT 500;
-- Takes about 20 seconds
SELECT *
FROM public.host
WHERE date_trunc('day', dt) = date_trunc('day', TIMESTAMP '2021-10-05 00:00:00')
LIMIT 500;
Hi @jianxiu, I have been able to replicate the issue and will contact the appropriate team regarding remediation. Thank you for your sample queries and analysis. This is very helpful when debugging! Edit: The reason that date_trunc is added is because when you set the filter you chose day granularity, so 5am today is considered the same as 6am today. There is an optimization that we can add here and I will follow up internally so that we can address it.