pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSurprising benchmark count(1) vs. count(*)
6+ messages / 4 participants
[nested] [flat]
* Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 10:09 Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2019-09-19 10:09 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/
Is there a reason why count(*) seems to be faster?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 10:22 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2019-09-19 10:22 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 11:22 Thomas Kellerer <spam_eater@gmx.net>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2019-09-19 11:22 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 14:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-09-19 14:11 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 20:38 Adam Brusselback <adambrusselback@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Adam Brusselback @ 2019-09-19 20:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Surprising benchmark count(1) vs. count(*)
@ 2019-09-19 21:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Brusselback <adambrusselback@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2019-09-19 21:36 UTC (permalink / raw)
To: Adam Brusselback <adambrusselback@gmail.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-09-19 21:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-19 10:09 Surprising benchmark count(1) vs. count(*) Thomas Kellerer <spam_eater@gmx.net>
2019-09-19 10:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-09-19 11:22 ` Thomas Kellerer <spam_eater@gmx.net>
2019-09-19 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-19 20:38 ` Adam Brusselback <adambrusselback@gmail.com>
2019-09-19 21:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox