Re: Strange performance degregation in sql function (PG11.1)

On 5/18/19 4:17 AM, Alastair McKinley wrote:
> 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 

Postgres version?

Was the migration from one Postgres version to another?

Did you run ANALYZE after migration?

More below.


> 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.

Can you provide the EXPLAIN ANALYZE for each case. If you are worried 
about the information revealed maybe use the anonymization available here:

https://explain.depesz.com/

Using EXECUTE will override the plan caching in plpgsql.

> 
> 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


-- 
Adrian Klaver
adrian.klaver@aklaver.com