Re: Query with date where clause is very slow

I'd tell you, but I lost the database last night <g>

I'll go rebuild the test data but it'll take a while.

Scott Marlowe wrote:

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