From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Mike Christensen <imaudi(at)comcast(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query with date where clause is very slow |
Date: | 2009-02-20 05:13:26 |
Message-ID: | dcc563d10902192113w242928e2r8dc28a3526a6a6ba@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Christensen | 2009-02-20 05:17:20 | Re: Query with date where clause is very slow |
Previous Message | Mike Christensen | 2009-02-20 04:32:35 | Query with date where clause is very slow |