Smart logic queries performance inside functions for PostgreSQL

Question

Consider the following sql query:

SELECT a,b,c
FROM t
WHERE (id1 = :p_id1 OR :p_id1 IS NULL) AND (id2 = :p_id2 OR :p_id2 IS NULL)

Markus Winand in his book "SQL Performance explained" names this approach as one of the worst performance anti-patterns of all, and explains why (the database has to prepare plan for the worst case when all filters are disabled).

But later he also writes that for the PostgreSQL this problem occurs only when re-using a statement (PreparedStatement) handle.

Assume also now that query above is wrapped into the function, something like:

CREATE FUNCTION func(IN p_id1 BIGINT,IN p_id2 BIGINT)
...
 $BODY$
  BEGIN
     ...
  END;
 $BODY$

So far I have misunderstanding of few points:

Will this problem still occur in case of function wrapping? (I've tried to see the execution plan for the function call, but Postgres doesn't show me the details for the internal function calls even with SET auto_explain.log_nested_statements = ON).
Let's say I'm working with legacy project and can not change the function itself, only java execution code. Will it be better to avoid prepared statement here and use dynamic query each time? (Assuming that execution time is quite long, up to several seconds). Say this, probably, ugly approach:

getSession().doWork(connection -> {
    ResultSet rs = connection.createStatement().executeQuery("select * from func("+id1+","+id2+")");
    ...
})

Egor Rogov · Accepted Answer · 2017-05-05 15:00:30Z

2

1. It depends.

When not using prepared statements, PostgreSQL plans a query every time anew, using parameters values. It is known as custom plan.

With prepared statements (and you're right, PL/pgSQL functions do use prepared statements) it's more complicated. PostgreSQL prepares the statement (parses its text and stores parse tree), but re-plans it each time it is executed. Custom plans are generated at least 5 times. After that the planner considers using a generic plan (i. e. parameter-value-independent) if it's cost is less than the average cost of custom plans generated so far.

Note, that cost of a plan is an estimation of the planner, not real I/O operations or CPU cycles.

So, the problem can occur, but you need some bad luck for that.

2. The approach you suggested will not work, because it doesn't change behavior of the function.

In general it is not so ugly for PostgreSQL not to use parameters (as it is for e. g. Oracle), because PostgreSQL doesn't have shared cache for plans. Prepared plans are stored in each backend's memory, so re-planning will not affect other sessions.

But as far as I know, currently there is no way to force planner to use custom plans (other than reconnect after 5 executions...).

answered May 5, 2017 at 15:00

Egor Rogov

5,48828 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andremoniy Over a year ago

Thank's for your answer. Regarding 1st point I do not quite understand. Does it mean that described problem generally is not a problem for PostgreSQL when such type of queries are wrapped into the function?

Egor Rogov Over a year ago

It really depends on luck. Lets say a query has one parameter and badly needs different plans for values A and B. For example, A asks for sequential scan (high cost), while B benefits from an index scan (low cost). Generic plan uses sequential scan. You execute F(A) 5 times and the planner decides that it's okay to switch to generic plan. Now you have a problem: F(B) will not use index scan. But if you call F(A), F(B), F(A), F(B) and so on, average custom cost will be less than cost of generic plan and you are safe.

Andremoniy Over a year ago

Okay, but I think we always should consider worst case, shouldn't we? Looks like in the worst case it still will be the problem. And do I correctly understand that for a function the plan will be calculated regardless using prepared statement?

Egor Rogov Over a year ago

Yes, PL/pgSQL functions implicitly use prepared statements to execute SQL queries.

saward Over a year ago

I'm not sure when it was available in postgres, but you can force the planner to continue using custom plans after the 5th exection. E.g., for a particular transaction: 'set local plan_cache_mode = force_custom_plan' will do it (postgresql.org/docs/14/…)

Collectives™ on Stack Overflow

Smart logic queries performance inside functions for PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related