代码之家 › 专栏 › 技术社区 › Josh Smeaton

如何让postgres统计数据保持最新,以鼓励选择最佳索引

postgresql

Josh Smeaton · 技术社区 · 4 年前

我有一个通知表,其中包含大约7000000条记录,相关列包括:

id: integer
time_created: timestamp with time zone
device_id: integer (foreign key to another table)

以及指数:

CREATE INDEX notifications_device ON notifications (device_id);
CREATE INDEX notifications_time ON notifications (time_created);

我的问题是:

SELECT COUNT(*) AS "__count"
FROM "notifications"
WHERE ("notifications"."device_id" IN (
    SELECT "id" FROM device WHERE (
        device."device_type" = 'iOS' AND
        device."registration_id" IN (
             'XXXXXXX',
             'YYYYYYY',
             'ZZZZZZZ'
        )
    )
)
AND "notifications"."time_created" BETWEEN
    '2020-10-26 00:00:00' AND '2020-10-26 17:33:00')
;

对于一天中的大部分时间,此查询将使用设备id上的索引,并将在1ms内运行。但是,一旦非常快地将表写入(发送日志记录通知),规划器就会切换到使用上的索引 time_created 查询结果是300毫秒。

经营 ANALYZE NOTIFICATIONS 立即修复问题,并将索引打开 device_id

该表被删减到每晚最后30天,这就是为什么在表上有一个单独的索引 创建时间 专栏。

我是否可以修复此问题,以便计划员始终选择上的索引 设备id ,通过强制postgres在此表上保持更好的统计数据?或者,我可以重新编写 创建时间 time_created < ..30 days ago.. time_created BETWEEN midnight and now

Rows Removed by Filter      = 20926
Shared Hit Blocks           = 143934
Plan Rows                   = 38338
Actual Rows                 = 84479

良好计划(设备id):

Rows Removed by Filter      = 95
Shared Hit Blocks           = 34
Plan Rows                   = 1
Actual Rows                 = 0

1 回复 | 直到 4 年前

Tim Biegeleisen 4 年前

实际上,我建议在市场上建立一个综合指数 notifications 表:

CREATE INDEX idx1 ON notifications (device_id, time_created);

该索引将涵盖当前版本中的两个限制 WHERE 条款我还要在 device 表:

CREATE INDEX idx2 ON device (device_type, registration_id, id);

子查询的子句。它还包括 id 柱,以完全覆盖 SELECT 条款如果使用,Postgres可以更快速地计算数据库上的子查询桌子

您还可以通过更改列顺序来处理上述两个索引的一些细微变化。例如,您还可以尝试:

CREATE INDEX idx1 ON notifications (time_created, device_id);
CREATE INDEX idx2 ON device (registration_id , device_type, id);

jjanes 4 年前

但是,这是一个问题吗好的为什么要有索引?如果夜间查询需要更长的时间,这有关系吗?事实上,对于删除表的3%,它是否甚至使用了索引?如果使用了索引,是否真的会加快速度?也许你可以用分区来代替索引,或者什么都不用。

在任何情况下,您都可以使用这个丑陋的黑客来强制它不使用索引:

AND "notifications"."time_created" + interval '0 seconds' BETWEEN '2020-10-26 00:00:00' AND '2020-10-26 17:33:00'