'Redshift query runs forever from only 2 mins by just changing a specific number to a variable
My query will automatically update every month in rundeck with updated date by using sysdate.
Here is the first query to generate the time variables for further use.
create temp table variables as
select dateadd('MONTH', 0, sysdate) as endMonth,
dateadd('MONTH', 1, sysdate) as endMonthPlusOne,
-- dateadd('MONTH', -12, sysdate) as startMonth,
dateadd('MONTH', -1, sysdate) as startMonth
;
Here is my second query:
create table ml_data.user_events_filtered distkey(userid) sortkey(eventid) as
select *
from user_events
where 1=1
and eventid=1011
and servertime_monthly = date_trunc('month', (select startMonth
from variables limit 1))
This will run forever until I changed the query to:
create table ml_data.user_events_filtered distkey(userid) sortkey(eventid) as
select userid, eventid, clienttime, charparam1, charparam2, charparam3, charparam4
from user_events
where 1=1
and eventid=1011
and servertime_monthly = '2015-01-01'
;
It takes 2 mins to run. I checked every possible reasons. Does it has something to do with leader node. I don't know if the query planner compile the subquery into a fixed date before running the primary query. I ask this questions since if I only run the subquery, it takes 0.3s. Since my table has huge number of rows, in total it might sum up to a formidable number. The table user_events contains 40 billion rows.
Per Sami's response, I run the EXPLAIN clause for both of the code and here is my result.
XN Seq Scan on user_events (cost=0.01..434963947.53 rows=205328 width=65)
Filter: ((eventid = 1011) AND ((servertime_monthly)::timestamp without time zone = date_trunc('month'::text, $0)))
InitPlan
-> XN Limit (cost=0.00..0.01 rows=1 width=8)
-> XN Seq Scan on variables (cost=0.00..0.01 rows=1 width=8)
XN Seq Scan on user_events (cost=0.00..326222960.64 rows=2945182 width=65)
Filter: ((eventid = 1011) AND (servertime_monthly = '2015-01-01'::date))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
