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 > 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.


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:


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


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.

  • Is this still the case with the new versions? I think it wouldn't really need a lookup for every query - it could be cached. – Ondra Žižka Apr 26 '18 at 13:01
  • 2
    Btw, with a NOT NULL column, the difference is big if you have a lot of rows. In our case with millions of rows, COUNT(*) is 3 times faster. (Postgres 9.4) – Ondra Žižka Apr 26 '18 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.