我正在 Postgres 15 中使用警报表上的 Timescale 扩展执行以下查询,以获取用户名的最新警报。
EXPLAIN ANALYZE
SELECT *
FROM alerts_alerts
WHERE username IN ('<username_here>')
ORDER BY timestamp DESC
LIMIT 1
对于大多数用户名,查询执行速度很快,不到 150 毫秒。但是,对于某些用户名,需要更长的时间。几乎所有数据库都有大约相同数量的警报,大约 450 个,并且大多数数据库都有相当新的数据,全部在过去 6 个月内。
这是Explain Analyze
有问题的用户名:
"Limit (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.346..36129.370 rows=1 loops=1)"
" -> Custom Scan (ChunkAppend) on alerts_alerts (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.344..36129.368 rows=1 loops=1)"
" Order: alerts_alerts.""timestamp"" DESC"
" -> Index Scan using _hyper_1_234_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_234_chunk (cost=0.29..2262.68 rows=1 width=89) (actual time=5.795..5.796 rows=0 loops=1)"
" Filter: ((username)::text = 'username_long_query'::text)"
" Rows Removed by Filter: 30506"
" -> Index Scan using _hyper_1_233_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_233_chunk (cost=0.29..4337.82 rows=1 width=91) (actual time=11.112..11.112 rows=0 loops=1)"
" Filter: ((username)::text = 'username_long_query'::text)"
" Rows Removed by Filter: 59534"
[ ... Cut redundant log lines here ... ]
" -> Index Scan using _hyper_1_156_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_156_chunk (cost=0.42..11418.54 rows=2591 width=80) (never executed)"
" Filter: ((username)::text = 'username_long_query'::text)"
" -> Index Scan using _hyper_1_155_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_155_chunk (cost=0.29..7353.95 rows=749 width=84) (never executed)"
" Filter: ((username)::text = 'username_long_query'::text)"
[ ... Cut redundant log lines here ... ]
"Planning Time: 13.154 ms"
"Execution Time: 36129.923 ms"
现在,这是Explain Analyze
快速执行的用户名:
"Limit (cost=471.73..471.73 rows=1 width=458) (actual time=1.672..1.691 rows=1 loops=1)"
" -> Sort (cost=471.73..472.76 rows=414 width=458) (actual time=1.671..1.689 rows=1 loops=1)"
" Sort Key: _hyper_1_234_chunk.""timestamp"" DESC"
" Sort Method: top-N heapsort Memory: 27kB"
" -> Append (cost=0.29..469.66 rows=414 width=457) (actual time=1.585..1.654 rows=210 loops=1)"
" -> Index Scan using _hyper_1_234_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_234_chunk (cost=0.29..2.49 rows=1 width=372) (actual time=0.006..0.007 rows=0 loops=1)"
" Index Cond: ((username)::text = 'username_value'::text)"
" -> Index Scan using _hyper_1_233_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_233_chunk (cost=0.29..2.37 rows=1 width=385) (actual time=0.006..0.006 rows=0 loops=1)"
" Index Cond: ((username)::text = 'username_value'::text)"
[ ... Cut redundant log lines here ... ]
" -> Seq Scan on _hyper_1_83_chunk (cost=0.00..1.12 rows=1 width=504) (actual time=0.013..0.013 rows=0 loops=1)"
" Filter: ((username)::text = 'username_value'::text)"
" Rows Removed by Filter: 10"
" -> Seq Scan on _hyper_1_81_chunk (cost=0.00..1.12 rows=1 width=504) (actual time=0.009..0.009 rows=0 loops=1)"
" Filter: ((username)::text = 'username_value'::text)"
" Rows Removed by Filter: 10"
"Planning Time: 899.811 ms"
"Execution Time: 2.613 ms"
初步研究建议对数据库表进行维护。执行vacuum命令后,再次执行查询,但结果没有变化。
还应该指出的是,还有其他用户名使用“有问题的”规划,但执行时间仍然很快。
不确定如何解决查询执行时间的这种差异。添加另一个索引可能很有用,但由于我是 PostgreSQL 的新手,我目前不确定最好的方法。