Почему postgres работает так медленно, а один запрос - быстрый?

У меня есть функция, чтобы получить сотрудника в статусе "Создать".

    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;

Потребовалось 37 секунд, чтобы выполнить

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

Когда я использую один запрос

    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

Это займет 5 секунд, чтобы завершить

Как оптимизировать функцию выше. и почему функция работает медленнее, чем одиночный запрос, даже если я использую команду 'select...' в функции.

Индексирование в поле id каждой таблицы.

Источник

2 ответа

Возможная причина - слепая оптимизация для подготовленных операторов (встроенный SQL). Немного лучше в новых выпусках PostgreSQL, хотя это тоже может быть проблемой. План выполнения во встроенном SQL в PL / pgSQL повторно используется для большего количества вызовов - и оптимизируется для более частого значения (не для реально используемого значения). Иногда эта разница может привести к очень большим замедлениям.

Тогда вы можете использовать динамический SQL - EXECUTE заявление. Динамический SQL использует только один раз выполненные планы и использует реальные параметры. Следует исправить эту проблему.

Пример встроенного SQL с повторно использованными подготовленными планами.

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

Пример с динамическим 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;

Вторая функция может быть быстрее, если ваш набор данных содержит какую-то ужасную, часто фамилию - тогда общий план будет seq scan, но много времени вы будете спрашивать какую-то другую фамилию, и вы захотите использовать index scan, Динамическая параметризация запросов (например, ($1 IS NULL OR $1 = '''' OR rr.code = $1 OR) имеет тот же эффект.

Источник

Ваши запросы не совпадают.

Первый имеет

WHERE cc.employee_id = contract.employee_id

где второй имеет:

WHERE employee_id = he.id

А также:

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

Пожалуйста, проверьте снова с идентичными запросами и идентичными значениями.

Источник
Другие вопросы по тегам