First, a remark: your query could be written simpler as

    SELECT count(DISTINCT assembly_id) FROM assembly_prods;

Also, your statistics query is wrong, because `n_distict` can also be negative. You should query:

```
SELECT CASE WHEN s.n_distinct < 0
            THEN - s.n_distinct * t.reltuples
            ELSE s.n_distinct
       END AS n_distinct
FROM pg_class t
   JOIN pg_namespace n ON n.oid = t.relnamespace
   JOIN pg_stats s ON t.relname = s.tablename
                      AND n.nspname = s.schemaname
WHERE s.schemaname = 'public'
  AND s.tablename = 'assembly_prods'
  AND s.attname = 'assembly_id';
```

For a simple query like that, the statistics should contain a good estimate.

If the estimates are off, try to `ANALYZE` the table.

If that improves the results, see that the table is analyzed more often by configuring

    ALTER TABLE assembly_prods SET (autovacuum_analyze_scale_factor = 0.05);

It is also possible to set `autovacuum_analyze_scale_factor` to 0 and raise `autovacuum_analyze_threshold` to the daily change rate for the table.

If `ANALYZE` alone does not improve the estimate, increase the size of the sample:

    ALTER TABLE assembly_prods ALTER assembly_id SET STATISTICS 1000;

A new `ANALYZE` should now produce better estimates.

Getting good `n_distinct` estimates for more complicated queries becomes increasingly more difficult. Sometimes [extended statistics][1] will improve the estimate considerably.


  [1]: https://www.postgresql.org/docs/current/sql-createstatistics.html