People often complain that count (*) in PostgreSQL is very slow.
In this article, I want to explore options to get you results as quickly as possible.
Why is count (*) so slow?
Most people have no problem understanding that the next query will be slow :
SELECT count(*)FROM /* complex query */;
After all, this is a complex query, and PostgreSQL has to compute the result before it knows how many strings it will contain.
But many people are shocked when they find out that the next request is slow :
SELECT count(*) FROM large_table;
However, if you think about it again, the above still applies: PostgreSQL has to compute the result set before it can count it. Since there is no "magic row counter" stored in the table (as in MySQL’s MyISAM), the only way to count rows is to look through them.
So count (*) usually does a sequential table scan, which can be quite expensive.
Is "*" in count (*) a problem?
The "*" in SELECT * FROM… applies to all columns. Consequently, many people think that using count (*) is inefficient, and you should write count (id) or count (1) instead.
But the "*" in count (*) is quite different, it just means "string" and is not expanded at all (in fact, it is an "aggregate with zero argument"). Writing count (1) or count (id) is actually slower than count (*), because it has to check whether the argument is NULL or not (count, like most aggregates, ignores NULL arguments).
So you won’t get anywhere by avoiding "*".
Using index only scan
It is tempting to scan a small index rather than the whole table to count the number of rows. However, this is not so easy in PostgreSQL because of its multiversion concurrency management strategy. Each version of a string ("tuple") contains information about which database snapshot it is visible to But this (redundant) information is not stored in indexes. Therefore, it is usually not enough to count records in an index, because PostgreSQL has to refer to a table record ("heap tuple") to make sure that the index record is visible.
To mitigate this problem, PostgreSQL has implemented a visibility map( visibility map ), a data structure that stores information about whether all tuples in a table block are visible to everyone or not.
If most table blocks are fully visible, then an index scan does not require frequent visits to a bunch of tuples to determine visibility. Such an index scan is called an "index only scan, " and it is often faster to scan the index for row counts.
Now it is the VACUUM that supports the visibility map, so make sure that autovacuum runs often enough if you want to use a small index to speed up count(*).
Using the summary table
I wrote above that PostgreSQL does not store the number of rows in a table.
Maintaining this line count represents a large overhead because this event occurs every time the data is modified and does not pay for itself. It would be a bad deal. Furthermore, since different queries can see different versions of strings, the counter must also be versioned.
But nothing prevents you from implementing such a string counter yourself.
Suppose you want to keep track of the number of rows in your mytable. You can do this as follows :
START TRANSACTION;CREATE TABLE mytable_count(c bigint);CREATE FUNCTION mytable_count() RETURNS triggerLANGUAGE plpgsql AS$$BEGINIF TG_OP = 'INSERT' THENUPDATE mytable_count SET c = c + 1;RETURN NEW;ELSIF TG_OP = 'DELETE' THENUPDATE mytable_count SET c = c - 1;RETURN OLD;ELSEUPDATE mytable_count SET c = 0;RETURN NULL;END IF;END;$$;CREATE CONSTRAINT TRIGGER mytable_count_modAFTER INSERT OR DELETE ON mytableDEFERRABLE INITIALLY DEFERREDFOR EACH ROW EXECUTE PROCEDURE mytable_count();-- TRUNCATE triggers must be FOR EACH STATEMENTCREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytableFOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();-- initialize the counter tableINSERT INTO mytable_countSELECT count(*) FROM mytable;COMMIT;
We do everything in one transaction, so that no data changes across concurrent transactions can be "lost" due to the ring condition.
This is guaranteed by the fact that the CREATE TRIGGER command locks the table in SHARE ROW EXCLUSIVE mode, which prevents all concurrent changes.
The downside is that all concurrent data modifications must wait until SELECT count(*) is executed.
This gives us a really fast alternative to count (*), but at the cost of slowing down all the data changes in the table. Using deferred constraint trigger ensures that blocking the row in mytable_count is as short as possible to improve concurrency.
Although this counter table may receive many updates, no danger "table bloat." no, because these will all be HOT updates.
You really need count(*)
Sometimes the best solution is to look for an alternative.
Often the approximation is good enough that you don’t need the exact number. In this case, you can use the estimation that PostgreSQL uses to schedule queries :
SELECT reltuples::bigintFROM pg_catalog.pg_classWHERE relname = 'mytable';
This value is updated by both autovacuum and autoanalyze, so it should never exceed 10%. You can decrease the autovacuum_analyze_scale_factor for this table so that autoanalyze runs there more often.
Estimate the number of query results
So far, we have investigated how to speed up table row counting.
But sometimes you need to know how many rows the SELECT statement will return without actually executing the query.
Obviously, the only way to get an accurate answer to this question is to run a query. But if the score is good enough, you can use the PostgreSQL optimizer to get it.
The following simple function uses dynamic SQL and EXPLAIN to get the execution plan for the query passed in as an argument, and returns an estimate of the number of lines :
CREATE FUNCTION row_estimator(query text) RETURNS bigintLANGUAGE plpgsql AS$$DECLAREplan jsonb;BEGINEXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;RETURN (plan-> 0-> 'Plan'-> > 'Plan Rows')::bigint;END;$$;
Do not use this function to process untrusted SQL instructions, as it is inherently vulnerable to SQL injection.