在 Postgres 中为特定查询设置 work_mem [英] Setting work_mem in Postgres for specific queries

问题描述

我使用的库委托给 PostgreSQL 的 JDBC 驱动程序，有些查询非常复杂，需要更多内存.我不想将 work_mem 设置为所有查询的大值，只是这个子集.问题是执行以下代码会导致错误:

I'm using a library that delegates to a JDBC driver for PostgreSQL, and some queries are very complex and require more memory. I don't want to set work_mem to something large for all queries, just this subset. The problem is that executing the following code results in an error:

// pseudo-code for what is happening
String sql = "set work_mem = 102400;";
sql += "SELECT * FROM expensive_query";

ResultSet rs = DatabaseDriver.execute(sql);

当我运行这个时，我得到一个错误:

When I run this I get an error that:

set work_mem = 102400;

不返回任何结果.

这在 pgAdmin 中有效，因为您可以一次执行多个查询.有没有更好的方法来做到这一点，还是我需要执行任意 SQL 然后提取我想要的结果集?

This works in pgAdmin because you can execute multiple queries at once. Is there a better way to do this or do I need to execute arbitrary SQL and then extract the result set I want?

推荐答案

我不知道 DatabaseDriver 是做什么的，但是使用普通"JDBC，您只需要执行以下操作:

I have no idea what DatabaseDriver does, but with "plain" JDBC you just need to do the following:

Statment stmt = connection.createStatement();
stmt.execute("set work_mem = 102400");
ResultSet rs = stmt.executeQuery("select ...");

这篇关于在 Postgres 中为特定查询设置 work_mem的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！