3 deleted 4 characters in body
source | link

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 ascendcodata.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.

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 ascendco.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.

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.

2 added 126 characters in body
source | link

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

[Edit] Ha! Found a query to the planner doesn't always work well enoughbug in my calling code just writing this up. For example, when I tryHere's how to call the row estimate distinct values this way:

select * from row_count_estimate('select * from data.scan');

Here is how not to call it:

select * from row_count_estimate('select count(*) from ascendco.assembly');

The second version is asking for an estimate of how many rows count(PG 11.4 or 11.5*), I always get back 1 will return. Maybe thatIt will change in PG 12 as there are more stats about distinct value distributions? No cluereturn 1, which isn't what we want to know.

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.

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 ascendco.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.

1
source | link

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.