Обсуждение: Query with date where clause is very slow

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!

Mike

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?

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?
 

On Thu, Feb 19, 2009 at 10:17 PM, Mike Christensen <imaudi@comcast.net> wrote:
> 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.
>

I assume the data was properly representative of the real data, and
that you were running analyze; before running any queries.  Normally
when you see this kind of slow down it's that postgresql picked a bad
query plan because of bad estimates.  Most bad estimates can be fixed
with simple increases in the the stats target for the offending
column.  Sometimes it takes more than that.  Look for numbers of row
estimated versus actual rows returned, and loops.

On 2009-02-20, 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;

converting timestamp to date is moderately complex (lots of integer division)
try this instead of (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;

 ('today'::date -14)

indexing notifications on (userid,date) may help significantly too,

bye.