PostgreSQL: Count Query Takes Too Much Time

I have some problems with my query - it takes too much time (2636124 ms!): SELECT COUNT(*) AS '__count' FROM 'dictionary_dictionary' WHERE NOT ('dictionary_dictionary'.'id' IN

Solution 1:

Try adding DISTINCT keyword, which should narrow the checked subset of ids:

SELECT COUNT(*) AS "__count" 
FROM "dictionary_dictionary" 
WHERE NOT ("dictionary_dictionary"."id" IN (SELECT distinct U1."word_id" AS Col1
                                            FROM "dictionary_frequencydata" U1 
                                            WHERE U1."user_id" = 1));

Solution 2:

In this case, your query should be a lot faster if you re write it like below, as both the subqueries are fast. The final result is equivalent to the query generated by django.

It seems the seq scan with filter operation on dictionary_dictionary is quite expensive, but the plain seq scan is very fast. I'm not sure why this is so.

SELECT 
tot - excl
from (select count(*) tot
      from dictionary_dictionary) t1
, (select count(DISTINCT d.id) excl
   from dictionary_dictionary d 
   join dictionary_frequencydata f
     on d.id = f.word_id 
   where f.user_id = 1 ) t2

If rows are infrequently inserted into dictionary_dictionary, then the count should not change that often. then it will be more efficient to cache the result of select count(*) from dictionary_dictionary and subtract the count of excluded ids from it. When rows are inserted / removed from dictionary_dictionary, the cache would need to be updated. It is possible to maintain this cache automatically using triggers on insert & delete from dictoinary_dictionary