is it possible to make the postgresql estimate the rows by condition

Question

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$
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
$function$
;

Answer 1

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.

Answer 2

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