Re: Query with date where clause is very slow

On Thu, Feb 19, 2009 at 9:32 PM, Mike Christensen <imaudi(at)comcast(dot)net> 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?