sql - postgres 中的大表连接非常慢(12 小时以上)

Question

我正在努力优化一个简单的 LEFT JOIN对两个非常大的表，到目前为止已经需要 > 12 小时才能完成并正在进行中。

下面是执行计划:

Gather  (cost=1001.26..11864143.06 rows=8972234 width=133)
  Workers Planned: 7
  ->  Nested Loop Left Join  (cost=1.26..10773657.51 rows=1281748 width=133)
        ->  Parallel Index Scan using var_case_aliquot_aliquot_ind on var_case_aliquot vca  (cost=0.56..464070.21 rows=1281748 width=103)
        ->  Index Scan using genotype_pos_ind on snv_genotypes gt  (cost=0.70..8.01 rows=1 width=65)
              Index Cond: ((vca.chrom = chrom) AND (vca.start = start) AND (vca.end = end) AND ((vca.alt)::text = (alt)::text))
              Filter: (vca.aliquot_barcode = aliquot_barcode)

这是查询:

SELECT vca.aliquot_barcode,
    vca.case_barcode,
    vca.gene_symbol,
    vca.variant_classification,
    vca.variant_type,
    vca.chrom,
    int4range(vca.start::integer, vca."end"::integer, '[]'::text) AS pos,
    vca.alt,
    gt.called AS mutect2_call,
    gt.ref_count,
    gt.alt_count,
    gt.read_depth,
    gt.called OR
        CASE
            WHEN (gt.alt_count + gt.ref_count) > 0 THEN (gt.alt_count::numeric / (gt.alt_count + gt.ref_count)::numeric) > 0.20
            ELSE false
        END AS vaf_corrected_call
   FROM analysis.var_case_aliquot vca
     LEFT JOIN analysis.snv_genotypes gt ON vca.aliquot_barcode = gt.aliquot_barcode AND vca.chrom = gt.chrom AND vca.start = gt.start AND vca."end" = gt."end" AND vca.alt::text = gt.alt::text

两个表都很大: vca和 gt分别有 900 万 (2 GB) 和 13 亿行 (346 GB)。

我创建了 vca ( MATERIALIZED VIEW ) 仅用于执行此连接。本质上，它是一个连接表，其中只有 1:1 匹配左连接所需的字段，然后是一些额外的元数据。正如您从查询计划中看到的那样，所有被连接的字段都被正确索引。

查询本身很简单，是否有我遗漏的东西可以加快速度？我不认为有什么方法可以使用 WHERE反而？

我可以在我的 postgres 设置中调整一些可能有帮助的东西吗？目前我有以下几点:

shared_buffers = 4096MB
effective_cache_size = 20GB
work_mem = 64MB
maintenance_work_mem = 4096MB
max_wal_size = 4GB
min_wal_size = 128MB
checkpoint_completion_target = 0.9
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

12/12 更新:

表 DDL:

CREATE TABLE analysis.snv_genotypes (
    aliquot_barcode character(30) NOT NULL,
    chrom character(2) NOT NULL,
    start bigint NOT NULL,
    "end" bigint NOT NULL,
    alt character varying(510) NOT NULL,
    genotype character(3),
    read_depth integer,
    ref_count integer,
    alt_count integer,
    called boolean
);

ALTER TABLE ONLY analysis.snv_genotypes
    ADD CONSTRAINT genotype_pk PRIMARY KEY (aliquot_barcode, chrom, start, "end", alt);
CREATE INDEX called_ind ON analysis.snv_genotypes USING btree (called);
CREATE INDEX genotype_pos_ind ON analysis.snv_genotypes USING btree (chrom, start, "end", alt);

CREATE MATERIALIZED VIEW analysis.var_case_aliquot AS
 SELECT var_case_aliquot.aliquot_barcode,
    var_case_aliquot.case_barcode,
    var_case_aliquot.chrom,
    var_case_aliquot.start,
    var_case_aliquot."end",
    var_case_aliquot.alt,
    var_case_aliquot.gene_symbol,
    var_case_aliquot.variant_classification,
    var_case_aliquot.variant_type,
    var_case_aliquot.hgvs_p,
    var_case_aliquot.polyphen,
    var_case_aliquot.sift
   FROM var_case_aliquot
  WITH NO DATA;

CREATE INDEX var_case_aliquot_aliquot_ind ON analysis.var_case_aliquot USING btree (aliquot_barcode);
CREATE INDEX var_case_aliquot_pos_ind ON analysis.var_case_aliquot USING btree (chrom, start, "end", alt);

更广泛的 DDL 在这里: https://rextester.com/JRJH43442

12/13 更新:

澄清一下，我在 CentOS 7.3 上使用 Postgres 10.5 w/16 核和 32 GB 内存。查询现在已经运行了 24 小时以上，没有任何结果。

检查状态似乎是 wait_event_type是 IO .这是否意味着查询正在抓取/写入暂存空间？这能解释缓慢吗？

+------------------+---------------+---------------+---------------+---------------+-----------------+--------------+--------+-------------+--------------+
| application_name | backend_start | xact_start    | query_start   | state_change  | wait_event_type | wait_event   | state  | backend_xid | backend_xmin |
+------------------+---------------+---------------+---------------+---------------+-----------------+--------------+--------+-------------+--------------+
| psql             | 12/12/18 8:42 | 12/12/18 8:42 | 12/12/18 8:42 | 12/12/18 8:42 | IO              | DataFileRead | active | 22135       | 22135        |
+------------------+---------------+---------------+---------------+---------------+-----------------+--------------+--------+-------------+--------------+

我有很多可用资源:

$ free -h
              total        used        free      shared  buff/cache   available
Mem:            31G        722M        210M        5.0G         30G         25G
Swap:          3.7G        626M        3.1G

我想增加可用内存会有所帮助吗？有什么方法可以优化需要比可用内存更多的内存的查询吗？

Answer 1

来自这篇文章的评论:

Your query is using genotype_pos_indand filtering on aliquot_barcode. Try deleting (temporarly) genotype_pos_ind and if that doesn't work, search how to force index usage.

您的查询应该使用 genotype_pk反而。

根据你所说的，可能有很多记录与 aliquot_barcode 的值相同。 , chrom , start和 end ，因此 RDBMS 将需要很长时间来过滤每个 aliquot_barcode .

如果它对您来说仍然太长，您可以尝试我的旧答案，我将保留以供进一步引用:



不幸的是，我无法优化您的查询:需要考虑的事情太多了。用 13 个字段的 900 万条记录构建结果可能太多了:可能会发生交换，您的操作系统不允许分配这么多内存，同时还会使 JOIN等..(写在真正的答案之前......)

我曾经优化过一些查询，其中包含大约 1000 万条记录的 15 个表。 SELECT在合理的时间内(少于 10 小时)，这种规模的规模是不可能实现的。

我没有任何 RDBMS 来测试我所说的内容。另外，我已经半年没有做任何 SQL 了 :p
找出为什么这需要这么多时间(如您所问)将花费太多时间，因此这是原始问题的另一种解决方案。

我采用的解决方案是制作临时表:

创建临时表:tmp_analysis ，与您的 SELECT 具有相同的字段+ 一些实用领域:

一个 ID 字段( tmp_ID ，一个大整数)，一个用于检查记录是否已更新的 bool 值(tmp_updated)，以及用于检查何时更新的时间戳( tmp_update_time )。
当然还有来自原始 SELECT 的具有相同数据类型的所有字段(来自 vca 和 gt )

插入您来自 vca 的所有记录:

使用 null (或任何其他默认值，如果你不能)来自 gt 的字段暂时。套装 tmp_updated为假。使用简单的 count()对于主键。

使用 gt 中的字段更新所有这些记录。

使用 WHERE而不是 JOIN :

UPDATE tmp_analysis as tmp -- I don't think you need to use a schema to call tmp_analysis
    SET tmp_update = true,
    tmp_update_time = clock_timestamp(),
    tmp.mutect2_call = gt.called
    gt.ref_count,
    gt.alt_count,
    gt.read_depth,
    gt.called = -- ... (your CASE/WHEN/ELSE/END should work here)
FROM 
    analysis.snv_genotypes gt
WHERE --JOIN should work too
    tmp.aliquot_barcode = gt.aliquot_barcode AND 
    tmp.chrom = gt.chrom AND 
    vca.start = gt.start AND 
    tmp."end" = gt."end" AND 
    tmp.alt::text = gt.alt::text

我说你应该用 EXISTS出于性能原因，但我错了，因为我认为您不能从 EXISTS 内部检索字段健康)状况。可能有一种方法可以告诉 Postgresql 它是一对一的关系，但我不确定。总之，指数

显然，SELECT您的 tmp_analysis表来获取您的记录!

一些注意事项:

如果花费太多时间:

使用 tmp_ID例如，将更新次数限制为 10 000 的字段并检查第三个查询的执行计划( UPDATE ):您应该对临时表进行完整扫描，并对 gt 进行索引扫描(在 genotype_pk 上)。如果没有，请检查您的索引并搜索如何强制 PGSL 使用索引。您应该使用 WHERE tmp_ID < 10000而不是 LIMIT 10000 . IIRC, LIMIT将执行整个查询并只给你部分结果。

如果仍然花费太多时间:

使用 tmp_ID 分割查询并且(如您所说)使用 loop statement在 UPDATE一次查询 100 000 条或更少的记录(再次使用 where tmp_ID < x AND tmp_ID > y )。再次检查执行计划:全扫描应该受 tmp_id限制在索引扫描之前。不要伪造在此字段上添加索引(如果它还不是主键)。

如果以后需要再次调用:

使用 BEGIN/END TRANSACTION封装所有查询，以及 TEMPORARY TABLE CREATE TABLE tmp_analysis 上的选项这样您就不必在执行查询后清理 tmp_analysis。

如果您仍然遇到循环问题:

在循环内使用事务，如果它再次卡住，则停止它。然后您可以稍后使用较小的循环大小恢复它。

如果你想减少一点执行时间:

您可以使用 INSERT .. AS .. SELECT 在一个查询中执行第 1 步和第 2 步。 ，但我不记得如何为来自 gt 的字段设置数据类型，因为它们将被设置为 null。通常，这应该整体上快一点。

如果你好奇:

没有循环的查询仍然需要 10 多个小时，停止它并检查 tmp_update_time 以查看执行时间如何演变，也许它会给你一个关于为什么原始查询不起作用的线索。 PGSQL 上有多个配置选项来限制 RAM 使用、磁盘使用、线程。您的操作系统可能会设置自己的限制，并检查磁盘交换、CPU 缓存使用情况等(我认为您已经完成了其中的一些工作，但我没有检查)