pgsql-hackers
❮
[PATCH] Replace COUNT(NULL) with '0'::bigint
- Jump to comment-1Junwang Zhao<zhjwpku@gmail.com>Jan 25, 2026, 6:49 AM UTCHi,
In [1], David Rowley noted that COUNT(NULL) can be replaced
with '0'::bigint. The change should be straightforward, and I came
up with the attached patch to implement it.
[1] https://www.postgresql.org/message-id/CAApHDvrde9DUpQ3DhPd3ia9tchVmhZqewfzxSYWmYFWVj%3DLPpg%40mail.gmail.com
--
Regards
Junwang Zhao- Jump to comment-1David Rowley<dgrowleyml@gmail.com>Jan 25, 2026, 12:13 PM UTCOn Sun, 25 Jan 2026 at 19:48, Junwang Zhao <zhjwpku@gmail.com> wrote:
In [1], David Rowley noted that COUNT(NULL) can be replaced
Coming up with the code to do this wasn't the problem. I already
with '0'::bigint. The change should be straightforward, and I came
up with the attached patch to implement it.
[1] https://www.postgresql.org/message-id/CAApHDvrde9DUpQ3DhPd3ia9tchVmhZqewfzxSYWmYFWVj%3DLPpg%40mail.gmail.com
posted it in the patch in [2]. The reason I didn't commit that part is
simply that I don't think anyone would ever write COUNT(NULL) in a
query. My opinion has not changed since I wrote [3]. The main reason I
even mentioned COUNT(NULL) is because I wanted the API to support
replacing the Aggref with some other Node type, and that was the only
example I could think of to test to ensure it worked. I had hoped that
someone might come up with some ideas to do that which might be more
applicable in the real world, rather than regurgitate ideas I'd
thought of but didn't think were useful.
Do you really feel like this is useful to anyone? or did you just
write the patch because you can?
Corey did mention in [4] that he is in favour, so maybe it's just me
who thinks it's useless... Perhaps someone else wants to commit it.
David
[2] https://postgr.es/m/CAApHDvppFVDdjpYrs%3DpwgCnp-jv-tneQyfu8rWM8ymHcuJOJYw%40mail.gmail.com
[3] https://postgr.es/m/CAApHDvppFVDdjpYrs%3DpwgCnp-jv-tneQyfu8rWM8ymHcuJOJYw%40mail.gmail.com
[4] https://postgr.es/m/CADkLM%3De2Rb%2Bs3TV3LgYx8O1z2Cs%2BX2FSoafd6%3DUzpPD7hiocfw%40mail.gmail.com- Jump to comment-1Tom Lane<tgl@sss.pgh.pa.us>Jan 25, 2026, 4:44 PM UTCDavid Rowley <dgrowleyml@gmail.com> writes:
On Sun, 25 Jan 2026 at 19:48, Junwang Zhao <zhjwpku@gmail.com> wrote:
In [1], David Rowley noted that COUNT(NULL) can be replaced
with '0'::bigint. The change should be straightforward, and I came
up with the attached patch to implement it.Coming up with the code to do this wasn't the problem. I already
Yeah, I agree that this should be so rare as to not be worth expending
posted it in the patch in [2]. The reason I didn't commit that part is
simply that I don't think anyone would ever write COUNT(NULL) in a
query.
planner cycles to check for, not to mention future code maintenance
costs. The other special cases we optimize COUNT() for are common
real-world usages, but not this.regards, tom lane- Jump to comment-1Junwang Zhao<zhjwpku@gmail.com>Jan 26, 2026, 2:42 AM UTCOn Mon, Jan 26, 2026 at 12:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
On Sun, 25 Jan 2026 at 19:48, Junwang Zhao <zhjwpku@gmail.com> wrote:
In [1], David Rowley noted that COUNT(NULL) can be replaced
with '0'::bigint. The change should be straightforward, and I came
up with the attached patch to implement it.Coming up with the code to do this wasn't the problem. I already
posted it in the patch in [2]. The reason I didn't commit that part is
simply that I don't think anyone would ever write COUNT(NULL) in a
query.
Agreed, good to know, thanks for the explanation.
Yeah, I agree that this should be so rare as to not be worth expending
planner cycles to check for, not to mention future code maintenance
costs. The other special cases we optimize COUNT() for are common
real-world usages, but not this.
--
regards, tom lane
Regards
Junwang Zhao
- Jump to comment-1Junwang Zhao<zhjwpku@gmail.com>Jan 25, 2026, 12:44 PM UTCOn Sun, Jan 25, 2026 at 8:13 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Sun, 25 Jan 2026 at 19:48, Junwang Zhao <zhjwpku@gmail.com> wrote:
In [1], David Rowley noted that COUNT(NULL) can be replaced
with '0'::bigint. The change should be straightforward, and I came
up with the attached patch to implement it.
[1] https://www.postgresql.org/message-id/CAApHDvrde9DUpQ3DhPd3ia9tchVmhZqewfzxSYWmYFWVj%3DLPpg%40mail.gmail.com
Ah, I didn't check the v2 details carefully.
Coming up with the code to do this wasn't the problem. I already
posted it in the patch in [2].The reason I didn't commit that part is
I did this because of Corey's comment "There is nothing faster than nothing"
simply that I don't think anyone would ever write COUNT(NULL) in a
query. My opinion has not changed since I wrote [3]. The main reason I
even mentioned COUNT(NULL) is because I wanted the API to support
replacing the Aggref with some other Node type, and that was the only
example I could think of to test to ensure it worked. I had hoped that
someone might come up with some ideas to do that which might be more
applicable in the real world, rather than regurgitate ideas I'd
thought of but didn't think were useful.
Do you really feel like this is useful to anyone? or did you just
write the patch because you can?
and your note "Which is leaving the door open for more aggressive
optimisations that someone might want to do, e.g. the mentioned
COUNT(NULL) replaced with '0'::bigint."
I wasn't following that context and only saw this thread recently,
so I may have misunderstood the point of the comment.
I agree that people are more likely to write COUNT(1) than
COUNT(NULL), so I'm fine with leaving COUNT(NULL) as is.
--
Corey did mention in [4] that he is in favour, so maybe it's just me
who thinks it's useless... Perhaps someone else wants to commit it.
David
[2] https://postgr.es/m/CAApHDvppFVDdjpYrs%3DpwgCnp-jv-tneQyfu8rWM8ymHcuJOJYw%40mail.gmail.com
[3] https://postgr.es/m/CAApHDvppFVDdjpYrs%3DpwgCnp-jv-tneQyfu8rWM8ymHcuJOJYw%40mail.gmail.com
[4] https://postgr.es/m/CADkLM%3De2Rb%2Bs3TV3LgYx8O1z2Cs%2BX2FSoafd6%3DUzpPD7hiocfw%40mail.gmail.com
Regards
Junwang Zhao