Why postgres function so slow but single query is fast?

Question

I have function to get employee in 'Create' status.

    CREATE OR REPLACE FUNCTION get_probation_contract(AccountOrEmpcode TEXT, FromDate DATE,
                                                  ToDate           DATE)
  RETURNS TABLE("EmpId" INTEGER, "EmpCode" CHARACTER VARYING,
  "DomainAccount" CHARACTER VARYING, "JoinDate" DATE,
  "ContractTypeCode" CHARACTER VARYING, "ContractTypeName" CHARACTER VARYING,
  "ContractFrom" DATE, "ContractTo" DATE, "ContractType" CHARACTER VARYING,
  "Signal" CHARACTER VARYING) AS $$
BEGIN
  RETURN QUERY
  EXECUTE 'SELECT
    he.id                                                                       "EmpId",
    rr.code                                                                     "EmpCode",
    he.login                                                                    "DomainAccount",
    he.join_date                                                                "JoinDate",
    contract_type.code                                                          "ContractTypeCode",
    contract_type.name                                                          "ContractTypeName",
    contract.date_start                                                         "ContractFrom",
    contract.date_end                                                           "ContractTo",
    CASE WHEN contract_group.code = ''1'' THEN ''Probation''
    WHEN contract_group.code IN (''3'', ''4'', ''5'') THEN ''Official''
    WHEN contract_group.code = ''2'' THEN ''Collaborator'' END :: CHARACTER VARYING "ContractType",
    ''CREATE'' :: CHARACTER VARYING                                               "Signal"
  FROM
    hr_employee he
    INNER JOIN resource_resource rr
      ON rr.id = he.resource_id
    INNER JOIN hr_contract contract
      ON contract.employee_id = he.id AND contract.date_start = (
      SELECT max(date_start) "date_start"
      FROM hr_contract cc
      WHERE cc.employee_id = contract.employee_id
    )
    INNER JOIN hr_contract_type contract_type
      ON contract_type.id = contract.type_id
    INNER JOIN hr_contract_type_group contract_group
      ON contract_group.id = contract_type.contract_type_group_id
  WHERE
    contract_group.code = ''1''


    AND
    ($1 IS NULL OR $1 = '''' OR rr.code = $1 OR
     he.login = $1)
    AND (
      (he.join_date BETWEEN $2 AND $3)
      OR (he.join_date IS NOT NULL AND (contract.date_start BETWEEN $2 AND $3))
      OR (he.create_date BETWEEN $2 AND $3 AND he.create_date > he.join_date)
    )
    AND rr.active = TRUE
'using AccountOrEmpcode, FromDate, ToDate ;
END;
$$ LANGUAGE plpgsql;

It took 37 second to execute

SELECT *
 FROM get_probation_contract('', '2014-01-01', '2014-06-01');

When I use single query

    SELECT
      he.id                                                                       "EmpId",
      rr.code                                                                     "EmpCode",
      he.login                                                                    "DomainAccount",
      he.join_date                                                                "JoinDate",
      contract_type.code                                                          "ContractTypeCode",
      contract_type.name                                                          "ContractTypeName",
      contract.date_start                                                         "ContractFrom",
      contract.date_end                                                           "ContractTo",
      CASE WHEN contract_group.code = '1' THEN 'Probation'
      WHEN contract_group.code IN ('3', '4', '5') THEN 'Official'
      WHEN contract_group.code = '2' THEN 'Collaborator' END :: CHARACTER VARYING "ContractType",
      'CREATE' :: CHARACTER VARYING                                               "Signal"
    FROM
      hr_employee he
      INNER JOIN resource_resource rr
        ON rr.id = he.resource_id
      INNER JOIN hr_contract contract
        ON contract.employee_id = he.id AND contract.date_start = (
        SELECT max(date_start) "date_start"
        FROM hr_contract
        WHERE employee_id = he.id
      )
      INNER JOIN hr_contract_type contract_type
        ON contract_type.id = contract.type_id
      INNER JOIN hr_contract_type_group contract_group
        ON contract_group.id = contract_type.contract_type_group_id
    WHERE
      contract_group.code = '1'
AND (
      (he.join_date BETWEEN '2014-01-01' AND '2014-06-01')
      OR (he.join_date IS NOT NULL AND (contract.date_start BETWEEN '2014-01-01' AND '2014-01-06'))
      OR (he.create_date BETWEEN '2014-01-01' AND '2014-01-06' AND he.create_date > he.join_date)
    )
    AND rr.active = TRUE

It take 5 second to complete

How to optimize the function above. and why function is slow than single query so much even I use execute 'select ...' in function.

Indexing in field id each table.

Answer 1

Possible reason is a blind optimization for prepared statements (embedded SQL). It is little bit better in new PostgreSQL releases, although it can be the issue there too. Execution plan in embedded SQL in PL/pgSQL is reused for more calls - and it is optimized for more often value (not for really used value). Sometimes this difference can make really big slowdowns.

Then you can use dynamic SQL - EXECUTE statement. Dynamic SQL uses only once executed plans and it uses real parameters. It should to fix this issue.

Example of embedded SQL with reused prepared plans.

CREATE OR REPLACE FUNCTION fx1(_surname text)
RETURNS int AS $$
BEGIN
  RETURN (SELECT count(*) FROM people WHERE surname = _surname)
END;

Example with dynamic SQL:

CREATE OR REPLACE FUNCTION fx2(_surname text)
RETURNS int AS $$
DECLARE result int;
BEGIN
  EXECUTE 'SELECT count(*) FROM people WHERE surname = $1' INTO result
      USING _surname;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Second function can be faster if your dataset contains some terrible often surname - then common plan will be seq scan, but lot of time you will ask some other surname, and you will want to use index scan. Dynamical query parametrization (like ($1 IS NULL OR $1 = '''' OR rr.code = $1 OR) has same effect.

Answer 2

Your queries are not the same.

The first one has

WHERE cc.employee_id = contract.employee_id

where the second one has:

WHERE employee_id = he.id

And also:

($1 IS NULL OR $1 = '''' OR rr.code = $1 OR
 he.login = $1)

Please test again with identical queries and identical values.