patternsqlMinor
Is using SUM() twice suboptimal?
Viewed 0 times
twicesuboptimalusingsum
Problem
I know I have to write
My question now is, whether or not this is suboptimal. As a programmer, this query looks like the DB will calculate the sum twice. Is that so, or should I rely on optimizations the DB engine will do for me?
Update: an explain of a comparable query:
SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise):SELECT id,
sum(hours) AS totalhours
FROM mytable
GROUP BY id
HAVING sum(hours) > 50;My question now is, whether or not this is suboptimal. As a programmer, this query looks like the DB will calculate the sum twice. Is that so, or should I rely on optimizations the DB engine will do for me?
Update: an explain of a comparable query:
postgres=> explain select sum(counttodo) from orderline group by orderlineid having sum(counttodo) > 100;
QUERY PLAN
--------------------------------------------------------------------
HashAggregate (cost=1.31..1.54 rows=18 width=8)
Filter: (sum(counttodo) > 100)
-> Seq Scan on orderline (cost=0.00..1.18 rows=18 width=8)
(3 rows)Solution
The sum is only computed once.
I verified this using
and then used a debugger to check how many times
I verified this using
create table mytable (id int, hours int);
insert into mytable values (1, 60);
select sum(hours) from mytable group by id having sum(hours) > 50;and then used a debugger to check how many times
int4_sum (the transition function behind the sum aggregate) was called: once.Code Snippets
create table mytable (id int, hours int);
insert into mytable values (1, 60);
select sum(hours) from mytable group by id having sum(hours) > 50;Context
StackExchange Database Administrators Q#43237, answer score: 3
Revisions (0)
No revisions yet.