1

I have the following query to run, which takes 50-60ms (query plan here):

SELECT (SUM(added_vacancies) + (SELECT active_vacancies FROM report.vacancy_stats WHERE date = make_date(2021,06,01) AND company_id = 6886 LIMIT 1) - SUM(archived_vacancies)) as result
  FROM report.vacancy_stats
 WHERE company_id = 6886
   AND vacancy_stats.date >= (make_date(2021,06,01) + integer '1')::DATE
   AND vacancy_stats.date <= make_date(2021,09,25)
 LIMIT 1;

Now I've put it into a function like this; and suddenly, it takes at least 300ms to run:

CREATE OR REPLACE FUNCTION report.cp_active_vacancy_count_during_period(cid integer, date_start date, date_end date)
RETURNS bigint
LANGUAGE sql
STABLE LEAKPROOF PARALLEL SAFE
AS $function$
 SELECT (SUM(added_vacancies) + (SELECT active_vacancies FROM report.vacancy_stats WHERE date = date_start AND company_id = cid LIMIT 1) - SUM(archived_vacancies)) as result
   FROM report.vacancy_stats
  WHERE company_id = cid
    AND vacancy_stats.date >= (date_start + integer '1')::DATE
    AND vacancy_stats.date <= date_end
  LIMIT 1;
$function$;

What could be the reason for this huge difference, and how can I solve that issue?

Daniel Ziltener
  • 647
  • 1
  • 6
  • 21
  • Can you show the query plan for both executions? (the function one will probably be very empty). If able, you could try to get a query plan for the function by checking out this: https://dba.stackexchange.com/a/84496/129289 – JensV Sep 26 '21 at 12:53
  • In the first step remove `IMMUTABLE` as the function is only stable. – klin Sep 26 '21 at 12:55
  • I replaced it with `STABLE LEAKPROOF PARALLEL SAFE` now. I tried to get `EXPLAIN ANALYZE` to work with the function body, but I didn't manage to get it to work - `LOAD 'auto_explain'; SET auto_explain.log_nested_statements = ON;` didn't do anything. – Daniel Ziltener Sep 26 '21 at 13:04
  • 1
    You forgot `SET auto_explain.log_min_duration = 0;`. – Laurenz Albe Sep 26 '21 at 15:22
  • Show us the query plan for the first query regardless. It might help to guess what the problem is – JensV Sep 26 '21 at 16:16
  • How are you using the function in the query? – Adrian Klaver Sep 26 '21 at 16:21
  • Okay, thanks, Where does the query plan end up then after that? It is not in the log file, and also isn't returned as result from running explain analyze (that one just returns the normal "empty" query plan for functions) – Daniel Ziltener Sep 26 '21 at 16:32
  • I added the query plan for the first query in the question. I'm calling the function like `SELECT report.cp_active_vacancy_count_during_period(6886, make_date(2021,06,01), make_date(2021,09,25));` – Daniel Ziltener Sep 26 '21 at 16:33

0 Answers0