Postgres inner query performance

Question

I have a table which I need to select from everything with this rule:

id = 4524522000143 and validPoint = true
and date >  (max(date)- interval '12 month')
-- the max date, is the max date for this id

Explaining the rule: I have to get all registers and count them, they must be at least 1 year old from the newest register.

This is my actual query:

WITH points as (
    select 1 as ct from base_faturamento_mensal
    where id = 4524522000143 and validPoint = true
    group by id,date
    having date >  (max(date)- interval '12 month')
)  select sum(ct) from points

Is there a more efficient way for this?

Answer 1

Well your query is using the trick with including an unaggregated column within HAVING clause but I don't find it particularly bad. It seems fine, but without the EXPLAIN ANALYZE <query> output I can't say much more.

One thing to do is you can get rid of the CTE and use count(*) within the same query instead of returning 1 and then running a sum on it afterwards.

select count(*) as ct
from base_faturamento_mensal
where id = 4524522000143 
  and validPoint = true
group by id, date
having date > max(date) - interval '12 months'