Postgres: count(*) vs count(id) I saw in the documentation the difference between count(*) and count(pk).  I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).

My question is about Postgres' internal optimizations. Is it smart enough to pick up that a SERIAL PRIMARY KEY is going to exist in every row and never be false and just count rows or will it do redundant predicate checks for each row?  I agree that this is probably too much of a pointless optimization but I'm just curious.

I took a look at the output of EXPLAIN and EXPLAIN VERBOSE for count(*), count(id) and count(id &gt; 50) to see if EXPLAIN mentioned checking the predicates in its output. It doesn't.
I got consistent results in my repeated tests with various versions over the last years:
count(*) is slightly faster than count(pk). It is also shorter and most of the time it better fits what is tested: the existence of a row.

Concerning:


  Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going
  to exist in every row and never be false


The only relevant thing is the NOT NULL constraint. The PRIMARY KEY is NOT NULL automatically, serial or never false are orthogonal to the question.

With count(col), if PostgreSQL was trying to be smart and check the system catalog whether a column was NOT NULL and fall back to an equivalent count(*), you'd still have one more look-up on a system table than with count(*).

As for EXPLAIN output, there is a hint:

EXPLAIN SELECT count(*) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=0) ...


EXPLAIN SELECT count(pk) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=4) ...

Meaning, count(col) is not converted to count(*), even if it's defined NOT NULL.