我有一个定义了以下表的dbfiddle demo：

CREATE TABLE status_table (
  base_name  text      NOT NULL
, version    smallint  NOT NULL
, ref_time   int       NOT NULL
, processed  bool      NOT NULL
, processing bool      NOT NULL
, updated    int       NOT NULL
, PRIMARY KEY (base_name, version)
);

CREATE TABLE data_table (
  location  text      NOT NULL
, param_id  text      NOT NULL
, ref_time  int       NOT NULL
, fcst_time smallint  NOT NULL
, timestamp int       NOT NULL
, value     text      NOT NULL
, PRIMARY KEY (location, param_id, ref_time, fcst_time)
);

未定义其他索引。
注意，对于data_table中的每一行，它是ref_time + fcst_time = timestamp的情况，我知道这不是理想的，但这是它发展的方式。（对于一批数据）且fcst_time是偏移时间，给出数据记录的实际timestamp（对于每一批，存在数据记录的时间序列，其从ref_time开始，并且具有逐渐增加的timestamp或fcst_time和单个数据value）。
然后，我使用以下复杂的查询从data_table中删除选定的行。（sel1和sel2），然后删除不在sel1中也不在sel2中的data_table的所有行。
顺便说一句，sel1基本上对应于我从data_table阅读数据的查询（尽管我在这样做时限制为特定的location，因此它相当快）...因此sel1只是在查询中 * 可能 * 选择的行的集合...我希望保留这些行而不是删除它们。
sel2是那些与仍在处理的数据相关的行，所以我也需要保留这些行。
因此，请记住以下查询：

WITH
  stats AS (
    SELECT ref_time
      , max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
      , (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
    FROM status_table
    GROUP BY ref_time
  ),
  min_ts AS (
    SELECT ref_time FROM stats WHERE ready ORDER BY ref_time DESC LIMIT 1
  ),
  sel1 AS (
    -- we need to keep all of these rows (don't delete)
    SELECT DISTINCT ON (d.location, d.timestamp, d.param_id)
      d.location, d.param_id, d.ref_time, d.fcst_time
    FROM data_table AS d
    INNER JOIN stats s USING (ref_time)
    WHERE s.ready AND d.timestamp >= (SELECT ref_time FROM min_ts)
    ORDER BY d.location, d.timestamp, d.param_id, d.ref_time DESC
  ),
  sel2 AS (
    -- we also need to keep all of these rows (don't delete)
    SELECT
      d.location, d.param_id, d.ref_time, d.fcst_time
    FROM data_table AS d
    INNER JOIN stats AS s USING (ref_time)
    WHERE NOT s.settled
  )
DELETE FROM data_table 
  WHERE
    (location, param_id, ref_time, fcst_time) NOT IN (SELECT location, param_id, ref_time, fcst_time FROM sel1)
  AND
    (location, param_id, ref_time, fcst_time) NOT IN (SELECT location, param_id, ref_time, fcst_time FROM sel2);

但是我发现在我的实际数据库中这是非常慢的。我知道我需要优化我的索引，可能还有主键，并且尝试了各种各样的方法都没有任何真实的的成功，所以我有点迷路了。
下面是在我的实际数据库上执行上述查询的EXPLAIN输出：

QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Delete on data_table  (cost=4002975.62..118180240066541.86 rows=0 width=0)
   CTE stats
     ->  HashAggregate  (cost=234.02..234.21 rows=4 width=6)
           Group Key: status_table.ref_time
           ->  Seq Scan on status_table  (cost=0.00..164.01 rows=7001 width=9)
   ->  Seq Scan on data_table  (cost=4002741.41..118180240066307.66 rows=19567628 width=6)
         Filter: ((NOT (SubPlan 3)) AND (NOT (SubPlan 4)))
         SubPlan 3
           ->  Materialize  (cost=4002741.30..4293628.93 rows=7691318 width=18)
                 ->  Subquery Scan on sel1  (cost=4002741.30..4210105.34 rows=7691318 width=18)
                       ->  Unique  (cost=4002741.30..4133192.16 rows=7691318 width=22)
                             InitPlan 2 (returns $1)
                               ->  Limit  (cost=0.09..0.09 rows=1 width=4)
                                     ->  Sort  (cost=0.09..0.10 rows=2 width=4)
                                           Sort Key: stats.ref_time DESC
                                           ->  CTE Scan on stats  (cost=0.00..0.08 rows=2 width=4)
                                                 Filter: ready
                             ->  Sort  (cost=4002741.20..4035353.91 rows=13045086 width=22)
                                   Sort Key: d.location, d."timestamp", d.param_id, d.ref_time DESC
                                   ->  Hash Join  (cost=0.11..1925948.51 rows=13045086 width=22)
                                         Hash Cond: (d.ref_time = s.ref_time)
                                         ->  Seq Scan on data_table d  (cost=0.00..1697659.40 rows=26090171 width=22)
                                               Filter: ("timestamp" >= $1)
                                         ->  Hash  (cost=0.08..0.08 rows=2 width=4)
                                               ->  CTE Scan on stats s  (cost=0.00..0.08 rows=2 width=4)
                                                     Filter: ready
         SubPlan 4
           ->  Materialize  (cost=0.11..2611835.48 rows=39135256 width=18)
                 ->  Hash Join  (cost=0.11..2186850.21 rows=39135256 width=18)
                       Hash Cond: (d_1.ref_time = s_1.ref_time)
                       ->  Seq Scan on data_table d_1  (cost=0.00..1501983.12 rows=78270512 width=18)
                       ->  Hash  (cost=0.08..0.08 rows=2 width=4)
                             ->  CTE Scan on stats s_1  (cost=0.00..0.08 rows=2 width=4)
                                   Filter: (NOT settled)
 JIT:
   Functions: 45
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(37 rows)