Hi all,
I recently experienced a performance degradation in an operational system that I can't explain. I had a function wrapper for a aggregate query that was performing well using the expected indexes with the approximate structure as shown below.
create or replace function example_function(param1 int, param2 int) returns setof custom_type as
$$
select * from big_table where col1 = param1 and col2 = param2;
$$ language sql;
After creating two new indexes on this table to support a different use case during a migration, this unchanged function reduced in performance by several orders of magnitude. Running the query inside the function manually on the console however worked as expected and the query plan did not appear to have changed. On a hunch I changed the structure of the function to the structure below and immediately the query performance returned to the expected baseline.
create or replace function example_function(param1 int, param2 int) returns setof custom_type as
$$
BEGIN
return query execute format($query$
select * from big_table where col1 = %1$L and col2 = %1$
$query$,param1,param2);
END;
$$ language plpgsql;
The source data itself did not change during the time when I noticed this issue. Can anyone explain or guess what could have caused this degradation? The only other maintenance that I attempted was 'DISCARD PLANS;' which did not help.
Best regards,
Alastair
