Fast COUNT(*) in PostgreSQL
One of the frequently-asked questions about PostgreSQL is “why is SELECT COUNT(*) FROM some_table doing a slow sequential scan?”
This has been asked repeatedly on mailing lists everywhere, and the common answer in the FAQ provides a fine explanation which I shall not repeat. There is some elaboration on slow counting.
Regrettably, the proposed alternative solutions aren’t always quite so fine. The one that is most typically pointed out is this one, Tracking the row count
How Tracking the row count works
The idea is fine, at least at first blush:
- Set up a table that captures row counts
CREATE TABLE rowcounts ( table_name text not null primary key, total_rows bigint);
- Initialize row counts for the desired tables
DELETE FROM rowcounts WHERE table_name = 'my_table'; INSERT INTO ROWCOUNTS (table_name, total_rows) SELECT 'my_table', count(*) from my_table;
- Establish trigger function on my_table which has the following logic
if tg_op = 'INSERT' then update rowcounts set total_rows = total_rows + 1 where table_name = 'my_table'; elsif tg_op = 'DELETE' then update rowcounts set total_rows = total_rows - 1 where table_name = 'my_table'; end if;
- If you want to know the size of my_table, then query
SELECT total_rows FROM rowcounts WHERE table_name = 'my_table';
The problem with this approach
On the face of it, it looks fine, but regrettably, it doesn’t work out happily under conditions of concurrency. If there are multiple connections trying to INSERT or DELETE on my_table
, concurrently, then all require an exclusive lock on the tuple in rowcounts
for my_table
, and there is a risk (heading towards unity) of:
- Deadlock, if different connections access data in incompatible orderings
- Lock contention, leading to delays
- If some of the connections are running in SERIALIZABLE mode, rollbacks due to inability to serialize this update
So, there is risk of delay, or, rather worse, that this counting process causes otherwise perfectly legitimate transactions to fail. Eek!
A non-locking solution
I suggest a different approach, which eliminates the locking problem, in that:
- The triggers are set up to only ever INSERT into the
rowcounts
- An asynchronous process does summarization, to shorten
rowcounts
- I’d be inclined to use a stored function to query
rowcounts
Table definition
CREATE TABLE rowcounts ( table_name text not null, total_rows bigint, id serial primary key); create index rc_by_table on rowcounts(table_name);
I add the id
column for the sake of nit-picking normalization, so that anyone that demands a primary key gets what they demand. I’d not be hugely uncomfortable with leaving it off.
Trigger strategy
The triggers have the following form:
if tg_op = 'INSERT' then insert into rowcounts(table_name,total_rows) values ('my_table',1); elsif tg_op = 'DELETE' then insert into rowcounts(table_name,total_rows) values ('my_table',-1); end if;
Note that since the triggers only ever INSERT into rowcounts, they no longer interact with one another in a way that would lead to locks or deadlocks.
Function to return row count
create or replace function row_count(i_table text) returns integer as $$ begin return sum(total_rows) from rowcounts where table_name = i_table; end $ language plpgsql;
It would be tempting to have this function itself do a “shortening” of the table, but, that would reintroduce into the application the locking that we were wanting to avoid. So DELETE/UPDATE
are still deferred.
Function to clean up row counts table
This function needs to be run once in a while to summarize the table contents.
create or replace function rowcount_cleanse() returns integer as $$ define prec record; begin for prec in select table_name, sum(total_rows) as sum, count(*) as count from rowcounts group by table_name loop if count > 1 then delete from rowcounts where table_name = prec.table_name; insert into rowcounts (table_name, total_rows) values (prec.table_name, prec.total_rows); end if; end loop; return 0; end $ language plpgsql;
Initializing rowcounts for a table that is already populated
Nothing has yet been mentioned that would cause an initial entry to go into rowcounts for an already-populated table.
create or replace function rowcount_new_table(i_table text) returns integer as $$ declare query text; begin delete from rowcounts where table_name = i_table; query := 'insert into rowcounts(table_name, total_rows) select ''|| i_table ||'', count(*) from ' || i_table || ';'; execute query; return total_rows from rowcounts where table_name = i_table; end $ language plpgsql;
If a table has already got data in it, then it’s necessary to populate rowcounts
with an initial count. Implementing such a function is straightforward, and is left as an exercise to the reader.
Further enhancements possible
It is possible to shift some of the maintenance back into the row_count()
function, if we do some exception handling.
create or replace function row_count(i_table text) returns integer as $$ declare prec record; begin begin lock table rowcounts nowait; select sum(total_rows) as sum, count(*) as count from rowcounts where table_name = i_table; if count > 1 then delete from rowcounts where table_name = i_table; insert into rowcounts (table_name, total_rows) values (prec.table_name, prec.total_rows); end if; return prec.total_rows; exception return sum(total_rows) from rowcounts where table_name = i_table; end; end $ language plpgsql;
This is more than a little risky, as, if this function wins the lock, it will block other processes that wish to access row counts until it’s done, this likely isn’t a worthwhile exercise.