Return to Revisions

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.