主页 / dba / 问题 / 187055
Accepted
Hassan Baig
Asked: 2017-09-28 06:15:02 +0800 CST

优化psql上的postgresql维护语句

  • 772

在我维护的类似 reddit 的应用程序（称为Links）中，用户发布超链接，然后其他人可以在所述链接下公开回复。前者保存在名为 postgresql 9.3.10links_link的表中，后者保存在名为links_publicreply.

我的任务是：

  1. 删除link在 30 天时间窗口内创建的所有对象。

  2. 删除publicreply与 (1) 中的对象关联并在 45 天时间窗口内创建的所有对象。

以下是我从 2016 年开始的一个时间窗口的做法：

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

请注意，我links_link以完全相同的方式查询了两次。即在两个delete语句中各一次。现在在 Django ORM（我是本地人）中，我会通过首先links_link分别获取所有必需的对象 ID，然后在所有处理语句中使用它们来优化它。我如何在psql命令行中优化它？

我见过的最中肯的建议是在这个 SO answer中，但似乎该with子句必须在调用它的 SQL 语句中。

换句话说，以下行不通吗？

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;
postgresql postgresql-9.3

1 个回答

  1. Best Answer

    我见过的最中肯的建议是在这个 SO answer 中，但似乎 with 子句必须在它被调用的 SQL 语句中。

    是的，您可以使用修改WITH，即从一个表中删除并将id值返回给查询的其余部分的 CTE，因此它们可用于从第二个表中删除：

    WITH
  links_link_deleted AS
    ( DELETE FROM links_link
        WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
          AND submitted_on < timestamp'2016-08-23 01:00:00'
      RETURNING id
    ) 
DELETE FROM links_publicreply
  WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
    AND submitted_on < timestamp '2016-09-07 01:00:00'
    AND answer_to_id IN
        ( SELECT id
          FROM links_link_deleted
        )
 ;

    换句话说，以下行不通吗？

    那会起作用，但它会是两个语句。

    • 2

相关问题