时间戳

首页 > 解决方案 > 获取过去 24 小时内未更新的行

postgresql - 获取过去 24 小时内未更新的行

问题描述

我有一个大表（40+ 百万条记录），其结构如下：

CREATE TABLE collected_data(
    id TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY(id, status),
    blob JSONB,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

我需要获取所有（或至少 100,000 条）updated_at超过 24 小时、具有某种状态且具有不为空的 blob 的记录。

所以查询变为：

SELECT
    id
FROM
    collected_data
WHERE
    status = 'waiting'
    AND blob IS NOT NULL
    AND updated_at < NOW() - '24 hours'::interval
LIMIT 100000;

这导致执行计划如下：

Limit  (cost=0.00..234040.07 rows=100000 width=12)
  ->  Seq Scan on collected_data  (cost=0.00..59236150.00 rows=25310265 width=12)
"        Filter: ((blob IS NOT NULL) AND (type = 'waiting'::text) AND (updated_at >= (now() - '24:00:00'::interval)))"

它几乎总是会导致全表扫描，这意味着某些查询非常慢。

我曾尝试创建类似的索引，CREATE INDEX idx_special ON collected_data(status, updated_at);但没有帮助。

有什么办法可以让这个查询更快吗？

标签： postgresql

解决方案

计划者认为 25,310,265 行将满足您的条件，因此它认为通过 seq 扫描仅获取 100,000 行然后提前停止会被宠坏。如果真的没有那么多，或者有那么多但它们都聚集在表格的错误部分，那么这实际上不会那么快。如果在选择了其中的 100,000 个后，您接下来要做的是以不再符合标准的方式更新它们，则尤其可能出现这种情况。因为那时你必须不断地走过那些曾经有资格的人的积累残余物，才能找到下一批。

您可以通过在查询中添加“order by updated_at”来鼓励它使用索引。您还可以通过创建部分索引CREATE INDEX ON collected_data(status, updated_at) where blob is not null或CREATE INDEX ON collected_data(updated_at) where status='waiting' and blob is not null.

推荐阅读