count(*): The Most Efficient Way to Count Rows in Your Table

Have you ever heard people say that select(*) is inefficient because it pulls back every column in the table? Well, they're right! But did you know that count(*) is actually the most efficient way to count all of the rows in your table?

Why is count(*) efficient?

When you use count(*), the database engine will use an index to count the rows. The index that it uses is called the smallest secondary index available. This means that the database engine will only have to read a small amount of data to count the rows, which makes the query very efficient.

What if I want to count a specific column?

If you want to count a specific column, you can use count(column_name). However, this will not be as efficient as count(*), because the database engine will have to read all of the rows in the table to count the number of rows that contain a non-null value for the specified column.

Proof of Concept

To prove that count star is the most efficient way to count rows, let's run a quick experiment. We'll create a table with 1 million rows and then use both count(*) and count(id) to count the number of rows.

-- Used PostgreSQL v15
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  birthday DATE NOT NULL
);

INSERT INTO my_table (name, birthday)
SELECT
  'Name' || n,
  '2000-01-01'::DATE + n * '1 day'::INTERVAL
FROM generate_series(1, 1000000) AS n;

-- Query #1
select count(*) from my_table;

-- Query #2
select count(id) from my_table;

The results of the experiment are as follows:

As you can see, the results of both queries are the same. However, the select count(*) query was much faster. This is because the database engine was able to use an index to count the rows, while the select count(id) query had to read all of the rows in the table.

Conclusion

If you need to count the rows in a table, the most efficient way to do it is to use count(*). This will ensure that the query is as fast as possible, even if the table contains a large number of rows.