A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
1 of 3

You've solved your problem, that's great. Recently, I got turned on to the benefits of getting estimated counts rather than "perfect" counts, in cases where it's slow, despite any cleanup efforts you might take. Here's a function adapted ("stolen") from a fine blog post by Laurenz Albe:

DROP FUNCTION IF EXISTS api.row_count_estimate (text);

CREATE OR REPLACE FUNCTION api.row_count_estimate (query text)
 RETURNS bigint
AS $$
DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION api.row_count_estimate (text) OWNER TO user_bender;

And something similar for views:

DROP FUNCTION IF EXISTS api.view_count_estimate (regclass);

CREATE FUNCTION api.view_count_estimate(view_id regclass) RETURNS bigint 
AS $$
DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || pg_get_viewdef(view_id) INTO plan;
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION api.view_count_estimate (regclass) OWNER TO user_bender;

I've got nothing for materialized views. I've also found that this trick of passing a query to the planner doesn't always work well enough. For example, when I try to estimate distinct values this way (PG 11.4 or 11.5), I always get back 1. Maybe that will change in PG 12 as there are more stats about distinct value distributions? No clue.