CODEWITHSUNDEEP
postgresqlspring-boothikaricptimescaledb
TheStranger
TheStranger

Reputation: 1587

How do I set plan_cache_mode to force_custom_plan in my application?

I have a Spring Boot application with a REST service. I'm using PorstgreSQL as my database with TimescaleDB. I'm trying to set plan_cache_mode to force_custom_plan because I don't want it to switch to generic plan. I'm using Hikari to manage the datasource. How do I set it in the HikariDataSource?

Upvotes: 4

Views: 2423

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153970

As I explained in this article, you should probably set it only for the transactions that require you to rebuild a generic plan.

If you're using JPA, you can do it like this:

Session session = entityManager.unwrap(Session.class);
 
session.doWork(connection -> {
    try (Statement statement = connection.createStatement()) {
        statement.executeUpdate(
            "SET plan_cache_mode=force_custom_plan"
        );
    }
});

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247270

You can set the parameter on the database or user level:

CREATE ROLE|DATABASE somename SET plan_cache_mode = force_custom_plan;

Then all new connections as that user or to that database get that setting.

Upvotes: 4

Related Questions