Postgres中大量插入后的慢查询

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到1.5M之间。当发生这种情况时，表的大小超过15M条记录。

插入完成后，我们开始以块的方式处理表。为此，我们首先选择下一个块的一定数量的项目，然后处理它们。选择这些项目的查询语句如下：

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会增加，$2和$3始终相同（刚插入的BULK_ID），$4通常在2,000到10,000之间。

问题在于前几个块更新的时间很长。例如，对于2000的限制，大多数更新在不到1秒的时间内完成，而前几个更新需要超过2分钟的时间。

我们正在努力了解为什么会出现这种情况以及如何修复它。阅读文档后：

为确保数据页面一致性，在每个检查点之后对数据页面进行的第一次修改会导致记录整个页面内容。

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

有什么建议吗？

- xpapad

---

也许将您的postgre.conf文件添加进来会有所帮助，也许您的一些设置对于您正在处理的工作负载来说太低了。 - Kuberchaun

@JustBob postgresql.conf 可能是指的吧？我从来没有听说过 "postgre.conf" :-P - Craig Ringer

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;

索引

对于您的情况，像这样的部分索引应该是最优选择：

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

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

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

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

- Erwin Brandstetter

---

确实，我们尝试过在插入后手动运行ANALYZE，似乎有所帮助。但是我们不知道是否是ANALYZE有用，还是因为我们给数据库时间去做它正在做的事情（例如将页面刷新到磁盘等）。我们不确定的原因是新插入的元素似乎只占总元素的一小部分（5-7%），这足以证明需要进行显式分析吗？ - xpapad

@xpapad：很可能是的。它可能只占总行数的几个百分点，但是却是相关行的巨大块。统计数据包括的不仅仅是行数。例如值的频率.. - Erwin Brandstetter

Answer 2

1) 更改条件的顺序，不确定但我认为索引没有被使用：

AND CHUNK_ID IS NULL AND STATE='PENDING'

变成

STATE='PENDING' AND CHUNK_ID IS NULL

以及

WHERE wi.STATE='PENDING' AND wi.BULK_ID = $2

变成

WHERE wi.BULK_ID = $2 AND wi.STATE='PENDING'

2) 如果您不使用列CHUNK_ID进行select，那么我建议将条件WHERE CHUNK_ID IS NULL添加到索引BASE_BULK_WI_IDX01中 - 这将导致索引更小，因为它仅用于更新。

此外，State和User_ID列不需要是varchar - 您应该使用较小的类型，如enum或integer。索引会更小，保存和读取数据更快，使用的磁盘、处理器和其他资源也会更少。

- Wojtas

---

条件的顺序不重要。查询规划器会根据需要应用它们。SQL 不是一种过程性语言！尽管如此，您其他的观点都是好的。 - Erwin Brandstetter

我根据自己在Postgres 8.4上的经验撰写了有关索引的建议，因此如果规划器在更新版本中工作方式不同，那么了解这一点是很好的。 - Wojtas

显然，我指的是1)更改条件顺序。你在2)关于索引的建议很好（还有一些改进的空间）。 - Erwin Brandstetter