在PostgreSQL中,我在tickets
表的日期字段上有一个索引。当我将字段与now()
进行比较时,查询非常有效:
# explain analyze select count(1) as count from tickets where updated_at > now();
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=90.64..90.66 rows=1 width=0) (actual time=33.238..33.238 rows=1 loops=1)
-> Index Scan using tickets_updated_at_idx on tickets (cost=0.01..90.27 rows=74 width=0) (actual time=0.016..29.318 rows=40250 loops=1)
Index Cond: (updated_at > now())
Total runtime: 33.271 ms
如果我尝试将其与now()
减去间隔进行比较,它会走下坡路并使用位图堆扫描。
# explain analyze select count(1) as count from tickets where updated_at > (now() - '24 hours'::interval);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=180450.15..180450.17 rows=1 width=0) (actual time=543.898..543.898 rows=1 loops=1)
-> Bitmap Heap Scan on tickets (cost=21296.43..175963.31 rows=897368 width=0) (actual time=251.700..457.916 rows=924373 loops=1)
Recheck Cond: (updated_at > (now() - '24:00:00'::interval))
-> Bitmap Index Scan on tickets_updated_at_idx (cost=0.00..20847.74 rows=897368 width=0) (actual time=238.799..238.799 rows=924699 loops=1)
Index Cond: (updated_at > (now() - '24:00:00'::interval))
Total runtime: 543.952 ms
是否有更有效的方法来查询使用日期算法?
第一个查询期望找到 rows=74
,但实际上找到了rows=40250
。
第二个查询期望找到 rows=897368
,但实际上找到了rows=924699
。
当然,处理23倍的行需要更多的时间。所以你的实际时间并不奇怪。
updated_at > now()
数据的统计信息已经过时。运行:
ANALYZE tickets;
并重复查询。你真的有updated_at > now()
的数据吗?听起来不对。
然而,对于最近更改的数据来说,统计数据是过时的,这并不奇怪。这是事物的逻辑。如果您的查询依赖于当前统计数据,则必须在运行查询之前运行ANALYZE
。
也测试(只在你的会话):
SET enable_bitmapscan = off;
和重复你的第二个查询查看时间没有位图索引扫描。
为什么位图索引扫描更多行?
普通的索引扫描按照索引中的顺序从堆中获取行。这很简单,没有开销。对于少数行来说速度很快,但是随着行数的增加,最终可能比位图索引扫描更昂贵。
位图索引扫描在查找表之前从索引中收集行。如果多行驻留在同一数据页上,就可以避免重复访问,并且可以大大提高速度。行越多,位图索引扫描节省时间的机会就越大。
对于更多的行(大约占表的5%,很大程度上取决于实际数据),计划器切换到顺序扫描表的,根本不使用索引。
最优的是在Postgres 9.2中引入的索引扫描。这只有在满足一些先决条件的情况下才有可能。如果所有相关的列都包含在索引中,则索引类型支持它,并且可见性映射表明数据页上的所有行对所有事务都是可见的,那么该页不必从堆(表)中取出,索引中的信息就足够了。
这个决定取决于你的统计数据(Postgres期望找到多少行及其分布)和成本设置,最重要的是random_page_cost
, cpu_index_tuple_cost
和effective_cache_size
。