在PostgreSQL中根据它们的ID选择大量行的最快方法是什么?

Question

我正在使用带有libpqxx的postgres，我有一个表格，我们将简化为：

data_table
{
bytea id PRIMARY KEY,
BigInt size
}

如果我在 cpp 中有一组 ID，例如std::unordered_set<ObjectId> Ids，从data_table中获取 ID 和大小参数的最佳方法是什么？

到目前为止，我已经使用了准备好的声明：constexpr char* preparedStatement = "SELECT size FROM data_table WHERE id = $1";

然后在事务中，我为集合中的每个条目调用了该预准备语句，并检索了集合中每个条目的结果，

pqxx::work transaction(SomeExistingPqxxConnection);
std::unordered_map<ObjectId, uint32_t> result;
for (const auto& id : Ids)
{
auto transactionResult = transaction.exec_prepared(preparedStatement, ToPqxxBinaryString(id));
result.emplace(id, transactionResult[0][0].as<uint32_t>());
}
return result;

因为集合可以包含数万个对象，而表可以包含数百万个对象，这可能需要相当长的时间来处理，而且我认为这不是对 postgres 的特别有效的使用。

我对SQL几乎是新手，所以我真的不知道我正在做的事情是否是正确的方法，或者这是否是一种更有效的方法。

E：值得一提的是，ObjectId 类基本上是 std：：array<uint8_t，> 的类型包装器，也就是 256 位加密哈希。

Answer 1

据我了解的任务：

从包含数百万行和可能还有几列("简化"(的表中获取"数万个对象"的id(PK(和size(bigint(。

最快的检索方法是仅索引扫描。在您的特定情况下，获得它最便宜的方法是通过在 PK 索引中"包含"size列来为您的查询提供"覆盖索引"，如下所示(需要 Postgres 11 或更高版本(：

CREATE TEMP TABLE data_table (
id   bytea
, size bigint 
, PRIMARY KEY (id) INCLUDE (size)  -- !
)

关于覆盖索引：

• 在PostgreSQL中覆盖索引有助于JOIN列吗？

然后一次检索多个 ID 的单个查询(或几个查询(中的所有行，如下所示：

SELECT id, size
FROM   data_table
JOIN  (
VALUES ('id1'), ('id2') -- many more 
) t(id) USING (id);

或者这里列出的其他方法之一：

• 按整数数组中的索引查询表

或者创建一个临时表并加入其中.
但不要"将所有这些 ID 逐个插入其中"。使用更快的COPY(或 psql 中的元命令copy(来填充临时表。看：

• 如何使用 Postgres 中 CSV 文件中的值更新选定的行？

而且您不需要临时表上的索引，因为无论如何都会在顺序扫描中读取该索引。你只需要我列出的覆盖PK指数。

您可能希望在填充临时表后ANALYZE临时表，以便为 Postgres 提供一些列统计信息以供使用。但只要你得到我想要的仅索引扫描，你也可以跳过它。查询计划不会比这更好。

Answer 2

id 是主键，因此已编制索引，因此我首先关心的是查询设置时间。 例如，预编译存储过程。 第二种方法是将您的集合放在一个临时表中，也可能键入 id，这样两个表/索引就可以在一个选择中连接。 这方面的索引应该排序，就像树而不是哈希一样，所以它们可以合并。