2

When executing a statement in Postgres you can set specific query planner configuration parameters as described in the documentation.

These can be set on a system level by the administrator or on a user level in a specific session. I now have the issue that the application does not support setting parameters in the user session, and I don't want to set it globally, since this would affect all queries.

Is it possible to set a query planning parameter via hints in the SQL itself (like in Oracle) or create a profile on a certain SQL statement to include certain parameters?

For context, the parameter I am talking about is plan_cache_mode. When it is set to auto, a certain prepared statement is using a generic plan that has very bad performance (~8s execution time). As soon as I force custom plans via force_custom_plan the execution time drops to ~10ms. I know that better indexes or statistics might change the generic plan, but I am looking for an easier solution than diving into SQL optimization.

Improve this question
2
  • If your application does not support setting parameters in the session, how would it support hints? Commented May 17, 2023 at 23:40
  • That is true, I don't know yet if the devs will be able to to that, it was just a route I thought might be worthwile pursuing. Commented May 19, 2023 at 21:59

2 Answers 2

Reset to default
4

You can put your SQL statement into a function:

CREATE FUNCTION myquery() RETURNS TABLE (...)
   LANGUAGE sql SET plan_cache_mode = force_custom_plan AS
$$SELECT ...$$;

that would override the default parameter value while the function executes.

Improve this answer
5
  • It's a nice idea, but sadly incompatible with the DAL the developers use. But thanks! Commented May 17, 2023 at 11:00
  • I have no idea what a DAL is, but if it can execute SELECT ..., it can certainly also execute SELECT * FROM myquery(). Commented May 17, 2023 at 11:15
  • It's a data abstraction layer and we can't change how it accesses the data (else we could insert the change in the variable itself). I just have control over the database where a SQL query arrives that I would like to tune. Commented May 17, 2023 at 13:00
  • Looks like you have to work around the DAL then. That is sometimes necessary for good performance. Tuning queries is the alternative, but that very often also meens rewriting them. Commented May 17, 2023 at 13:25
  • …or call the function from a view, certainly the DAL can access views? Commented May 17, 2023 at 23:39
2

There is a 3rd party extension to add hints, pg_hint_plan, but I don't see anything there that would hint the plan_cache_mode. So you would have to dig into the details of the good and bad plan, and hint those details. And if you can't control the DAL, how are you going to inject hints, anyway?

I would consider changing plan_cache_mode globally (or at least for a particular user and/or database) to be a low-risk change. Have you tried it and experienced a problem?

Improve this answer
1
  • Thanks for the answer, but yes, I don't know if the developers will be able to insert hints into the SQLs. Setting plan_cache_mode globally (doesn't really matter if global or per session, since theres only one application user anyway) is what I'm doing right now, but I've experienced issues in the past where other SQLs where then slower because they would profit more from generic plans. Commented May 19, 2023 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.