PostgreSQL索引查询速度不一致

Question

我们在同一张表上有2个完全相同（双精度）的列，使用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"
---------------------

发生了什么事？我该如何使第二个查询正常运行并快速执行？就我所知，两个设置是完全相同的。

- user1051849

---

你之前执行过 VACUUM FULL ANALYZE 吗？你是执行了 EXPLAIN ANALYZE 还是只有 EXPLAIN？ - ckruse

是否涉及复合索引？请展示表定义和“explain analyze”的输出。 - wildplasser

在这样的问题中，您真的应该包含精确的表和索引定义。哪些列是“NOT NULL”？索引大小？VACUUM FULL ANALYZE是否会改变任何内容？CLUSTER reports USING USING idx1和CLUSTER reports USING USING idx2是否会改变任何内容？（我希望它会！） - Erwin Brandstetter

Answer 1

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

我不是Postgres的专家，但仔细想想，我对此并不感到惊讶。

查询计划正在遍历索引，查找与area2 = 18匹配的相应行，并希望在第一个匹配项处停止（它正在使用索引，因此可以从最低值开始向上移动）。这只是对它的工作原理的猜测；我不知道Postgres是否确实是这样做的。

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

顺便说一句，假设有很多区域，您可能会通过对Area2建立索引而获得更好的结果。

- Gordon Linoff

---

感谢输入 - 实际上，也有一个关于area2的索引 - 只是似乎PostgreSQL选择不使用它... - user1051849

Answer 2

您正在进行索引扫描，但所检查的记录数量取决于您需要往上滚动多少列表才能匹配 area2 条件。

除非您的 area2 分布很奇怪，要优化此查询，您应该在 (area2, latitude) 和 (area2, longitude) 上放置组合索引。我猜您会得到小于10毫秒的结果。PG 还可以使用其位图堆扫描功能将独立的 area2 索引与现有索引相结合，而不是使用组合索引。

- Andrew Lazarus