CODEWITHSUNDEEP
sqlpostgresqlcount
Andre
Andre

Reputation: 439

Count Estimate on PostgreSQL

I was having troubles with a count query, it was too slow. Then i found an article talking about the Count Estimate on PostgreSQL. It uses the function below to estimate the amount of rows:

CREATE OR REPLACE FUNCTION public.count_estimate(query text)
  RETURNS integer AS
$BODY$
DECLARE
    rec   record;
    ROWS  INTEGER;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;

    RETURN ROWS;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.count_estimate(text)

And i use it like this:

SELECT count_estimate('SELECT * FROM table');

The query I pass to the function accepts conditions (=, >, <, LIKE, SIMILAR TO ...), but when I try to use this specific conditional ~ it returns me a wrong count.

Is there anyway to adapt this function to make it works with ~ in where clause?

Thank you

Upvotes: 2

Views: 2743

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

https://www.postgresql.org/docs/current/static/sql-explain.html

https://www.postgresql.org/docs/current/static/using-explain.html

the function you use just runs EXPLAIN and parses the output to show the expected number of rows. The only way to "adapt" the function for showing better results here - is adding ANALYZE after the EXPLAIN to get "actual" rows. But of course it will cost you the actual execution of the query you pass as argument, so it makes no sense to estimate the count before running the actual query, if you estimate after actually running it.

Upvotes: 2

Related Questions