CODEWITHSUNDEEP
sqlpostgresql
William Falcon
William Falcon

Reputation: 9823

Speed up active user query

This query doesn't complete right now because the window is too large. Any way to speed it up?

select
    gs.date,
    (select count(distinct customer_id) 
     from messages 
     where direction = 'received' 
       and created_at between gs.date - interval '89 days' and gs.date)
from
    generate_series('2015-07-09'::date, current_date::date, interval '1 day') gs
order by gs.date;

Upvotes: 1

Views: 36

Answers (1)

gcbenison
gcbenison

Reputation: 11963

Try the following:

select
  gs.date,
  count(distinct(customer_id))
from
  messages
  inner join generate_series('2015-07-09'::date, current_date::date, interval '1 day') gs
where
  direction = 'received'
  and created_at between gs.date - interval '89 days' and gs.date
group by gs.date
order by gs.date

It will still be an expensive query (doing a rolling unique count like that is just an inherently expensive thing to calculate) but I think that will be faster than having a subquery in your select clause. The query planner has more freedom to choose an efficient plan using a join and a group by.

Upvotes: 1

Related Questions