How to count distinct rows in PostgreSQL table faster?

Question

I am running query "SELECT COUNT(DISTINCT id) FROM table1", where table1 has millions of records. The query is too slow. How to make it faster?

Answer 1

COUNT (DISTINCT id) is very slow. You should try SELECT count(*) FROM (SELECT DISTINCT id FROM table1) AS foo. Check the execution time of the following queries:

select count (*) from (select DISTINCT person_id from observation) as foo;
  count
---------
 9561606
(1 row)

Time: 122688.628 ms

select count (person_id) from (select DISTINCT person_id from observation) as foo;
  count
---------
 9561606
(1 row)

Time: 130568.055 ms

select count (DISTINCT person_id) from observation;
  count
---------
 9561606
(1 row)

Time: 431907.274 ms