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.
[Edit] Ha! Found a bug in my calling code just writing this up. Here's how to call the row estimate:
select * from row_count_estimate('select * from data.scan');
Here is how not to call it:
select * from row_count_estimate('select count(*) from data.assembly');
The second version is asking for an estimate of how many rows count(*) will return. It will return 1, which isn't what we want to know.