top of page
  • Alon Spiegel

Dispelling the Myth: count(*) vs. count(1) Efficiency in Modern Databases

As an experienced DBA, I often encounter the belief that SELECT COUNT(1) is faster than SELECT COUNT(*). While this may have held true in the early '90s with Oracle 6 and 7, advancements in database optimizers have debunked this notion. To illustrate, I conducted tests using PostgreSQL 15 on a substantial partition table (662 GB), with each day contributing around 2.5 GB. In the first two examples, utilizing COUNT(*) and COUNT(1) respectively yielded identical execution plans, both opting for a full table scan.




Moving forward, the third and fourth examples involve selecting directly from the underlying partition, rendering the filter stage redundant. However, the sequential scan stage remains consistent, highlighting that PostgreSQL treats both SELECT COUNT(1) and SELECT COUNT(*) equivalently in modern scenarios.



If you find yourselves struggling with such dilemmas contact us and one of our database specialists will help you.

Comments


bottom of page