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?
2 Answers
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.
4 Comments
CircuitSacul
OK. I suppose at some point I should just test it, since a lot can change from version 11 to 14.
"I haven't checked whether this has been fixed in PostgreSQL 14" - I don't think so. The Postgres devs don't really think it's worth the effort: postgresql.org/message-id/flat/…
Lukas Eder
@a_horse_with_no_name: Thanks for digging it up. Someone might still change their opinion in the future, though...
wildplasser
I don't believe that the difference will be measurable if all the records must come from disk.
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.
1 Comment
Alex O
These results run contrary to the other answers.
count(1)is faster thancount(*). And in Postgrescount(*)is actually slightly faster: blog.jooq.org/whats-faster-count-or-count1count(*)is the 'standard' way. Don't confuse people.COUNT(*)to count rows in a result. The devs will normally optimize the documented scenario(s) before any others. Postgres doc: postgresql.org/docs/current/functions-aggregate.htmlcount(*). See: dba.stackexchange.com/a/309924/3684 Andcount(1)is utterly pointless to begin with, in any RDBMS (that I know of). Generally replace that withcount(*).