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
