我正在尝试获取表中最近的行.我有一个简单的时间戳created_at索引.当我查询ORDER BY created_at DESC LIMIT 1时,它需要比我想象的要多得多(我的机器上36k行约50ms).
EXPLAIN声称它使用向后索引扫描,但我确认将索引更改为(created_at DESC)不会更改查询规划器中的简单索引扫描的成本.
如何优化此用例？
运行postgresql 9.2.4.
编辑：
# EXPLAIN SELECT * FROM articles ORDER BY created_at DESC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.58 rows=1 width=1752)
-> Index Scan Backward using index_articles_on_created_at on articles (cost=0.00..20667.37 rows=35696 width=1752)
(2 rows)
解决方法
假设我们正在处理一个大表,partial index可能会有所帮助：
CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC) WHERE created_at > '2013-09-15 0:0'::timestamp;
正如你已经发现的那样：下降或上升在这里几乎不重要. Postgres可以以几乎相同的速度向后扫描(例外情况适用于多列索引).
查询使用此索引：
SELECT * FROM tbl WHERE created_at > '2013-09-15 0:0'::timestamp -- matches index ORDER BY created_at DESC LIMIT 1;
这里的要点是使索引更小,因此它应该更容易缓存和维护.
>您需要选择一个保证小于最新时间戳的时间戳.
>您应该不时重新创建索引以切断旧数据.
>条件需要是无法修改的.
因此,一次性效应会随着时间的推移而恶化.具体问题是硬编码条件：
WHERE created_at > '2013-09-15 0:0'::timestamp
自动化
您可以不时手动更新索引和查询.或者你在这样的函数的帮助下自动化它：
CREATE OR REPLACE FUNCTION f_min_ts() RETURNS timestamp LANGUAGE sql IMMUTABLE AS $$SELECT '2013-09-15 0:0'::timestamp$$
指数：
CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC); WHERE created_at > f_min_ts();
查询：
SELECT * FROM tbl WHERE created_at > f_min_ts() ORDER BY created_at DESC LIMIT 1;
使用cron作业或某些基于触发器的事件自动进行娱乐.您的查询现在可以保持不变.但是您需要在更改后以任何方式使用此函数重新创建所有索引.只需删除并创建每个.
首先……
…测试你是否真的用这个打到瓶颈.
尝试一下简单的DROP索引……;创建索引……完成工作.那么你的索引可能已经臃肿了.您的autovacuum设置可能已关闭.
或者尝试VACUUM FULL ANALYZE以获得整个表格以及原始状态的指数并再次检查.
其他选项包括usual general performance tuning和covering indexes,具体取决于您从表中实际检索的内容.
