PostgreSQL multiple aggregation with same filters

Question

I have the following query which works fine

select created_date,
       count(*) filter ( where p_id=1 ) as count_tt_1,
       coalesce(sum(price) filter ( where p_id=1 ),0) as sum_tt_1,

       count(*) filter ( where tt_enum=2 and p_id=5 ) as count_tt_2,
       coalesce(sum(price) filter ( where tt_enum=2 and p_id=5 ),0) as sum_tt_2,

       count(*) filter ( where tt_enum=3 and p_id=5 ) as count_tt_3,
       coalesce(sum(price) filter ( where tt_enum=3 and p_id=5 ),0) as sum_tt_3
       from (
                  select created_date, price, tt_enum, p_id
                  from my_table
                  where falgged=0
       ) T GROUP BY created_date order by created_date

I wonder if there is a way to optimize it since it has repeating where clauses in every two filter so I'm assuming it filters twice.

Note that my actual aggregations might be much more than the 3x2 ones in this example and the where filters might have 2-3 where clauses.

I though of something like this

select created_date,
       count(*) filter ( where tt=1 ) as count_tt_1,
       coalesce(sum(price) filter ( where tt=1 ),0) as sum_tt_1,

       count(*) filter ( where tt=2 ) as count_tt_2,
       coalesce(sum(price) filter ( where tt=2 ),0) as sum_tt_2,

       count(*) filter ( where tt=3 ) as count_tt_3,
       coalesce(sum(price) filter ( where tt=3 ),0) as sum_tt_3
       from (
                  select created_date,price,
                         case when p_id=1 then 1
                              when tt_enum=2 and p_id=5 then 2
                              when tt_enum=3 and p_id=5 then 3 end as tt
                  from my_table
                  where falgged=0
       ) T GROUP BY created_date order by created_date

but is there a better way?

Gordon Linoff · Accepted Answer · 2021-07-31 11:40:02Z

What you are suggesting looks like a micro-optimization. The work done by the query is really for the aggregation part rather than in the filters.

It is possible that filtering the data only for the rows that will match would be a little bit faster, so you could try:

select created_date,
       count(*) filter ( where p_id=1 ) as count_tt_1,
       coalesce(sum(price) filter ( where p_id=1 ),0) as sum_tt_1,
       count(*) filter ( where tt_enum=2 and p_id=5 ) as count_tt_2,
       coalesce(sum(price) filter ( where tt_enum=2 and p_id=5 ), 0) as sum_tt_2,
       count(*) filter ( where tt_enum=3 and p_id=5 ) as count_tt_3,
       coalesce(sum(price) filter ( where tt_enum=3 and p_id=5 ), 0) as sum_tt_3
from my_table
where flgged = 0 and
      (tt_enum = 1 or
       tt_enum = 2 and p_id = 5 or
       tt_enum = 3 and p_id = 5
      )
group by created_date
order by created_date;

However, Postgres has a smart optimizer so even this might not help the performance.

Also note that this would filter out rows where are all the values are 0s, which might not be what you really want.

Collectives™ on Stack Overflow

PostgreSQL multiple aggregation with same filters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related