CODEWITHSUNDEEP
sqlpostgresql
sarina
sarina

Reputation: 129

PostgreSQL order of addition/subtraction and aggregate functions

What is the order that each step of this query are executed in PostgreSQL?

SELECT SUM(field1)+SUM(field2)+SUM(field3)-SUM(field4);

I gather that the addition/subtraction occurs in the regular order of operations -- in straight left-right order. Do we know what order the different SUMs occur?

This is a subset of a long query (about 40 seconds for execution) and I think that the data in the fields may be changing in real time while the query is being executed.

Upvotes: 1

Views: 2756

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324741

zerkms's answer cuts to the gist of what you actually need to know, and is correct. Transaction isolation will make sure you're not affected by concurrency issues in this simple case (but note, it's not a magic solution to all concurrency).

To answer your question literally as written: they're actually interleaved.

If you write:

SELECT SUM(field1)+SUM(field2)+SUM(field3)-SUM(field4) FROM mytable;

then Pg reads mytable once. It reads each row and feeds the mytable.field1 to the first sum, mytable.field2 into the second sum, etc. Then it repeats for each row.

So all the aggregation is done in parallel (though not using multiple CPUs).

Upvotes: 1

zerkms
zerkms

Reputation: 255005

and I think that the data in the fields may be changing in real time while the query is being executed

PostgreSQL will ensure the data integrity thanks to transactions isolation.

So all the SUMs will be evaluated as if they were run at the very same moment regardless of how long it takes for the query to run.

References:

Upvotes: 2

Related Questions