sql - 使用索引或位图索引扫描对时间戳进行高效的 PostgreSQL 查询？

Question

在 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

是否有更有效的使用日期算法进行查询的方法？

Answer 1

第一个查询期望找到 rows=74，但实际上找到了 rows=40250。
第二个查询期望找到 rows=897368，实际上找到了 rows=924699。

当然，处理 23 倍的行数需要更多的时间。所以你的实际时间并不奇怪。

使用 updated_at > now() 的数据统计信息已过时。运行:

ANALYZE tickets;

并重复您的查询。你真的有 updated_at > now() 的数据吗？这听起来不对。

然而，对于最近更改的数据，统计数据已过时也就不足为奇了。这就是事物的逻辑。如果您的查询依赖于当前的统计数据，您必须在运行查询之前运行 ANALYZE。

同时测试(仅在您的 session 中):

SET enable_bitmapscan = off;

并重复您的第二个查询以查看没有位图索引扫描的时间。

为什么位图索引扫描更多行？

一个普通的索引扫描从堆中按顺序从索引中找到行。这很简单，愚蠢且没有开销。对于少数行来说速度很快，但最终可能比行数越来越多的位图索引扫描更昂贵。

位图索引扫描在查找表之前从索引中收集行。如果多行驻留在同一个数据页上，则可以减少重复访问，并可以大大加快速度。行越多，机会越大，位图索引扫描将节省时间。

对于更多的行(表的大约 5%，很大程度上取决于实际数据)，规划器切换到表的顺序扫描并且根本不使用索引。

最佳的是 index only scan ，在 Postgres 9.2 中引入。这只有在满足某些先决条件的情况下才有可能。如果所有相关列都包含在索引中，索引类型支持它并且可见性映射表明数据页上的所有行对所有事务都是可见的，则不必从堆(表)中获取该页并且索引中的信息就足够了。

决定取决于您的统计数据(Postgres 期望找到多少行及其分布)和 cost settings ，最重要的是 random_page_cost、cpu_index_tuple_cost 和 effective_cache_size。