>>>>> "Alastair" == Alastair McKinley <a.mckinley@xxxxxxxxxxxxxxxxxxxx> writes: Alastair> Hi all, Alastair> I recently experienced a performance degradation in an Alastair> operational system that I can't explain. I had a function Alastair> wrapper for a aggregate query that was performing well using Alastair> the expected indexes with the approximate structure as shown Alastair> below. Alastair> create or replace function example_function(param1 int, param2 int) returns setof custom_type as Alastair> $$ Alastair> select * from big_table where col1 = param1 and col2 = param2; Alastair> $$ language sql; This function isn't inlinable due to missing a STABLE qualifier; that's a pretty big issue. Without inlining, the function will be run only with generic plans, which means that the decision about index usage will be made without knowledge of the parameter values. Was your actual function inlinable? See https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions You can get the query plan of a non-inlined function using the auto_explain module (with its log_nested_statements option). The query plan of non-inlined function calls is not otherwise shown by EXPLAIN. Alastair> After creating two new indexes on this table to support a Alastair> different use case during a migration, this unchanged Alastair> function reduced in performance by several orders of Alastair> magnitude. Running the query inside the function manually on Alastair> the console however worked as expected and the query plan did Alastair> not appear to have changed. But when you run it manually, you'll get a custom plan, based on the parameter values. Alastair> On a hunch I changed the structure of the function to the Alastair> structure below and immediately the query performance Alastair> returned to the expected baseline. Alastair> create or replace function example_function(param1 int, param2 int) returns setof custom_type as Alastair> $$ Alastair> BEGIN Alastair> return query execute format($query$ Alastair> select * from big_table where col1 = %1$L and col2 = %1$ Alastair> $query$,param1,param2); Alastair> END; Alastair> $$ language plpgsql; Using EXECUTE in plpgsql will get you a custom plan every time (though you really should have used EXECUTE USING rather than interpolating the parameters into the query string). I suggest looking into the inlining question first. -- Andrew (irc:RhodiumToad)
