时间戳

首页 > 解决方案 > 如何优化删除前 N 条记录后的旧记录？

sql - 如何优化删除前 N 条记录后的旧记录？

问题描述

我在工作中使用，postgres并且有一个用作 pkey 索引的表queriesbtree

TABLE queries (
  id serial
  ... other fields
)

我们有一个查询想要删除比前 N 个元素更旧的元素，查询看起来像这样，并且$1是N。

DELETE FROM queries
WHERE id < COALESCE(
  (
    SELECT min(id)
    FROM (
      SELECT id
      FROM queries
      ORDER BY id DESC
      LIMIT $1
    ) ids
  ),
  0
);

此查询的EXPLAIN结果是

Delete on queries  (cost=174908.32..322825.71 rows=272064 width=6)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=174908.31..174908.32 rows=1 width=8)
          ->  Limit  (cost=0.42..168658.31 rows=500000 width=8)
                ->  Index Only Scan Backward using queries_pkey on queries queries_1  (cost=0.42..275314.52 rows=816191 width=8)
  ->  Seq Scan on queries  (cost=0.00..147917.39 rows=272064 width=6)
        Filter: (id < COALESCE($0, '0'::bigint))

在我们的 PROD 环境中，这个查询需要相当长的时间N = 5e5。有一个seq扫描，但我真的不知道它来自哪里，也不知道如何优化它。是不是已经有一个索引了id，而且似乎这id是我们唯一要比较的东西？或者有不同的查询来实现我最初的目标？

标签： sqlpostgresqlquery-optimization

解决方案

您需要EXPLAIN (ANALYZE, BUFFERS)查看时间花在哪里（小心，它会执行DELETE）。

执行计划看起来很合理，如果您遇到少至 500000 行的问题，我敢打赌该表是由未索引的外键引用的。

推荐阅读