我们有一个数据库,其中一张表包含需要保存不同时间(通常在几十分钟到两周之间)的序列化临时数据。我们还有一个低优先级的后台进程,可以从表中删除旧行。后台进程在一个事务中最多删除 1000 行:
delete from temporarydata
where id in (
select id from temporarydata
where (created + ttl) <= 1553755330 limit 1000
)
1553755330
示例中的 是自 UNIX 纪元以来的当前秒数,包含created
自 UNIX 纪元以来添加数据ttl
的秒数,并且包含数据应保持活动状态的秒数。
从技术上讲,这确实有效,但临时数据中有大约 2M 行,并且子选择变得非常慢,因为总和需要对表进行顺序扫描以找到所有匹配的行。这会导致数据库上的额外后台负载。
> explain (analyze,verbose,timing,buffers) select id from temporarydata
where (created + ttl) <= 1553755330 limit 1000
Limit (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1)
Output: id
Buffers: shared hit=3068 read=230500
-> Seq Scan on public.temporarydata (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1)
Output: id
Filter: ((temporarydata.created + temporarydata.ttl) <= 1553755330)
Rows Removed by Filter: 1916405
Buffers: shared hit=3068 read=230500
Planning time: 0.402 ms
Execution time: 6735.849 ms
我宁愿只添加一个新索引,该索引始终包含created + ttl
PostgreSQL 能够自动用于此查询的总和。这可以通过高性能实现吗?
(我正在考虑重写应用程序代码来保存created
而expires
不是ttl
where expires
is created
+ ttl
。然后我计算逻辑ttl
作为这些值的差异。我认为应用程序不会ttl
单独发出繁重的查询。)
如果您对每条记录都使用了相同的 TTL,您可以通过简单地将 TTL 移动到比较的右侧来避免功能索引(感谢
jjanes
更正):优化器将只计算一次差异,然后将其用于索引
created
列。如果您需要为不同的记录使用不同的 TTL,您可以存储的不是
created -- ttl
一对而是预先计算的created -- expires
一个您可以使用功能索引:
我认为重构表以存储过期将是一个好主意。如果您不想这样做,那么您可以制作一个表达式 index
on temporarydata ((created + ttl))
。然而,让它使用这个索引可能需要一些鼓励,因为统计系统可能无法自然地为它提供足够好的估计。将 ORDER BY 添加到您的子选择应该会提供这种鼓励:
(另外,你想先删除最过期的似乎是有道理的。事实上,我不知道你为什么想要 LIMIT 。)