PostgreSQL work_mem with spring framework

Question

It's possible to increase work_mem for one special query which is implemented with Spring Framework?

My repository class is:

@Repository
public interface AccountSpringRepository extends JpaRepository<Account, Long> {

...

    @Query(value = "SELECT DISTINCT a FROM Account a here_is_the_query> now())")
    List<Account> findAccountByStatus(
            @Param("status") List<EnumStatusType> status,
            Pageable limit);
}

It's possible and how to increase work_mem for the given query?

SET work_mem = '256MB';

Regards, Rafal

Did you try @Query(value = "set work_mem = '128MB'; SELECT DISTINCT a FROM Account a here_is_the_query> now())") — user330315
– user330315, Commented Aug 28, 2019 at 14:53

Laurenz Albe · Accepted Answer · 2019-08-26 13:17:02Z

1

I don't know how to do it in the framework specifically, but the way this is done in general is:

start a transaction

run

SET LOCAL work_mem = '256MB';  --valid only for the transaction

run your query
commit the transaction

answered Aug 26, 2019 at 13:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

rafcio Over a year ago

I know this with LOCAL. But without LOCAL this sould be changed for all followed queries. Why I can't see the new value, when I call "SHOW work_mem" in pgAdmin? I have to evalute the change before we put this on production.

Laurenz Albe Over a year ago

SET changes the parameter only for the current session. Do you really want this for all queries? Do you have low enough max_connections to afford such a high value? If yes, you'd set it in postgresql.conf and reload to activate it.

rafcio Over a year ago

I changed it only fo the current trancaction.

rafcio · Accepted Answer · 2019-08-28 14:42:06Z

0

I did this:

@Modifying @Query(value = "SET work_mem = '128MB'", nativeQuery = true) void setWorkMem();

and this:

statement = connection.createStatement(); statement.execute("SET work_mem = '128MB';"); statement.execute("insert into public.test values('trest')");

And when I call "SHOW work_mem" than I get the DEFAULT value back. And in the postgresql log I see:

LOG: Ausführen <unnamed>: SET work_mem = '128MB' -> called from Java code

and not

LOG: Anweisung: -- SET work_mem = '128MB'; -> called from pgAdmin

Why?

answered Aug 28, 2019 at 14:42

rafcio

97 bronze badges

Collectives™ on Stack Overflow

PostgreSQL work_mem with spring framework

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related