Postgres 中索引扫描与顺序扫描的区别

Question

我可以帮您翻译成中文。这段内容是关于使用Postgres数据库，试图查看1000000行表上索引扫描和顺序扫描之间差异的。请描述该表。

\d grades 

然后解释分析行，范围在10到500000之间

explain analyze select name from grades where pid between 10 and 500000 ; 

然后解释分析从第10行到第600000行之间的内容

explain analyze select name from grades where pid between 10 and 600000 ;

对我来说很奇怪的是，为什么第一个查询会进行索引扫描，而第二个查询却进行顺序扫描，尽管它们都使用了包含在索引中的相同列进行查询。

- Elsayed

Answer 1

如果你只需要一行表格，使用索引扫描比顺序扫描更快。如果你需要整个表格，则顺序扫描比索引扫描更快。PostgreSQL在这两种访问方法之间切换的临界点就在这之间。

你可以调整random_page_cost来影响选择顺序扫描的点。如果你使用SSD存储，应将该参数设置为1.0或1.1，告诉PostgreSQL索引扫描在你的硬件上更便宜。

- Laurenz Albe

---

将 random_page_cost=1.1 进行设置对我很有效 - 规划器从 300+ms 的顺序扫描转换为 <1ms 的索引扫描。 - Yuri

Answer 2

PostgreSQL使用基于代价的优化器，而不是基于规则的优化器。如果您将索引扫描的估计成本18693按预期行数比例线性放大（虽然这并不完全是规划器所做的，但应该是一个足够好的第一近似），您将得到22330。这高于顺序扫描的预期成本21372，因此选择顺序扫描。

如果您以同样的方式放大索引扫描的实际时间，您将得到89毫秒，略快于顺序扫描的实际时间。因此，也许规划器在这里有一个非常轻微的误差，但在实践中绝对不用担心。

如果运行时间的差异是10倍而不是10％，那么可能值得进一步调查。

- jjanes

Answer 3

这是因为如果SELECT返回的行数超过表中所有行数的5-10％，顺序扫描比索引扫描快得多。而您的第二个查询已经达到了这个阈值，因为您正在获取更多的行。

- eshirvana

---

请在第一个查询中使用@eshirvana，检索大约50%而不是10%的行，并向我解释索引扫描。 - Elsayed