sql - PostgreSQL 索引查询速度不一致

Question

我们在同一个表上有 2 个相同的( double )列，2 个相同的索引运行 2 个相同的查询。然而其中一个比另一个快了将近 10*。这是什么原因造成的？

1) SELECT MIN("reports"."longitude") AS min_id FROM "reports" WHERE (area2 = 18)

2) SELECT MIN("reports"."latitude") AS min_id FROM "reports" WHERE (area2 = 18)

1 次在 28 毫秒内运行，2 次在 >300 毫秒内运行

这里是“解释”:
1)

Result  (cost=6.07..6.08 rows=1 width=0)"
InitPlan 1 (returns $0)"
  ->  Limit  (cost=0.00..6.07 rows=1 width=8)"
      ->  Index Scan using longitude on reports  (cost=0.00..139617.49 rows=22983 width=8)"
            Index Cond: (longitude IS NOT NULL)"
            Filter: (area2 = 18)"

2)

Result  (cost=5.95..5.96 rows=1 width=0)"
InitPlan 1 (returns $0)"
  ->  Limit  (cost=0.00..5.95 rows=1 width=8)"
      ->  Index Scan using latitude on reports  (cost=0.00..136754.07 rows=22983 width=8)"
            Index Cond: (latitude IS NOT NULL)"
            Filter: (area2 = 18)"

---

此处要求的是解释分析输出...

1)

Result  (cost=6.07..6.08 rows=1 width=0) (actual time=10.992..10.993 rows=1 loops=1)"
InitPlan 1 (returns $0)"
    ->  Limit  (cost=0.00..6.07 rows=1 width=8) (actual time=10.985..10.986 rows=1 loops=1)"
          ->  Index Scan using longitude on reports  (cost=0.00..139617.49 rows=22983 width=8) (actual time=10.983..10.983 rows=1 loops=1)"
                Index Cond: (longitude IS NOT NULL)"
                Filter: (area2 = 18)"
Total runtime: 11.033 ms"

2)

 Result  (cost=5.95..5.96 rows=1 width=0) (actual time=259.749..259.749 rows=1 loops=1)"
InitPlan 1 (returns $0)"
    ->  Limit  (cost=0.00..5.95 rows=1 width=8) (actual time=259.740..259.740 rows=1 loops=1)"
          ->  Index Scan using latitude on reports  (cost=0.00..136754.07 rows=22983 width=8) (actual time=259.739..259.739 rows=1 loops=1)"
                Index Cond: (latitude IS NOT NULL)"
                Filter: (area2 = 18)"
Total runtime: 259.789 ms"
---------------------

这是怎么回事？如何让第二个查询正常运行并快速运行？据我所知，这两种设置是相同的。

Answer 1

首先，无法保证索引可以加快查询速度。其次，在做性能考虑时，需要多次运行每个查询。加载索引和将页面加载到缓存中会产生开销，这会影响查询的长度。

我不是 Postgres 方面的专家，但考虑到这一点，我并不感到惊讶。

查询计划循环遍历索引，找到匹配 area2 = 18 的对应行，然后希望在第一个停止(它正在使用索引，因此它可以从最低值开始向上移动)。这是对其工作原理的猜测；我不知道 Postgres 正是这样做的。

无论如何，发生的情况是该区域比纬度索引的开头更接近经度索引的开头。因此，它首先在那里找到第一个匹配的记录。如果这个解释是正确的，则表明与数据库中的其他内容相比，该区域相对西部(较低经度)和相对北部(较高纬度)。

顺便说一句，假设有很多区域，使用 Area2 上的索引可能会获得更好的结果。