How to count SQL databases quickly in PostgreSQL and MySQL

Counting records in a database table seems like it should be a fast task. Since the database software is responsible for inserting and removing records from a table, it should just know how many records there are in the table, right? However, with database tools like MySQL and PostgreSQL, that’s not the case. In this post, you’ll learn some techniques to manually fast count records with these tools.

Counting rows with COUNT(*)

If you have ever used MySQL with the MyISAM engine, the following query is incredibly fast:

The MyISAM engine maintains a count of all rows in a table, making counts in MySQL/MyISAM spectacularly fast.

However, if you've done counts with  InnoDB, another popular MySQL storage engine, or with a PostgreSQL table, then you know a count query takes much longer.

Here's what that query plan, on a PostgreSQL database, looks like for a database with about 4.6 million rows:

2.5 seconds.

That's a long time to wait to get a row count.

Counting rows in MySQL/InnoDB and PostgreSQL

Unlike MySQL/MyISAM, both MySQL/InnoDB and PostgreSQL use Multiversion Concurrency Control for counting. This means the database keeps information about old rows to better support concurrency and rollback features. Essentially, each unique transaction may have slightly different data from other transactions processed at the same time.

Because the state of every table is unique to each transaction with Multiversion Concurrency Control, there cannot be one single true count of rows in the database table like there is with a MySQL/MyISAM table. Consequentially, when one does a count, the database engine has to sequentially scan the table to determine how many rows are in the current transaction's view of reality for that table, at that time.

Count(1) vs Count(*)

As a side note, you may have heard that it's faster to do a count(1) than a count(*), because of the assumption that the * requires the database to access the whole row. That’s not true for PostgreSQL or MySQL and the count(1) version is slightly slower on average.

This is because PostgreSQL has optimized count(*) as a special case that includes no arguments. The count(1) includes an argument and has to check each row to validate that 1 is still not null. That adds a small but measurable overhead, particularly with very large tables.

Counting faster with PostgreSQL and MySQL/InnoDB

Slow counting is problematic if your app needs to know how many rows are in a table, or how many rows a query would return, without actually running the whole query.

Even though the database engines differ, the solutions to this problem for both PostgreSQL and MySQL/InnoDB are similar.

How to count SQL rows faster by leveraging indexes

This basic query for counting is slow with PostgreSQL and MySQL/InnoDB:

There is another way to think about count queries, though. Consider a table with a primary key of id. The above query could also be written as:

This is functionally equivalent to a basic count query and performs similarly to the basic query with an identical EXPLAIN plan on a PostgreSQL database. There's nothing to be gained there. However, consider a more involved count that is looking for the number of distinct server IDs in a table.

The EXPLAIN plan for this is pretty predictable:

This is very slow on this table, which contains about 4.6 million rows. You can see in the above plan the line, Seq Scan on telemetries, which indicates that the database still had to do a sequential scan of the table in order to count those distinct IDs. However, what happens if we rewrite it according to the prior template?

This query returns the same results as the prior example, but consider the plan that the query planner generates for it:

It is 10 times faster! The magic is in the following line: Index Only Scan using. The database engine was able to determine that it could leverage an index to find all of the distinct IDs. Then it just has to scan that much smaller set in order to count them.

If you need an exact count on a regular basis, you should be doing your query in the most efficient way possible. As the previous example shows, sometimes you can use the EXPLAIN ANALYZE capability of both PostgreSQL and MySQL to help you optimize something so simple as a count. If you can leverage an index to reduce the total number of rows that you are scanning, then you may be able to get an exact count quickly enough.

Sometimes this approach isn’t optimal, though, and other strategies are needed.

How to count ahead of time

If you need to quickly get an exact count, one option is to pay the time cost for this data in small pieces, ahead of time, by using triggers and functions to maintain a MySQL/MyISAM-like count that is kept up to date at all times.

With this approach, you create a table that stores row counts for other tables, then use triggers to update the row count on every insert or delete.

The following PostgreSQL example uses a trigger and a PL/pgSQL function in 3 steps:

1. First, you need to create a table that will store the count:

2. Next, create a function that will do the counting:

3. Finally, seed the table will the current count of the table you are counting, and then apply the trigger to your table. Replace my_table in the code snippet with the name of the table you’re counting.

Now you can query the current count from the row_count table:

The syntax will be a bit different, but you can apply the same concept to MySQL/InnoDB.

There's a cost for each insert and delete to maintain this count. Amortized over the life of your data, this extra cost will be insignificant if the data doesn't change much, but could add up in a table that has a lot of churn.

Relying on estimates for SQL count

You can also forgo accuracy for speed and rely on estimates.

The PostgreSQL and MySQL/InnoDB engines already maintain estimates of table size. In PostgreSQL, this information is stored in a table called pg_class.In MySQL/InnoDB, it’s stored in a table called information_schema. You can get an estimate of the total table size by querying the appropriate table. 

Here's how to estimate count with PostgreSQL:

And here’s how to do the same with MySQL:

This count will rarely be accurate, but if a ballpark number is sufficient, this query is very fast.

Other estimate options in MySQL

MySQL has another table called information_schema.innodb_sys_tablestats that also has a num_rows field. Here’s the documentation on this field:

The current estimated number of rows in the table. Updated after each DML operation. The value could be imprecise if uncommitted transactions are inserting into or deleting from the table.

So this is also an estimated number of rows and should return similar results as the previous example.

Other estimate options in PostgreSQL

For PostgreSQL, there is another option. The PostgreSQL query planner keeps an estimate of both the number of rows in a table (the reltuples field of pg_class) and of the size, in pages, of the table (the relpages field of pg_class).

You can query both the current size of the table on disk, in bytes as well as the size in bytes of each block.

If we assume that the estimated number of rows in pg_class divided by the estimated number of pages (blocks) in pg_class provides a relatively accurate number of rows per page of storage, we can then multiply that by the actual number of pages that are in use.

This is calculated by dividing the actual bytes in use by the block size. The number should be more accurate than the PostgreSQL planner's estimate. It's probably easier to understand this as code:

This produces a surprisingly accurate estimate of the table size, in rows. This is actually what the PostgreSQL query planner does to produce accurate estimates of row counts.

Estimating row count for arbitrary queries

The previous technique is great for quickly generating a reliable estimate of the row size of an entire table. However, it’s not helpful if you need to estimate the size of the return set of an arbitrary query. Fortunately, there is a similar estimation strategy that can be employed with any query.

When you use the EXPLAIN command to analyze a query, the query planner reports an estimated row count. 

A stored procedure can run any other command or procedure, including an EXPLAIN, and it can extract data from what the procedure returns. You can build a count estimator for queries by leveraging the query planner to do the heavy lifting, then scraping the data from it.

Here’s an example that I use in a working product:

This iterates over the output of an EXPLAIN, returning the number following the first rows= that it finds in the output, and it’s fast. The query planner usually develops a plan in a fraction of a millisecond, even on slower hardware installations.

So what is the best way to fast count?

If you want an exact count, there's no magic to make it fast. The best you can do is write the most efficient query possible. To understand the most efficient query, you’ll need to clarify your desired outcome.

Pros and cons of an accurate count

If you need an accurate count that’s fast, you can store a combination of procedures and triggers to keep track of the count. This adds operational overhead to inserts and deletes, however, and may make this approach too slow for a busy database.

Pros and cons of estimated counts

The only other option is to accept estimates. There are a variety of approaches, such as querying the same data that the query planner uses to make its estimates, using the query planner, and then scraping, via a stored procedure, the query planner's estimate of row size.

Fast observability with New Relic

For deeper insights into how your changes to your row-counting strategies and your database as a whole are performing, New Relic offers quickstarts for both PostgreSQL and for MySQL. These quickstarts let you quickly instrument your database, giving you immediate observability of performance, which you can then use for further optimization and troubleshooting.