Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

I got consistent results in my repeated tests with various versions over the last years:
count(*) is slightly faster 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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