时间相关的postgres查询速度

我有一种情况，选择查询可能需要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表填充后立即运行查询，它将为您提供计划，如顶部所示，查询将花费很长时间。如果您等待几分钟，则该计划将是带有hash-join的较低计划。较高的成本估算值小于较低的成本估算值。

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

我什至通过将tmp_variant_filtered表复制到另一个表（例如“ test”）中进行了实验。如果我在复制后立即运行查询（通常会有几秒钟的延迟），那么我就陷入了困境。杀死当前的工作，等待几分钟，相同的查询很快就会出现。

很久以前，我正在进行查询调整。现在我才开始重新拾起它。我正在阅读并试图理解为什么postgres会有这种行为。希望专家给出提示。