One of the most common questions on IRC is "how to make SELECT count(*) FROM tbl; faster?" While it's not clear why an application would need to run this query repeatedly, it is possible to make it a bit faster. There are two common approaches:
The easiest way is to use a trigger to update a static row count in a separate table:
CREATE TABLE counttbl (rowcount int NOT NULL, CHECK (rowcount >= 0)); CREATE UNIQUE INDEX "counttbl_single_row" ON counttbl((1)); -- only allow one row in counttbl CREATE FUNCTION update_counttbl() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN UPDATE counttbl SET rowcount = rowcount + 1; ELSIF TG_OP = 'DELETE' THEN UPDATE counttbl SET rowcount = rowcount - 1; END IF; RETURN NULL; -- ignored in an AFTER trigger END; $$ language plpgsql; CREATE TRIGGER update_rowcount AFTER INSERT OR DELETE ON realtbl FOR EACH ROW EXECUTE PROCEDURE update_counttbl(); INSERT INTO counttbl SELECT count(*) FROM realtbl; -- populate the count table
This approach is fairly straightforward, but suffers from a huge problem: only one transaction can INSERT INTO or DELETE FROM the table at a time because UPDATE on counttbl locks the row exclusively (the lock is necessary though). With some additional trickery, we can avoid this and achieve better concurrency:
CREATE TABLE counttbl (rowcount int NOT NULL, CHECK (rowcount >= -1)); CREATE UNIQUE INDEX "counttbl_single_row" ON counttbl((1)) WHERE rowcount > 1; -- only allow one "sum" row CREATE FUNCTION update_counttbl() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO counttbl VALUES(1); ELSIF TG_OP = 'DELETE' THEN INSERT INTO counttbl VALUES(-1); END IF; RETURN NULL; -- ignored in an AFTER trigger END; $$ language plpgsql; CREATE TRIGGER update_rowcount AFTER INSERT OR DELETE ON realtbl FOR EACH ROW EXECUTE PROCEDURE update_counttbl(); INSERT INTO counttbl SELECT count(*) FROM realtbl; -- populate the count table
Now we can run concurrent INSERTs and DELETEs on realtbl because we don't need to UPDATE any rows. To actually find out the count you'd run SELECT sum(rowcount) FROM counttbl; This approach has two problems though: it's a bit slower to find out the count than in the first solution, and it requires you to periodically update the "sum" row.
As always, neither of these solutions work for every possible case. It's up to you to choose the one that works for your application.