Re: count(*) vs count(id)

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Matt Zagrabelny <mzagrabe(at)d(dot)umn(dot)edu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Rob Sargent <robjsargent(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: count(*) vs count(id)
Date: 2021-02-02 08:31:22
Message-ID: fe5b3e56cf75094864ac0d05eadefacc42d572c0.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, 2021-02-01 at 19:14 -0600, Matt Zagrabelny wrote:
> > > What is count(*) counting then? I thought it was rows.
> >
> > Yeah, but count(id) only counts rows where id isn't null.
>
> I guess I'm still not understanding it...
>
> I don't have any rows where id is null:

Then the *result* of count(*) and count(id) will be the same.

The asterisk in count(*) is misleading. Different from any other
programming language that I know, the SQL standard has decided that
you cannot have an aggregate function without arguments. You have
to use the asterisk in that case.

So count(*) really is count(), that is, it counts one for every
row that it finds, no matter what the row contains.

But count(id) includes a check: if "id IS NULL", it is not counted.

If that condition is satisfied for all "id"s, you end up with
the same count. But count(id) is more expensive, because it
will perform this unnecessary NULLness check for each row.

In short: use count(*) if you want to count rows, and use
count(x) if you want to count all rows where x IS NOT NULL.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2021-02-02 08:35:23 Re: permission denied for large object 200936761
Previous Message Laurenz Albe 2021-02-02 08:22:58 Re: vacuum is time consuming