I have an article table with more than 2000000 rows, because the count was so slow, I am using the estimate rows like this:

select count_estimate('select * from article');

this works for the article pagination, now I am facing a problem is that I want to add some conditions with the pagination query. For example, I need to filter the article using the channel id like this:

select count_estimate('select * from article where sub_source_id = 308');

this seems could not work. when I changed the channel id, the rows count did not changed. the nuber is not precisely. what should I do to get the rows count using filter? is it possible? This is the function:

CREATE OR REPLACE FUNCTION public.count_estimate(query text)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
    rec   record;
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
  • I think the answer given on SE applies.
    – mustaccio
    Jul 28, 2022 at 14:56

2 Answers 2


The estimate is based on data found in pg_stats. Specialized estimates will only be estimated for values given in the MCV list. All values not in the MCV list will get the same estimate as each other, as there is not information to distinguish them. You could try increasing default_statistics_target or per-column statistics if you need more accurate results.


Slightly elaborated from my comment...

Clever approach, though EXPLAIN relies on the currency and availability of statistics, which may be stale or lacking (e.g., some WHERE predicates are not estimatable).

To obtain the most accurate results, you'd have to parse the output of EXPLAIN ANALYZE, which might defeat the purpose of what you're trying to accomplish, as it would execute the plan (execute the query), as opposed to simply plan it.

Alternatively, you could attempt to ensure that ANALYZE is executed with sufficient frequency to capture statistics after activities affecting significant row counts (e.g., bulk inserts). This approach, though, still suffers from limitations in available statistics (e.g., function-based WHERE filters).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.