Search Postgresql Archives

Strange performance degregation in sql function (PG11.1)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux