PostgreSQL如何使用索引？

Question

我有一个带有索引的表格：

create index on foo (a, b, c);

当搜索 a 和 b 时，Postgres 可以使用索引快速查找行：

test=# explain analyze select a from foo where a = 3 and b = 4;
 Index Only Scan using foo_a_b_c_idx on foo  (cost=0.43..486.83 rows=120 width=4) (actual time=0.141..23.981 rows=59049 loops=1)
   Index Cond: ((a = 3) AND (b = 4))
   Heap Fetches: 59049
 Total runtime: 25.894 ms

当搜索b和c时，速度要慢得多，因为它必须线性扫描整个索引（或表）：

test=# explain analyze select a from foo where b = 4 and c = 5;
 Index Only Scan using foo_a_b_c_idx on foo  (cost=0.43..121987.32 rows=120 width=4) (actual time=7.377..159.793 rows=59049 loops=1)
   Index Cond: ((b = 4) AND (c = 5))
   Heap Fetches: 59049
 Total runtime: 160.735 ms

然而，在这两种情况下，查询计划看起来是相等的（都称为“索引扫描”，带有一些“索引条件”）...是否可能告诉我们访问是否可以在对数时间或线性时间内完成（而不必查看每个索引定义）？

其他数据库系统更明确地说明了它们如何使用索引。在MS SQL中，第一个查询将是“索引搜索”（快速），而第二个查询将是“索引扫描”（慢）。在Sqlite中，第一个查询将是“使用覆盖索引搜索表foo”（快速），而第二个查询将是“使用覆盖索引扫描表foo”（慢）。

- stmax

---

原因是b,c不在索引的前沿，因此Postgres需要扫描更多的索引块以检查行是否符合条件。如果您确实需要第二个查询与第一个一样快，那么您需要在（b，c，a）上建立第二个索引。您还可以运行explain（analyze true，verbose true，buffers true），它将向您显示有关Postgres正在执行的IO数量的更多详细信息。 - user330315

@a_horse_with_no_name，这并没有回答我的问题。我知道为什么一个查询比另一个查询快。我的问题是：既然两种情况下的查询计划都相同（除了索引条件），那么我怎样才能知道哪个查询会更快（对数时间）或更慢（线性时间）？我尝试过“explain verbose”，但它也没有输出这些信息。 - stmax

@a_horse_with_no_name 是的，我做了，输出在这里：http://pastebin.com/e5qfZTsZ。如果不手动检查索引的定义，我怎么能从中判断查找是快还是慢？ - stmax

两个查询中，Buffers: shared hit=228 read=18541 written=1 这行有所不同吗？（顺便问一下，你知道这个网站吗：http://explain.depesz.com？） - user330315

那我不知道你在问什么。如果计划有所不同，那你还想要什么呢？但你可能想在Postgres（性能）邮件列表上提出这个问题。因为开发人员会阅读它，我相信如果有人知道答案，你会在那里得到它。 - user330315

Answer 1

除了@horse提到的更详细的查询计划选项之外，答案是：没有。除了对索引如何工作有基本的理解（当然还要了解自己的模式），没有其他提示或方法可以知道。

- Denis de Bernardy

---

抱歉，那并没有回答我的问题。我知道为什么一个查询比另一个查询快。我的问题是：既然两种情况下的查询计划都相同（除了索引条件），我怎样才能知道哪个查询会快（对数时间）或慢（线性时间）？这是查询计划中必要的信息，其他数据库系统显示它（参见上面的MS SQL、SQLite示例）。在Postgres中我从哪里获取这些信息，还是我必须手动检查每个索引的构造方式？ - stmax

说实话，我不确定你对这个问题的回答期望是什么。 - Denis de Bernardy

抱歉，也许问题不太清楚...例如，sqlite说“SCAN TABLE USING INDEX”与“SEARCH TABLE USING INDEX”，ms sql根据索引的使用方式分别说“Index Scan”和“Index Seek”。而postgres似乎将这两种情况都称为“Index Scan”。我正在尝试找出是否必须手动查看索引定义，或者是否有一种方法可以像其他数据库系统那样从postgres的查询计划中获取此信息。 - stmax

哦，那个！除了@horse强调的更详细的查询计划选项之外，答案是：没有。除了具有基本的索引工作原理的了解（当然还要知道自己的模式），没有提示或方法可以知道。 - Denis de Bernardy