0

I have a complex Redshift view looking to filter results based on a variable date range. So I have to compare a date and interval to CURRENT_DATE. The more complex the view, the longer the query takes. Even simply SELECT'ing CURRENT_DATE within the view results in a significant slowdown. 

SELECT CURRENT_DATE FROM complex_view; ==> Average time: ~ 800ms

SELECT CURRENT_DATE FROM less_complex_view; ==> Average time: ~ 400ms

SELECT CURRENT_DATE; ==> Average time: ~ 30ms

The query also never seems to become cached unlike even the following:

SELECT * FROM complex_view; ==> Average time after 4 slow initial calls: ~30 ms

However, if I insert CURRENT_DATE into a table in the view, and compare using that instead, the query is fast. 

SELECT curr_date_in_table FROM complex_view; ==> Average time: ~ 30ms

The issue with that is more complexity (a cron job to update a single row daily, when the task is honestly quite a basic one) and worse code maintainability. Why is it that simply referring to CURRENT_DATE in certain situations takes so much time? As with this very old related post, hardcoding the date also ensures a quick runtime, but I would like to automate the process.

I'm relatively new to using EXPLAINs, but there seemed to be no noticeable difference between querying using either the hardcoded current date, curr_date_in_table, or CURRENT_DATE. They all have some ridiculously high top-level cost regardless of runtime.

EDIT: Pavel and Jasen seem to be correct. I created an immutable UDF to return GETDATE() in SQL, and queries on the view ran nearly instantly. It only needs to be defined once, so automation and code maintainability are back on track! It is still very strange that this basic functionality needs to be redefined.

user10168319
  • 3
  • 2
  • It might be because `CURRENT_DATE` is executed on the leader node rather than the compute nodes (but I'm not sure). – John Rotenstein Aug 02 '18 at 04:31
  • `CURRENT_DATE` expands to `now()::date` which implies a timezone conversion (from timestamptz), timezone conversion is stable SFAIK not immutable - not sure if that's relevant. – Jasen Aug 02 '18 at 09:04

1 Answers1

0

CURRENT_DATE is a function, and usually should be very fast (on my comp about 300us). I really don't know what is real reason of your slow query - it is not possible deduce from informations that are here. The fundamental information is a execution plan of slow query, and it is not here.

But I am think so there can be some optimization issue. CURRENT_DATE although doesn't look like function, it is a function (stable function). Stable functions are not evaluated in planning/optimization stage - so when you use CURRENT_DATE in your query, the optimizer doesn't know what is a value and cannot be too aggressive.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94