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
