PostgreSQL – Will PostgreSQL Optimize Query with Same Aggregate Usage?

aggregatepostgresql

SQL would not let use aliases (s,d) in other columns, so, my query is:

SELECT
   employe,
   sum(salary + bonus) AS s,
   sum(debt) AS d,
   (sum(salary + bonus) - sum(debt)) AS total
      FROM test_table
      GROUP BY employe;

This query contain aggregate sum(salary + bonus) two times.

Will PostgreSQL optimize it (use result from first aggregate in second aggregate) or do sum again for the second aggregate?

Best Answer

The reason you can't use the aliases is that they don't exist during aggregation, renaming is performed after aggregation. I don't know the answer to your question, I guess the only way to find out for sure is to look at the plan. However, have you considered nesting your query?

SELECT employe, s, d, s-d as total
FROM (
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
) as t;

another option is to use a CTE:

WITH t as ( 
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
)
SELECT employe, s, d, s-d as total
FROM t;
Related Question