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(dot)klaver(at)aklaver(dot)com