postgresql - Why postgres function so slow but single query is fast? -

i have function 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) $$ 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'' ''probation''     when contract_group.code in (''3'', ''4'', ''5'') ''official''     when contract_group.code = ''2'' ''collaborator'' end :: character varying "contracttype",     ''create'' :: character varying                                               "signal"       hr_employee     inner join resource_resource rr       on rr.id = he.resource_id     inner join hr_contract contract       on contract.employee_id = he.id , contract.date_start = (       select max(date_start) "date_start"       hr_contract cc       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       contract_group.code = ''1''       ,     ($1 null or $1 = '''' or rr.code = $1 or      he.login = $1)     , (       (he.join_date between $2 , $3)       or (he.join_date not null , (contract.date_start between $2 , $3))       or (he.create_date between $2 , $3 , he.create_date > he.join_date)     )     , rr.active = true 'using accountorempcode, fromdate, todate ; end; $$ language plpgsql; 

it took 37 second execute

select *  get_probation_contract('', '2014-01-01', '2014-06-01'); 

when 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' 'probation'       when contract_group.code in ('3', '4', '5') 'official'       when contract_group.code = '2' 'collaborator' end :: character varying "contracttype",       'create' :: character varying                                               "signal"           hr_employee       inner join resource_resource rr         on rr.id = he.resource_id       inner join hr_contract contract         on contract.employee_id = he.id , contract.date_start = (         select max(date_start) "date_start"         hr_contract         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           contract_group.code = '1' , (       (he.join_date between '2014-01-01' , '2014-06-01')       or (he.join_date not null , (contract.date_start between '2014-01-01' , '2014-01-06'))       or (he.create_date between '2014-01-01' , '2014-01-06' , he.create_date > he.join_date)     )     , rr.active = true 

it take 5 second complete

how optimize function above. , why function slow single query use execute 'select ...' in function.

indexing in field id each table.

possible reason blind optimization prepared statements (embedded sql). little bit better in new postgresql releases, although can issue there too. execution plan in embedded sql in pl/pgsql reused more calls - , optimized more value (not used value). difference can make big slowdowns.

then can use dynamic sql - execute statement. dynamic sql uses once executed plans , uses real parameters. should fix issue.

example of embedded sql reused prepared plans.

create or replace function fx1(_surname text) returns int $$ begin   return (select count(*) people surname = _surname) end; 

example dynamic sql:

create or replace function fx2(_surname text) returns int $$ declare result int; begin   execute 'select count(*) people surname = $1' result       using _surname;   return result; end; $$ language plpgsql; 

second function can faster if dataset contains terrible surname - common plan seq scan, lot of time ask other surname, , want use index scan. dynamical query parametrization (like ($1 null or $1 = '''' or rr.code = $1 or) has same effect.