Is count() the most efficient way to count the number of objects?

Question

I need to count the number of objects of a particular model in my database and display the number with a context processor for the user to view on every page.

Right now I'm simply doing Model.objects.count(). It works, but as there are now more than 400,000 objects in the db, it has noticeably slowed things down.

I'm running on my development server, so maybe once I push to dedicated servers this won't be a problem, but I'm not sure... I'm worried what will happen once we get into the millions or beyond. Any tips?

This depends more on the database engine. A `COUNT(*) FROM table` can be as fast as *O(1)* so that means that the number of elements do not matter: https://stackoverflow.com/a/5258011/67579 — Willem Van Onsem, Oct 17 '18 at 17:45
In postgresql count is O(N). Do you really need the exact number of rows for each request? — awesoon, Oct 17 '18 at 17:47
@soon Adding reference: http://www.postgresqltutorial.com/postgresql-count-function/. It's basically written in the first lines of text. `When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use the COUNT(*) function on a big table, the query will be slow. This is related to the PostgreSQL MVCC implementation.` — Anton vBR, Oct 17 '18 at 17:56
And furthermore... if an exact number is not needed you can do an estimate, see here: https://wiki.postgresql.org/wiki/Count_estimate — Anton vBR, Oct 17 '18 at 18:01

score 0 · Answer 1 · answered Oct 18 '18 at 07:24

If the estimate you get from pg_class.reltuples is good enough, go for it. That would be the simplest solution.

If you need exact numbers, you could do that with a trigger. The following example keeps a count of the table mytab in mytab_count:

CREATE TABLE mytab_count (
   c bigint NOT NULL
);  

-- make sure there cannot be more than one row
CREATE UNIQUE INDEX mytab_count_singleton
   ON mytab_count ((1));

INSERT INTO mytab_count
   SELECT count(*) FROM mytab;

CREATE OR REPLACE FUNCTION count_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   CASE TG_OP
      WHEN 'INSERT' THEN
         UPDATE mytab_count SET c = c + 1;
         RETURN NEW;
      WHEN 'DELETE' THEN
         UPDATE mytab_count SET c = c - 1;
         RETURN OLD;
   END CASE;
END;$$;

CREATE TRIGGER count_trig AFTER INSERT OR DELETE ON mytab
   FOR EACH ROW EXECUTE PROCEDURE count_trig();

Is count() the most efficient way to count the number of objects?

1 Answers1