优化INSERT / UPDATE / DELETE操作

Question

我想知道以下脚本是否可以某种方式进行优化。它确实在磁盘上写了很多东西，因为它可能删除了最新的行并重新插入它们。我正在考虑应用“在重复键更新中插入…”之类的东西，并发现了单行更新的一些可能性，但我不知道如何在的上下文中应用它INSERT INTO ... SELECT query。

CREATE OR REPLACE FUNCTION update_member_search_index() RETURNS VOID AS $$
DECLARE
   member_content_type_id INTEGER;
BEGIN
   member_content_type_id :=
      (SELECT id FROM django_content_type
       WHERE app_label='web' AND model='member');

   DELETE FROM watson_searchentry WHERE content_type_id = member_content_type_id;

   INSERT INTO watson_searchentry (engine_slug, content_type_id, object_id
                                 , object_id_int, title, description, content
                                 , url, meta_encoded)
   SELECT 'default',
         member_content_type_id,
         web_member.id,
         web_member.id,
         web_member.name,
         '',
         web_user.email||' '||web_member.normalized_name||' '||web_country.name,
         '',
         '{}'
   FROM web_member
   INNER JOIN web_user ON (web_member.user_id = web_user.id)
   INNER JOIN web_country ON (web_member.country_id = web_country.id)
   WHERE web_user.is_active=TRUE;
END;
$$ LANGUAGE plpgsql;

编辑：
的架构web_member，watson_searchentry，web_user，web_country：http://pastebin.com/3tRVPPVi。

要点是更新列title和content中的内容watson_searchentry。表上有一个触发器，可search_tsv根据这些列设置列的值。

(content_type_id, object_id_int)inwatson_searchentry是表中的唯一对，但是atm索引不存在（没有用）。

该脚本最多应每天运行一次，以完全重建搜索索引，并且偶尔在导入一些数据之后运行。

Answer 1

修改表定义

如果您确实需要这些列，NOT NULL并且确实需要将字符串'default'作为default的默认值engine_slug，那么我建议您引入列默认值：

COLUMN           |          TYPE           |      Modifiers
-----------------+-------------------------+---------------------
 id              | INTEGER                 | NOT NULL DEFAULT ... 
 engine_slug     | CHARACTER VARYING(200)  | NOT NULL **DEFAULT 'default'**
 content_type_id | INTEGER                 | NOT NULL
 object_id       | text                    | NOT NULL
 object_id_int   | INTEGER                 |
 title           | CHARACTER VARYING(1000) | NOT NULL
 description     | text                    | NOT NULL **DEFAULT ''**
 content         | text                    | NOT NULL
 url             | CHARACTER VARYING(1000) | NOT NULL **DEFAULT ''**
 meta_encoded    | text                    | NOT NULL **DEFAULT '{}'**
 search_tsv      | tsvector                | NOT NULL
 ...

DDL语句将是：

ALTER TABLE watson_searchentry ALTER COLUMN  engine_slug DEFAULT 'default';

等等。

然后，您不必每次都手动插入这些值。

另外：object_id text NOT NULL, object_id_int INTEGER？真奇怪 我想你有你的理由…

我将遵循您的更新要求：

主要的一点是要更新的列title和content在watson_searchentry

当然，您 必须 添加 UNIQUE 约束来满足您的要求：

ALTER TABLE watson_searchentry
ADD CONSTRAINT ws_uni UNIQUE (content_type_id, object_id_int)

将使用随附的索引。通过此查询开始。

顺便说一句，我几乎从未varchar(n)在Postgres中使用过。只是text。

使用[修改数据的CTE](http://www.postgresql.org/docs/current/interactive/queries-

with.html#QUERIES-WITH-MODIFYING)查询

可以将其重写为具有数据修改通用表表达式（也称为“可写” CTE）的单个SQL查询。需要Postgres 9.1或更高版本。
此外，此查询仅删除必须删除的内容，并更新可以更新的内容。

WITH  ctyp AS (
   SELECT id AS content_type_id
   FROM   django_content_type
   WHERE  app_label = 'web'
   AND    model = 'member'
   )
, sel AS (
   SELECT ctyp.content_type_id
         ,m.id       AS object_id_int
         ,m.id::text AS object_id       -- explicit cast!
         ,m.name     AS title
         ,concat_ws(' ', u.email,m.normalized_name,c.name) AS content
         -- other columns have column default now.
   FROM   web_user    u
   JOIN   web_member  m  ON m.user_id = u.id
   JOIN   web_country c  ON c.id = m.country_id
   CROSS  JOIN ctyp
   WHERE  u.is_active
   )
, del AS (     -- only if you want to del all other entries of same type
   DELETE FROM watson_searchentry w
   USING  ctyp
   WHERE  w.content_type_id = ctyp.content_type_id
   AND    NOT EXISTS (
      SELECT 1
      FROM   sel
      WHERE  sel.object_id_int = w.object_id_int
      )
   )
, up AS (      -- update existing rows
   UPDATE watson_searchentry 
   SET    object_id = s.object_id
         ,title     = s.title
         ,content   = s.content
   FROM   sel s
   WHERE  w.content_type_id = s.content_type_id
   AND    w.object_id_int   = s.object_id_int
   )
               -- insert new rows
INSERT  INTO watson_searchentry (
        content_type_id, object_id_int, object_id, title, content)
SELECT  sel.*  -- safe to use, because col list is defined accordingly above
FROM    sel
LEFT    JOIN watson_searchentry w1 USING (content_type_id, object_id_int)
WHERE   w1.content_type_id IS NULL;

• 子查询django_content_type始终返回单个值？否则，CROSS JOIN可能会引起麻烦。

• 第一个CTEsel收集要插入的行。注意如何选择 匹配的列名 以简化操作。

• 在CTE中，del我避免删除可以更新的行。

• 在CTE中，up这些行将改为更新。

• 因此，我避免在final中插入之前未删除的行INSERT。

可以轻松地包装到SQL或PL / pgSQL函数中以供重复使用。

不适合大量并发使用。比您拥有的功能要好得多，但仍不能100％健壮地抵抗并发写入。但是，根据您更新的信息，这不是问题。

用DELETE和INSERT替换UPDATE可能会或可能不会昂贵得多。在内部，由于MVCC模型，每个UPDATE都会产生新的行版本。

速度第一

如果您不太在意保留旧行，则较简单的方法可能会更快：删除所有内容并插入新行。同样，包装到plpgsql函数中可以节省一些计划开销。基本上，您的函数做了一些小的简化，并遵循上面添加的默认值：

CREATE OR REPLACE FUNCTION update_member_search_index()
  RETURNS VOID AS
$func$
DECLARE
   _ctype_id int := (
      SELECT id
      FROM   django_content_type
      WHERE  app_label='web'
      AND    model = 'member'
      );  -- you can assign at declaration time. saves another statement
BEGIN
   DELETE FROM watson_searchentry
   WHERE content_type_id = _ctype_id;

   INSERT INTO watson_searchentry
         (content_type_id, object_id, object_id_int, title, content)
   SELECT _ctype_id, m.id, m.id::int,m.name
         ,u.email || ' ' || m.normalized_name || ' ' || c.name
   FROM   web_member  m
   JOIN   web_user    u USING (user_id)
   JOIN   web_country c ON c.id = m.country_id
   WHERE  u.is_active;
END
$func$ LANGUAGE plpgsql;

我什至不使用concat_ws()：它可以安全地使用NULL值并简化代码，但比简单的串联慢一些。

还：

表上有一个触发器，可search_tsv 根据这些列设置列的值。

将逻辑合并到该功能中会更快-如果这是唯一需要触发器的时间。否则，可能不值得大惊小怪。