提高PostgreSQL数组查询的性能

Question

提高PostgreSQL数组查询的性能

sqlarrayspostgresqlbigdatapostgresql-performance

5

我将双精度浮点数组（1.4百万个数值）存储在PostgreSQL表中。该表的创建语句如下。

CREATE TABLE analysis.expression
(
  celfile_name character varying NOT NULL,
  core double precision[],
  extended double precision[],
  "full" double precision[],
  probeset double precision[],
  CONSTRAINT expression_pkey PRIMARY KEY (celfile_name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE analysis.expression ALTER COLUMN core SET STORAGE EXTERNAL;
ALTER TABLE analysis.expression ALTER COLUMN extended SET STORAGE EXTERNAL;
ALTER TABLE analysis.expression ALTER COLUMN "full" SET STORAGE EXTERNAL;
ALTER TABLE analysis.expression ALTER COLUMN probeset SET STORAGE EXTERNAL;

这个表中的每个条目只会被写入一次，但可能会在随机索引处多次读取。根据PostgreSQL 的测试结果，即使将STORAGE设置为EXTERNAL（O(n)），随着向量长度的增长，查询速度也不会有很好的扩展性。这使得像下面这样选择数组中许多单独值的查询非常缓慢（需要几分钟甚至几小时）。

SELECT probeset[2], probeset[15], probeset[102], probeset[1007], probeset[10033], probeset[200101], probeset[1004000] FROM expression LIMIT 1000;

如果拉取足够多的单独索引，甚至比拉取整个数组还要慢。

有没有办法使这样的查询更快？

编辑：

I am using PostgreSQL 9.3.
All the queries I am running are simple SELECTs possibly
```
SELECT probeset[2], probeset[15], probeset[102], probeset[1007], probeset[10033], probeset[200101], probeset[1004000] FROM expression JOIN samples s USING (celfile_name) WHERE s.study = 'x';
```
In one scenario the results of these queries are feed through prediction models. The prediction probability gets stored back into the DB in another table. In other cases select items are pulled from the arrays for downstream analysis.
Currently 1.4 million is the longest single array, the others are shorter with the smallest being 22 thousand and the average being ~ 100 thousand items long.
Ideally I would store the array data as a wide table but with 1.4 million entries this isn't feasible, and long tables (i.e. rows with celfile_name, index, value) are much slower than PostgreSQL arrays if we want to pull a full array from the data from the DB. We do this to load our downstream data stores for when we do analysis on the full dataset.

- Nixuz

4

我怀疑Postgres需要在执行任何操作之前读取整个数组。即使表格表示更大，您最好将值存储为表格中的行。 - Gordon Linoff

1

一个性能问题应该明显地提供正在使用的Postgres版本，而不需要先被问到。 - Erwin Brandstetter

@Patrick - 我更新了问题，并提供了更多有关其他查询的见解。简而言之，每个数组的长度都可能不同，最长的单个数组有140万项。跨数组的相同索引无关联。 - Nixuz

@ErwinBrandstetter - PostgreSQL 9.3。不知道，谢谢你让我添加版本号。 - Nixuz

长表格（即具有celfile_name、index和value的行）比PostgreSQL数组慢得多。我不相信这一点。你有测量过吗？顺便说一句，通常情况下，人们不希望将celfile_name作为PK中的文本值，而是添加一个整数代理并对（surrogate_id，array_index）强制执行PK。 - wildplasser

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

您将数据存储在结构化数据管理存储容器（即PostgreSQL）中，但由于您的数据性质（即大型但大小不规则的类似数据集合），实际上您将数据存储在容器之外。正如您所注意到的那样，PostgreSQL不擅长从不规则和难以预测的大型数组检索数据；数组被存储在外部已经证明了您的要求与PostgreSQL优势不符。存储和读取数组的更好的解决方案比PostgreSQL更为合适是非常可能的。通过预测模型分析数组的结果存储在PostgreSQL数据库中的表格提示了一个混合解决方案：以某种形式存储您的数据，以便以您需要的模式进行有效访问，然后将结果存储在PostgreSQL中以供进一步处理。

由于您没有提供有关预测模型的任何细节，因此无法在此答案中具体说明，但希望这对您有所帮助。

如果您的预测模型是用某种PostgreSQL驱动程序可用的语言编写的，则将数据存储在适合该语言的格式中，进行预测并将结果写入PostgreSQL中的表格。这适用于使用pq库的C和C++等语言，以及使用高级库（如JDBC）的Java、C#、Python等语言。

如果您的预测模型是用MatLab编写的，则将数组存储在MatLab格式中，并连接到PostgreSQL获取结果。如果使用R编写，则可以使用PostgreSQL的R扩展。

关键在于您应该以允许在预测模型中有效使用的形式存储数组。将数据存储与预测模型匹配，而不是相反。