使用大型数据集加速postgresql查询

Question

我有一个postgresql数据库，其中包含我目前感兴趣的两个表。第一个表是我的" file“表，它包含一个文件名、有关该文件的一些相关信息，并以一个串行id作为其主键。下面是我的文件表的大致轮廓：

fileData(fileName varchar(120) unique, ... other info, id serial primary key)

然后，我有另一个表，它包含来自文件表中列出的文件的信息。它通过文件表的id链接到文件表。"data“表中有与每个文件相对应的可变行数，行号从几百行到几十万行不等。下面是我数据表的大致轮廓：

rawData(fileID integer references fileData(id), lineNum integer, data1 double, ... other info)

为了实现上面的内容，我有一个查询，首先通过fileData进行排序以获取每个文件的id以及其他一些信息。然后，我将对与该文件对应的原始数据进行排序，以找到“有趣”的信息。这个特定的查询是用c++编写的，使用Qt来处理实际的处理，但是大部分工作都是由数据库完成的( Qt只是将数据库查询作为文本查询传递进来，该查询需要匹配sql数据库通常需要的所有格式)。下面是我的查询示例：

QSqlQuery fileQuery, dataQuery;
int id;
fileQuery.prepare("SELECT id, fileType FROM fileData ORDER BY id");
if (!fileQuery.exec()){
    //error
    return;
}
while (fileQuery.next()){
    id = fileQuery.value(0).toInt();
    dataQuery.prepare("Select lineNum, data1, ...other info "
                      "FROM rawData WHERE fileID = ? and data1 < ? "
                      "ORDER BY fileID, lineNum");
    dataQuery.addBindValue(id);
    dataQuery.addBindValue(num);
    if (!dataQuery.exec()){
        return;
    }
    while (dataQuery.next()){
      //code to load pertinant info into my program to handle later
    }
}

这个程序花了大约两个小时才运行到最近，加载了1400个文件，大约有100万行数据。然而，我刚刚得到了更多的数据，现在有多达1650个数据文件，有1.3亿行数据，我的程序已经慢到爬行了。过去花了两个小时的时间，现在只花了六个多的时间来完成我现在拥有的文件的1/4，我的调试输出告诉我，我仍然在处理以前运行这个程序的文件，而不是任何新的数据。检查我的任务管理器，我可以看到我的程序几乎不能工作，而postgresql正在使用一个完整的核心来提供我所要求的数据，所以我知道当前的延迟是在我的sql命令中，而不是在我同时对数据所做的事情上。

最后，目前，在这个问题上投入更多的硬件不是我能做的事情。话虽如此，我还能做些什么来优化查询以提高访问这些数据的速度吗？或者说，我已经做了正确的事情，只需要吸收它，并处理由于我正在工作的数据集的大小而导致的缓慢？

Answer 1

您可能只能执行每个查询一次。

1)文件表非常小，因此可以将其加载到内存映射中并完成

2)由fileType过滤并按文件id排序的数据表查询不应该占用时间(当然，您有fileID + lineNum上的索引，对吗?)

Answer 2

是否有理由不将这两个查询合并为一个查询？

SELECT id, fileType, lineNum, data1, ...other info 
FROM fileData LEFT JOIN rawData on fileData.id = rawData.fileID
WHERE data1 < ? 
ORDER BY fileID, lineNum

另外，既然您说num是函数中的常量，而不是将其绑定到可替换的参数，那么我只需构造带有其值的查询字符串。确保在两个表上都有正确的索引也是必要的。