sql - Postgres 中大 INSERT 后查询缓慢

Question

我们在 RedHat 中使用 Postgres 9.2。我们有一个类似于以下的表:

CREATE TABLE BULK_WI (
    BULK_ID INTEGER NOT NULL,
    USER_ID VARCHAR(20) NOT NULL,
    CHUNK_ID INTEGER,
    STATE VARCHAR(16),
    CONSTRAINT BASE_BULK_WI_PK PRIMARY KEY(BULK_ID,USER_ID)
);
CREATE INDEX BASE_BULK_WI_IDX01 ON BULK_WI(STATE, CHUNK_ID);

作为批处理作业的一部分，我们首先使用新的 BULK_ID 向表中添加一些行。所有新记录的 CHUNK_ID = NULL，STATE = 'PENDING'。插入的行数在 500K 到 150 万之间。发生这种情况时表的大小超过 15M 记录。

插入后，我们开始分块处理表。为此，我们首先为下一个 block 选择一些项目，然后处理它们。通过以下查询选择项目:

UPDATE BASE_BULK_WI wi SET wi.STATE = 'PROCESSING', wi.CHUNK_ID = $1 
WHERE wi.STATE='PENDING' AND wi.BULK_ID = $2 
AND wi.USER_ID IN 
    (SELECT USER_ID FROM BASE_BULK_WI WHERE BULK_ID = $3 
     AND CHUNK_ID IS NULL AND STATE='PENDING' LIMIT $4 FOR UPDATE)

$1 随着每次 block 迭代而增加，$2 和 $3 始终相同(刚刚插入的 BULK_ID)，$4 通常在 2,000 到 10,000 之间。

问题是前几个 block 需要很长时间才能更新。例如，对于 2000 的限制，大多数更新发生在 1 秒内，而前几个更新需要 2 分钟以上。

我们正在尝试了解为什么会发生这种情况以及如何解决它。阅读文档后:

To ensure data page consistency, the first modification of a data page after each checkpoint results in logging the entire page content.

我们认为这与检查站和 WAL 有关，但我们无法确定。

有什么建议吗？

Answer 1

ANALYZE

autovacuum daemon也会自动运行 ANALYZE，但需要一些时间才能启动。如果您在巨大的 INSERT 之后立即运行 UPDATE，请确保运行 ANALYZE 在两者之间更新统计信息，否则查询规划器可能会做出错误的选择。

FROM 子句而不是 IN

IN 对于大型子查询来说是出了名的慢。这可能会表现更好:

UPDATE base_bulk_wi wi
SET   wi.state = 'PROCESSING'
    , wi.chunk_id = $1 
FROM (
    SELECT user_id, bulk_id 
    FROM   base_bulk_wi
    WHERE  bulk_id = $3 
    AND    chunk_id IS NULL
    AND    state = 'PENDING'
    LIMIT  $4
    FOR    UPDATE
    ) x 
WHERE wi.bulk_id = x.bulk_id
AND   wi.user_id = x.user_id;

索引

一个partial index这样应该最适合您的情况:

CREATE INDEX base_bulk_wi_partial_idx01 ON bulk_wi(chunk_id)
WHERE state = 'PENDING' AND chunk_id IS NULL;

为了获得最佳性能，请在INSERT之后创建此索引。如果它已经存在，那么在之前删除并在之后重新创建可能会有所帮助。

有人可能认为在此索引中包含 bulk_id 是个好主意，以便在 Postgres 9.2 中进行仅索引扫描。但由于子查询中有 FOR UPDATE ，所以这无论如何都不是一个选项。

如果 user_id 是 integer 而不是 varchar，将会有很大帮助。 (用户表的外键。)除了更快的处理和更小的表之外，两个整数完全适合最小大小的索引。您的主键将会受益匪浅。