count(*) vs count(id)

On 2/1/21 4:53 PM, Matt Zagrabelny wrote:

Greetings,

Is there a preferred method of counting rows?

count(*) vs count(field_name)

I have used count(*) for a long time and am hitting an inconsistency. At
least it is an inconsistency to me (I'm sure there is an explanation)...

INCORRECT OUTPUT (not desired results)
$ select calendar.entry, count(*)  from
generate_series('2020-01-01'::date, '2021-01-10'::date, '1
day'::interval) as calendar(entry) left join call_records on
calendar.entry = call_records.timestamp::date group by calendar.entry;
[...]
 2020-08-30 00:00:00-05 │     1
 2020-08-31 00:00:00-05 │     1
[...]

CORRECT OUTPUT (desired results)
$ select calendar.entry, count(id)  from
generate_series('2020-01-01'::date, '2021-01-10'::date, '1
day'::interval) as calendar(entry) left join call_records on
calendar.entry = call_records.timestamp::date group by calendar.entry;
[...]
 2020-08-30 00:00:00-05 │     0
 2020-08-31 00:00:00-05 │     0
[...]

What am I missing between count(*) and count(id)?

Thanks for any help!

-m

You got one null from count(*) likely.

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent <robjsargent@gmail.com> wrote:

[...]

You got one null from count(*) likely.

What is count(*) counting then? I thought it was rows.

-m

Matt Zagrabelny <mzagrabe@d.umn.edu> writes:

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent <robjsargent@gmail.com> wrote:

You got one null from count(*) likely.

What is count(*) counting then? I thought it was rows.

Yeah, but count(id) only counts rows where id isn't null.

regards, tom lane

On Mon, Feb 1, 2021 at 6:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Matt Zagrabelny <mzagrabe@d.umn.edu> writes:

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent <robjsargent@gmail.com>

wrote:

You got one null from count(*) likely.

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:

$ select count(*) from call_records where id is null;
count
═══════
0
(1 row)

Time: 0.834 ms
$

select count(id) from call_records where id is null;
count
═══════
0
(1 row)

Time: 0.673 ms

Which field is count(*) counting if it is counting nulls?

-m

On Mon, Feb 1, 2021 at 6:14 PM Matt Zagrabelny <mzagrabe@d.umn.edu> wrote:

Which field is count(*) counting if it is counting nulls?

count(id) excludes from the count rows where the id field is null. The
presence of a left join in your query is introducing a null here due to
there not being a related field, not because the id value in a table is
actually null.

David J.

At 2021-02-01T20:14:04-05:00, Matt Zagrabelny <mzagrabe@d.umn.edu> sent:

select count(id) from call_records where id is null;
count
═══════
0
(1 row)

Time: 0.673 ms

Which field is count(*) counting if it is counting nulls?

-m

What you're overlooking is that, at least to my reading of your original query, id would be null for any dates that do not have any corresponding call_records (because you used a left join).

Le mar. 2 févr. 2021 à 02:14, Matt Zagrabelny <mzagrabe@d.umn.edu> a écrit :

On Mon, Feb 1, 2021 at 6:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Matt Zagrabelny <mzagrabe@d.umn.edu> writes:

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent <robjsargent@gmail.com>

wrote:

You got one null from count(*) likely.

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:

$ select count(*) from call_records where id is null;
count
═══════
0
(1 row)

Time: 0.834 ms
$

select count(id) from call_records where id is null;
count
═══════
0
(1 row)

Time: 0.673 ms

Which field is count(*) counting if it is counting nulls?

You're doing a left join, so I guess there's no row where
call_records.timestamp::date = 2020-08-30. That would result with a NULL id.

Guillaume

On Mon, Feb 1, 2021 at 7:19 PM Guillaume Lelarge <guillaume@lelarge.info>
wrote:

You're doing a left join, so I guess there's no row where
call_records.timestamp::date = 2020-08-30. That would result with a NULL id.

Thanks for the excellent analysis everyone. I appreciate it!

Here is the documentation (for anyone reading the mailing list in the
future...)

count(*) bigint number of input rows
count(expression) any bigint number of input rows for which the value of
expression is not null

Have a great night (or equivalent for your TZ).

-m

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

Hello list

My English is not very good, so I pretend that through the examples you
understand what I intend to expose

-- Recreate the query that is supposedly wrong

select calendar.entry, count(*)
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
group by calendar.entry;

-- wrong???

entry | count
------------------------+-------
2020-08-20 00:00:00-05 | 1
2020-08-21 00:00:00-05 | 1
2020-08-22 00:00:00-05 | 1
2020-08-23 00:00:00-05 | 1
2020-08-24 00:00:00-05 | 1
2020-08-25 00:00:00-05 | 1
2020-08-26 00:00:00-05 | 1
2020-08-27 00:00:00-05 | 1
2020-08-28 00:00:00-05 | 2
2020-08-29 00:00:00-05 | 1
2020-08-30 00:00:00-05 | 1
2020-08-31 00:00:00-05 | 1
2020-09-01 00:00:00-05 | 1
2020-09-02 00:00:00-05 | 2
2020-09-03 00:00:00-05 | 1
2020-09-04 00:00:00-05 | 2
2020-09-05 00:00:00-05 | 1
2020-09-06 00:00:00-05 | 1
2020-09-07 00:00:00-05 | 1
2020-09-08 00:00:00-05 | 1
2020-09-09 00:00:00-05 | 1
2020-09-10 00:00:00-05 | 1
2020-09-11 00:00:00-05 | 1
2020-09-12 00:00:00-05 | 1
2020-09-13 00:00:00-05 | 1
2020-09-14 00:00:00-05 | 1
2020-09-15 00:00:00-05 | 1

-- In the count I will only consider the records of call_records

select calendar.entry, count(call_records.*)
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
group by calendar.entry;

--- perfect
         entry          | count
------------------------+-------
 2020-08-20 00:00:00-05 |     0
 2020-08-21 00:00:00-05 |     0
 2020-08-22 00:00:00-05 |     0
 2020-08-23 00:00:00-05 |     0
 2020-08-24 00:00:00-05 |     0
 2020-08-25 00:00:00-05 |     0
 2020-08-26 00:00:00-05 |     0
 2020-08-27 00:00:00-05 |     0
 2020-08-28 00:00:00-05 |     2
 2020-08-29 00:00:00-05 |     1
 2020-08-30 00:00:00-05 |     0
 2020-08-31 00:00:00-05 |     0
 2020-09-01 00:00:00-05 |     1
 2020-09-02 00:00:00-05 |     2
 2020-09-03 00:00:00-05 |     1
 2020-09-04 00:00:00-05 |     2
 2020-09-05 00:00:00-05 |     0
 2020-09-06 00:00:00-05 |     0
 2020-09-07 00:00:00-05 |     0
 2020-09-08 00:00:00-05 |     0
 2020-09-09 00:00:00-05 |     0
 2020-09-10 00:00:00-05 |     0
 2020-09-11 00:00:00-05 |     0
 2020-09-12 00:00:00-05 |     0
 2020-09-13 00:00:00-05 |     0
 2020-09-14 00:00:00-05 |     0
 2020-09-15 00:00:00-05 |     0

when placing * I want to bring all the join records between both tables and
when counting them of course there will be a row for the dates 2020-08-30
, 2020-08-31 so the call_records fields are null

select *
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date

entry | id | timestamp
------------------------+----+---------------------
2020-08-20 00:00:00-05 | |
2020-08-21 00:00:00-05 | |
2020-08-22 00:00:00-05 | |
2020-08-23 00:00:00-05 | |
2020-08-24 00:00:00-05 | |
2020-08-25 00:00:00-05 | |
2020-08-26 00:00:00-05 | |
2020-08-27 00:00:00-05 | |
2020-08-28 00:00:00-05 | 1 | 2020-08-28 09:44:11
2020-08-28 00:00:00-05 | 2 | 2020-08-28 10:44:11
2020-08-29 00:00:00-05 | 3 | 2020-08-29 11:44:11
2020-08-30 00:00:00-05 | |
2020-08-31 00:00:00-05 | |
2020-09-01 00:00:00-05 | 4 | 2020-09-01 02:44:11
2020-09-02 00:00:00-05 | 5 | 2020-09-02 03:44:11
2020-09-02 00:00:00-05 | 6 | 2020-09-02 04:44:11
2020-09-03 00:00:00-05 | 7 | 2020-09-03 05:44:11
2020-09-04 00:00:00-05 | 8 | 2020-09-04 06:44:11
2020-09-04 00:00:00-05 | 10 | 2020-09-04 07:44:11
2020-09-05 00:00:00-05 | |
2020-09-06 00:00:00-05 | |
2020-09-07 00:00:00-05 | |
2020-09-08 00:00:00-05 | |
2020-09-09 00:00:00-05 | |
2020-09-10 00:00:00-05 | |
2020-09-11 00:00:00-05 | |
2020-09-12 00:00:00-05 | |
2020-09-13 00:00:00-05 | |
2020-09-14 00:00:00-05 | |
2020-09-15 00:00:00-05 | |

--- counting

select entry, count(*)
from (
select *
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
) as u
group by entry

entry | count
------------------------+-------
2020-08-20 00:00:00-05 | 1
2020-08-21 00:00:00-05 | 1
2020-08-22 00:00:00-05 | 1
2020-08-23 00:00:00-05 | 1
2020-08-24 00:00:00-05 | 1
2020-08-25 00:00:00-05 | 1
2020-08-26 00:00:00-05 | 1
2020-08-27 00:00:00-05 | 1
2020-08-28 00:00:00-05 | 2
2020-08-29 00:00:00-05 | 1
2020-08-30 00:00:00-05 | 1
2020-08-31 00:00:00-05 | 1
2020-09-01 00:00:00-05 | 1
2020-09-02 00:00:00-05 | 2
2020-09-03 00:00:00-05 | 1
2020-09-04 00:00:00-05 | 2
2020-09-05 00:00:00-05 | 1
2020-09-06 00:00:00-05 | 1
2020-09-07 00:00:00-05 | 1
2020-09-08 00:00:00-05 | 1
2020-09-09 00:00:00-05 | 1
2020-09-10 00:00:00-05 | 1
2020-09-11 00:00:00-05 | 1
2020-09-12 00:00:00-05 | 1
2020-09-13 00:00:00-05 | 1
2020-09-14 00:00:00-05 | 1
2020-09-15 00:00:00-05 | 1

El mar, 2 de feb. de 2021 a la(s) 03:31, Laurenz Albe (
laurenz.albe@cybertec.at) escribió:

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

--
Cordialmente,

Ing. Hellmuth I. Vargas S.

I just ran a few practical tests on large (~14mil rows) tables that have
multiple indexes.

SELECT COUNT(id) forces PostgreSQL to use the primary key index.
SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to
be choosing one of smaller size which leads to less IO and hence returns
the result faster.

On Tue, Feb 2, 2021 at 3:45 PM Hellmuth Vargas <hivs77@gmail.com> wrote:

Am Wed, Feb 03, 2021 at 01:43:14AM -0500 schrieb Cherio:

I just ran a few practical tests on large (~14mil rows) tables that have
multiple indexes.

SELECT COUNT(id) forces PostgreSQL to use the primary key index.
SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to
be choosing one of smaller size which leads to less IO and hence returns
the result faster.

Would you mind throwing in a test for

select count(1) ...

?

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Wed, 2021-02-03 at 10:54 +0100, Karsten Hilbert wrote:

I just ran a few practical tests on large (~14mil rows) tables that have
multiple indexes.
SELECT COUNT(id) forces PostgreSQL to use the primary key index.
SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to
be choosing one of smaller size which leads to less IO and hence returns
the result faster.

Would you mind throwing in a test for

select count(1) ...

No need to test. That will be slightly slower because it repeatedly checks
if 1 is NULL or not.

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