sql - 时间相关的 postgres 查询速度

Question

我有一种情况，select 查询可以在 3 秒内完成或超过 1 小时仍未完成(我等不及并杀死了它)。我相信这可能与 postgres 服务器的自动统计收集行为有关。我有一个 3 表连接，其中一个有超过 7000 万行。

-- tmp_variant_filtered has about 4000 rows
-- variant_quick > 70 million rows
-- filtered_variant_quick has about 70 k rows
select count(*)
from "tmp_variant_filtered" t join "variant_quick" v on getchrnum(t.seqname)=v.chrom
        and t.pos_start=v.pos and t.ref=v.ref
        and t.alt=v.alt
    join "filtered_variant_quick" f on f.variantid=v.id
where v.samplerun=165
;

-- running the query immediately after tmp_variant_filtered was loaded
-- Query plan that will take > 1 hour and not finish
Aggregate  (cost=332.05..332.06 rows=1 width=8)
   ->  Nested Loop  (cost=0.86..332.05 rows=1 width=0)
         ->  Nested Loop  (cost=0.57..323.74 rows=1 width=8)
               Join Filter: ((t.pos_start = v.pos) AND ((t.ref)::text = (v.ref)::text) AND ((t.alt)::text = (v.alt)::text) AND (getchrnum(t.seqname) = v.chrom))
               ->  Seq Scan on tmp_variant_filtered t  (cost=0.00..315.00 rows=1 width=1126)
               ->  Index Scan using variant_quick_samplerun_chrom_pos_ref_alt_key on variant_quick v  (cost=0.57..8.47 rows=1 width=20)
                     Index Cond: (samplerun = 165)
         ->  Index Only Scan using filtered_variant_quick_pkey on filtered_variant_quick f  (cost=0.29..8.31 rows=1 width=8)
               Index Cond: (variantid = v.id)

-- running the query a few minutes after tmp_variant_filtered was loaded with copy command
-- query plan that will take less than 5 seconds to finish
 Aggregate  (cost=425.69..425.70 rows=1 width=8)
   ->  Nested Loop  (cost=8.78..425.68 rows=1 width=0)
         ->  Hash Join  (cost=8.48..417.37 rows=1 width=8)
               Hash Cond: ((t.pos_start = v.pos) AND ((t.ref)::text = (v.ref)::text) AND ((t.alt)::text = (v.alt)::text))
               Join Filter: (getchrnum(t.seqname) = v.chrom)
               ->  Seq Scan on tmp_variant_filtered t  (cost=0.00..359.06 rows=4406 width=13)
               ->  Hash  (cost=8.47..8.47 rows=1 width=20)
                     ->  Index Scan using variant_quick_samplerun_chrom_pos_ref_alt_key on variant_quick v  (cost=0.57..8.47 rows=1 width=20)
                           Index Cond: (samplerun = 165)
         ->  Index Only Scan using filtered_variant_quick_pkey on filtered_variant_quick f  (cost=0.29..8.31 rows=1 width=8)
               Index Cond: (variantid = v.id)

如果在填充 tmp 表后立即运行查询，它将为您提供如上所示的计划，并且查询将花费很长时间。如果您等待几分钟，则哈希连接的计划将较低。上层的成本估算小于下层。

由于查询嵌入在某种脚本语言中，因此使用了顶级计划，通常它会在几个小时内完成。如果我在终端上执行此操作，则在终止脚本后，将使用较低的计划，通常需要几秒钟才能完成。

我什至做了一个实验，将 tmp_variant_filtered 表复制到另一个表中，比如“测试”。如果我在复制后立即运行查询(手动，会有几秒钟的延迟)，那么我就被卡住了。杀死当前的工作，等待几分钟，同样的查询变得非常快。

很久以前我在做查询调优；现在我刚刚开始重新拾起它。我正在阅读并试图理解为什么 postgres 有这样的行为。望高手给予指点。

Answer 1

将行插入表中后，立即没有列值及其分布的统计信息。因此优化器假定表是空的。从(假设)空表中检索所有行的唯一明智策略是执行 Seq Scan。你可以在执行计划中看到这个假设:

Seq Scan on tmp_variant_filtered t  (cost=0.00..315.00 rows=1 width=1126)

rows=1意味着优化器期望 Seq Scan 只返回一行。因为它只有一行，所以规划器选择一个嵌套循环来进行连接——这意味着 Seq Scan 对另一个表中的每一行进行一次(如果您使用 explain (analyze, verbose) 生成执行计划，您可以更清楚地看到这一点)

如果您不手动执行，“autovacuum 守护程序”会在后台更新统计信息。这就是为什么等待一段时间后，您会看到一个更好的计划，因为优化器现在知道该表不为空。

一旦优化器对表的大小有了更好的了解，它就会选择效率更高的 Hash Join 将两个表放在一起——这意味着 Seq Scan 只执行一次，而不是多次。

始终建议运行 analyze (或 vacuum analyze )，如果您在填充表后立即需要一个好的执行计划，那么您显着更改了行数。

Quote from the manual

Whenever you have significantly altered the distribution of data within a table, running ANALYZE is strongly recommended. This includes bulk loading large amounts of data into the table. Running ANALYZE (or VACUUM ANALYZE) ensures that the planner has up-to-date statistics about the table. With no statistics or obsolete statistics, the planner might make poor decisions during query planning, leading to poor performance on any tables with inaccurate or nonexistent statistics