postgres fast count - ghdrako/doc_snipets GitHub Wiki

• https://www.citusdata.com/blog/2016/10/12/count-performance/

ANALYZE  users;    -- Ensure stats are fresh
-- Use reltuples for estimate   
 SELECT  reltuples:: NUMERIC   FROM  pg_class  WHERE  relname= 'users' ;

You ran ANALYZE on the users table to update status and query fetched the estimated row count for the users table as reltuples (short for relation tuples) from the pg_class catalog view.

cout distinct values

• https://wiki.postgresql.org/wiki/Loose_indexscan

cout distinct values HyperLogLog

• https://github.com/citusdata/postgresql-hll

Estimated distinct values in a set is to leverage the HyperLogLog data structure. You can use that in PostgreSQL with postgresql-hll.The HLL data structure estimates the number of distinct values in a large set. HyperLogLog is described as a cardinality estimator.

SELECT    COUNT(*)  AS  total,   
          COUNT(*) FILTER ( WHERE   type  =  'Rider' )  AS  riders,   
          COUNT(*) FILTER ( WHERE   type  =  'Driver' )  AS  drivers   
  FROM  users;