On Thu, Feb 19, 2009 at 9:32 PM, Mike Christensen <imaudi@xxxxxxxxxxx> wrote:
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!
What does explain analyze select ... have to say about each?
