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(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?
>
>