如何最好地索引这张表？

Question

我在PostgreSQL (>2000 M行)中有一个大表，必须尽快查询它。它代表了生物样品中基因表达的测量。问题是，有时测量直接在基因上(“探针”然后为空)，有时测量是通过基因的“探针”进行的(“基因”仍然是设置的)。一个基因可以有多个探针。没有其他表格包含基因-探针关系。

CREATE TABLE "gene_measurements" (
  "gene" INTEGER NOT NULL REFERENCES "genes" ON DELETE CASCADE,
  "sample" INTEGER NOT NULL REFERENCES "samples" ON DELETE CASCADE,
  "probe" INTEGER REFERENCES "probes" ON DELETE CASCADE,
  "value" REAL NOT NULL
);

常见的查询包括获取给定样本中所有基因的表达，在所有样本中获取给定基因/探针的表达，或在给定样本中获取给定基因/探针的表达。

现在，我有以下覆盖指数。它工作得很好，但是非常消耗空间。

CREATE INDEX "gene_measurements_gene_sample_value_index" ON "gene_measurements" ("gene", "sample", "value");
CREATE INDEX "gene_measurements_sample_gene_value_index" ON "gene_measurements" ("sample", "gene", "value");
CREATE INDEX "gene_measurements_sample_probe_value_index" ON "gene_measurements" ("sample", "probe", "value");
CREATE INDEX "gene_measurements_probe_sample_value_index" ON "gene_measurements" ("probe", "sample", "value");

在保持速度的同时，我还能做些聪明的事情来获得更整洁和/或更小的实现吗？谢谢!

Answer 1

一个真正的SQL表需要作为主键。从理论上讲，没有键的桌子是没有意义的。(实际上，一个没有PK的3G行的表是一场灾难)

在您的示例中，自然键似乎是(gene_id,sample_id,probe_id)列的组合。这三列的值是唯一寻址value.的所必需的。

问题在于您的if probe is absent; measurement was directly on the gene反约束。这将禁止使用三列键。删除此异常将允许多列主键。现在，数据技巧是将一个虚拟行插入到探测中，例如，使用id=0。

INSERT INTO probe(probe_id, probe_when, probe_name)
 VALUES( 0, '1901-01-01 00:00:00', 'Dummy probe');

现在更新gene_measurements，将probe IS NULL更改为probe=0。

CREATE TABLE gene_measurements (
  gene INTEGER NOT NULL REFERENCES genes(gene_id) ON DELETE CASCADE
  , sample INTEGER NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE
  , probe INTEGER NOT NULL REFERENCES probes (probe_id)
  , value REAL NOT NULL
        , PRIMARY KEY ( gene_id, sample_id,probe_id)
);

也可以添加一些其他索引，具有不同的排序，以帮助特定的查询，例如：

CREATE UNIQUE INDEX ON gene_measurements (sample_id,gene_id,probe_id);

您将需要一个探针FK的支持索引，任何以探测作为其第一列的索引都可以：

CREATE INDEX ON gene_measurements (probe_id, ...);

Answer 2

您可以在空间和时间之间选择任意的阈值。现在，你已经索引了整个表四次。这显然消耗了很多空间。

您可以从索引中删除一些数据，以换取更快的运行时：

• 例如，可以从所有索引中删除value。但是，除了索引中的查找之外，还需要对数据进行查找。
• 您还可以完全删除一些索引。例如，根据数据的不同，可以删除(sample, gene)或(sample, probe)。这将删除数据的一个完整覆盖范围，同时仍然允许您使用sample部件来处理具有sample和已删除列条件的查询。同样，您移除的情况也不再像以前那么快了。

如果您的目标是不惜一切代价实现最小的运行时，那么所有这些建议都不适合您。我不认为PostgreSQL宇宙中现在有任何东西可以解决你的问题。

由于您的数据很简单，并且您的用例受到限制，所以您可以考虑PostgreSQL以外的解决方案。特别是，你基本上只想要一个B树数据结构。(或多重。)有其他解决办法来构建这样的数据结构，例如，QDBM。尽管如此，您仍然需要构建多个这样的结构，以便为每个选择类型进行优化。我认为在空间上可以实现的节省不是很高--基本上，你可以去掉数据，但没有一个索引。因此，您可以节省当前存储大小的1/5，而代价是限制功能和软件生态系统中的额外复杂性。

你必须决定你需要什么，你想要什么，你想为这些目标牺牲什么。考虑到我在这里写的东西，我会坚持使用PostgreSQL。