sql - 提高 PostgreSQL 数组查询的性能

Question

我在 PostgreSQL 表中存储大型 double 向量(140 万个值)。该表的创建语句如下。

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 doesn't seem to scale terribly well for lookups as the vector length grows even with STORAGE set to EXTERNAL (O(n)) .这使得查询像下面这样，我们在数组中选择了许多单独的值，非常非常慢(分钟 - 小时)。

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

如果有足够多的单个索引被拉取，它甚至可能比拉取整个数组更慢。

有什么方法可以使此类查询更快？

编辑

• 我正在使用 PostgreSQL 9.3。

• 我正在运行的所有查询可能都是简单的 SELECT

  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';
  

  在一种情况下，这些查询的结果通过预测模型提供。预测概率被存储回另一个表中的数据库。在其他情况下，从阵列中提取选定项目以进行下游分析。

• 目前最长的单个数组为 140 万，其他较短，最小的为 22 千，平均长度约为 10 万。

• 理想情况下，我会将数组数据存储为一个宽表，但是对于 140 万个条目，这是不可行的，如果我们想提取一个来自数据库的数据的完整数组。我们这样做是为了在对完整数据集进行分析时加载下游数据存储。

Answer 1

您将数据存储在结构化数据管理存储容器(即 PostgreSQL)中，但由于数据的性质(即类似数据的大型但大小不规则的集合)，您实际上将数据存储在外部 的容器。 PostgreSQL 不擅长从不规则和不可预测的数据中检索数据？)大型数组，正如您所注意到的；数组存储在外部这一事实已经证明您的要求与 PostgreSQL 的优势不一致。很可能存在比 PostgreSQL 更好的存储和读取数组的解决方案。鉴于通过预测模型分析数组的结果存储在 PostgreSQL 数据库的一些表中，这暗示了一种混合解决方案:以某种形式存储您的数据，以允许以您需要的模式进行有效访问，然后将结果存储在 PostgreSQL 中以供使用进一步加工。

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

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

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

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