Hi all -
I have a fairly simple query:
select * from subscriptions s
inner join notifications n on n.userid = s.userid
inner join users u on u.userid = s.userid
where s.subscriberid='affaa328-5b53-430e-991a-22674ede6faf'
and n.date > (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;
It runs fairly slow (about 1200ms) with 10,000 rows in "users" and
200,000 rows in "subscriptions" and 500,000 rows in "notifications" and
I'm trying to figure out a way to speed this guy up. However, from what
I can tell the WHERE clause with the date is the thing really being a
hog here.
If I take out the last and just return all dates, the query runs in
about 300ms. I do have an index on notifications.date, btw..
Can someone point out exactly why this is running so slow? Perhaps it's
generating a new interval for each row or something? Is there a better
way to query rows by date? Thanks!
Mike
