PostgreSQL count estimate

Counting records in a database in tables that store millions of entries using the COUNT function can be time-consuming and resource demanding, as we can see in the attached analysis.

(
  sql = "EXPLAIN ANALYZE SELECT COUNT(*) FROM locations;"
  
  Ecto.Adapters.SQL.query!(Repo, sql)
)

Aggregate
(cost=323.03..323.04 rows=1 width=8)
(actual time=0.723..0.724 rows=1 loops=1)
  -> Index Only Scan using locations_pkey on locations 
(cost=0.28..319.21 rows=1529 width=0)
(actual time=0.061..0.574 rows=1536 loops=1)

Heap Fetches: 75
Planning Time: 0.128 ms
Execution Time: 0.756 ms

There is a better solution, as long as we do not need precise calculations, PostgreSQL allows us to estimate the number of records which is more efficient and faster.

(
    sql = """
    EXPLAIN ANALYZE
  SELECT reltuples::bigint
  FROM pg_catalog.pg_class
  WHERE relname = 'locations';
  """
  
  Ecto.Adapters.SQL.query!(Repo, sql)
)

Index Scan using pg_class_relname_nsp_index on pg_class 
(cost=0.28..8.30 rows=1 width=8) 
(actual time=0.120..0.121 rows=1 loops=1)

Index Cond: (relname = 'locations'::name)
Planning Time: 0.250 ms
Execution Time: 0.140 ms