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