Re: performance of sql and plpgsql functions

Julius Tuskenis <julius.tuskenis@gmail.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