Thread: Surprising benchmark count(1) vs. count(*)
https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ Is there a reason why count(*) seems to be faster?
On Thu, 2019-09-19 at 12:09 +0200, Thomas Kellerer wrote: > https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ > > Is there a reason why count(*) seems to be faster? "count(*)" is just the SQL standard's way of saying what you'd normally call "count()", that is, an aggregate without arguments. "count(1)" has to check if 1 IS NULL for each row, because NULL values are not counted. "count(*)" doesn't have to do that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe schrieb am 19.09.2019 um 12:22: >> https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ >> >> Is there a reason why count(*) seems to be faster? > > "count(*)" is just the SQL standard's way of saying what you'd > normally call "count()", that is, an aggregate without arguments. > > "count(1)" has to check if 1 IS NULL for each row, because NULL > values are not counted. "count(*)" doesn't have to do that. But 1 is a constant, why does it need to check it for each row?
Thomas Kellerer <spam_eater@gmx.net> writes:
> Laurenz Albe schrieb am 19.09.2019 um 12:22:
>> "count(1)" has to check if 1 IS NULL for each row, because NULL
>> values are not counted. "count(*)" doesn't have to do that.
> But 1 is a constant, why does it need to check it for each row?
[ shrug... ] There's no special optimization for that case.
And I can't say that it seems attractive to add one.
regards, tom lane
I will say I've seen count(1) in the wild a ton, as well as at my own company from developers who were used to it not making a difference.
There have been a couple queries in the hot path that I have had to changed from count(1) to count(*) as part of performance tuning, but in general it's not worth me worrying about. There are usually larger performance issues to track down in complex queries.
It would be nice if Postgres optimized this case though because it is really really common from what i've seen.
Thanks,
-Adam
Adam Brusselback <adambrusselback@gmail.com> writes:
> It would be nice if Postgres optimized this case though because it is
> really really common from what i've seen.
Since the introduction of the "planner support function" infrastructure,
it'd be possible to do this without it being a completely ugly kluge:
we could put the logic for it into a planner support function attached
to count(any). Currently planner support functions are only called for
regular functions, but we could certainly envision adding the ability to
do it for aggregates (and window functions too, why not).
I'm not particularly planning to do that myself, but if someone else
wants to write a patch, have at it.
regards, tom lane