Myujth

My coworker and I were discussing today which way is the faster and more efficient way to perform a count over a list of records.

Basically, the scenario is, when we want to fetch a list of records from a procedure, is it quicker/more efficient to calculate the total number of records from the procedure as we fetch the list of records in one query or is it better to fetch the list of records first and then call COUNT again on the same procedure (in 2 calls).

Which one of these examples is more efficient? Why?

Example 1

SELECT *, COUNT(1) OVER() AS total_rows FROM some_procedure();

This returns a total_rows column on every row along with the rest of the columns.

-or-

Example 2

SELECT * FROM some_procedure();
SELECT COUNT(*) FROM some_procedure();

This way requires 2 calls, but doesn't calculate a total_rows column.

Additionally, does the COUNT in Example 1 get calculated for every record or only once?

If the function returns only a few rows (compared to the number of rows in the table it reads from), I would count the returned rows:

with result as (
 select *
 from some_function()
)
select *, (select count(*) from result) as total_rows
from result;

Again, this only makes sense if the function does some heavy lifting that you only want to do once and the number of rows is reasonably small (how much that is depends on your server, anything in the "many thousands" is probably OK, anything like "millions of rows" is probably not going to be efficient.

There are a lot of "it depends", but as a general rule, I make the database do the work by doing a single call rather than multiple.

Single calls allow the DB to optimize, if possible.

With multiple calls, good deal of time is spent marshaling and unmarshaling parameters, network traffic, having the DB interpret the call, causing the interrupt back into the calling process to make the second call.

But, testing is the only way to know for sure.

The only way to know is to test.

Although there is overhead for multiple calls to the database, reading all the rows of a table can be quite expensive. Of course, if the table has 10 rows, then it is not expensive. A billion rows is a different matter.

Calculating count(*) in Postgres should require scanning all the rows -- and testing for locking (to handle against concurrent transactions). This is, unfortunately, rather expensive.

If you are reading all the rows, why not just count the ones that are returned?