Is COUNT(1) or COUNT(*) better for PostgreSQL

Question

I've seen answers to this question for other databases (MySQL, SQL Server, etc.) but not for PostgreSQL. So, is COUNT(1) or COUNT(*) faster/better for selecting the row count of a table?

Answer 1

Benchmarking the difference

The last time I've benchmarked the difference between COUNT(*) and COUNT(1) for PostgreSQL 11.3, I've found that COUNT(*) was about 10% faster. The explanation by Vik Fearing at the time has been that the constant expression 1 (or at least its nullability) is being evaluated for the entire count loop. I haven't checked whether this has been fixed in PostgreSQL 14.

Don't worry about this in real world queries

However, you shouldn't worry about such a performance difference. The difference of 10% was measurable in a benchmark, but I doubt you can consistently measure such a difference in an ordinary query. Also, ideally, all SQL vendors optimise the two things in the same way, given that 1 is a constant expression, and thus can be eliminated. As mentioned in the above article, I couldn't find any difference in any other RDBMS that I've tested (MySQL, Oracle, SQL Server), and I wouldn't expect there to be any difference.

Answer 2

Just as an additional data point, it looks like PostgreSQL does interpret different COUNT statements in a different way. I have a table with 40 million records running on PostgreSQL 14.3 and these are the results that I get:

select count(1) from "DATA" p -- 2m3s, 2m3s, 2m17s

select count(*) from "DATA" p -- 4m39s, 4m39s, 3m16s

select count(distinct "ID") from "DATA" p -- 4m39, 4m46s

select count("ID") from "DATA" p -- 2m31s, 2m25s

This seems to show that COUNT(1) is a better option in general than COUNT(*). The timing has been taken a few times, one after the other so it should be pretty accurate.