Re: performance of sql and plpgsql functions

Julius Tuskenis <julius(dot)tuskenis(at)gmail(dot)com> writes:
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
>   SELECT
>     COALESCE(sum(mok_nepadengta), 0)
>   FROM
>     public.b_pardavimai
>     JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
>   WHERE
>     (pard_tipas = ANY('{1, 2, 6, 7}'))
>     AND (mok_saskaita = 7141968)

I believe that the SQL-language function executor always uses generic
plans for parameterized queries (which is bad, but nobody's gotten
round to improving it). So the above is a poor way of investigating
what will happen, because it corresponds to a custom plan for the
value 7141968. You should try something like

PREPARE p(integer) AS
SELECT COALESCE ...
... AND (mok_saskaita = $1);

SET plan_cache_mode TO force_generic_plan;

EXPLAIN ANALYZE EXECUTE p(7141968);

What I suspect is that the statistics for mok_saskaita are
highly skewed and so with a generic plan the planner will
not risk using a plan that depends on the parameter value
being infrequent, as the one you're showing does.

regards, tom lane