c++—在postgresql中根据行的id选择大量行的最快方法？

ffvjumwh 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(301)

我使用postgres和libpqxx，我有一个表，我们将简化为

data_table
{
  bytea id PRIMARY KEY,
  BigInt size
}

如果我在cpp中有一组id，例如 std::unordered_set<ObjectId> Ids ，从数据表中获取id和size参数的最佳方法是什么？
到目前为止，我使用了一份事先准备好的声明： constexpr char* preparedStatement = "SELECT size FROM data_table WHERE id = $1"; 然后在一个事务中，我为集合中的每个条目调用了prepared语句，并检索了集合中每个条目的结果，

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上的类型 Package ，也称为256位加密散列。

sql postgresql c++rdms libpqxx

来源：https://stackoverflow.com/questions/62668439/fastest-way-to-select-a-lot-of-rows-based-on-their-id-in-postgresql

2条答案

按热度按时间

cgh8pdjw1#

我所理解的任务是：
得到 id （pk）和 size （bigint）表示一个包含数百万行和更多列的表中的“数以万计的对象”（simplified down）。
检索的最快方法是只索引扫描。在您的特定情况下，最便宜的方法是通过“包含”查询来为您的查询创建一个“覆盖索引” size pk索引中的列如下（需要postgres 11或更高版本）：

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

关于覆盖索引：
postgresql中的覆盖索引有助于连接列吗？
然后一次检索多个ID的单个查询（或几个查询）中的所有行，如：

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

或者这里列出的其他方法之一：
从整数数组按索引查询表
或者创建一个临时表并连接到它。
但不要“一个接一个地插入所有的ID”。快得多 COPY （或meta命令） \copy 在psql中）填充temp表。请参见：
如何用postgres中csv文件中的值更新选定的行？
而且您不需要临时表上的索引，因为该索引将在顺序扫描中读取。你只需要我列出的覆盖pk指数。
你可能想 ANALYZE 填写临时表后，给postgres一些列统计信息。但只要你得到我想要的索引扫描，你也可以跳过。查询计划再好不过了。