说明有关索引+ ORDER BY在PostgreSQL的9.1

问题描述：

表+ LIMIT +卦：说明有关索引+ ORDER BY在PostgreSQL的9.1

CREATE TABLE msp_adm_munic_complet_g_01 
(
    nom_tri character varying(64), 
    ogc_fid serial NOT NULL 
)

指数：

CREATE INDEX idx_gist_msp_adm_munic_complet_g_nom_tri 
    ON msp_adm_munic_complet_g_01 
    USING gist 
    (nom_tri COLLATE pg_catalog."default" gist_trgm_ops);

查询：

select * from msp_adm_munic_complet_g_01 
ORDER BY 'potato'<->nom_tri 
LIMIT 25;

问题：

为什么它通过梳子通过指数ORDER BY + LIMIT的初始化，而不是当查询只包含ORDER BY时？

当然，指数也增加了查询的速度

我发现的唯一的解释是在这里： http://www.postgresql.org/docs/9.1/static/indexes-ordering.html

但缺乏细节

编辑＃1：

带限制的查询计划：

Limit (cost=0.00..19.27 rows=25 width=590) 
    -> Index Scan using idx_gist_msp_adm_munic_complet_g_nom_tri on 
msp_adm_munic_complet_g_01 (cost=0.00..2784.49 rows=3612 width=590) 
     Order By: ((nom_tri)::text <-> 'potato'::text)

查询计划没有限制：

Sort (cost=1847.59..1856.62 rows=3612 width=590) 
    Sort Key: (('potato'::text <-> (nom_tri)::text)) 
    -> Seq Scan on msp_adm_munic_complet_g_01 (cost=0.00..682.15 rows=3612 width=590)

请向我们展示您的查询的执行计划（'explain analyze'）（理想情况下上传到http://explain.depesz.com） –

另外，请解释*为什么*您认为它应该使用索引。你认为执行者应该采取哪些精确的步骤？尝试并向他们提出一些估计的成本 - 如果您不知道某些步骤的相对成本，请不要担心。即使数字与现实不符，仔细思考它也是有用的。 –

当我阅读您所引用的PG文档中的解释时，为什么在这种情况下不使用索引对我有意义。你可能必须解释为什么你认为这个解释缺乏细节。 – harmic

答

当然，指数会增加查询

的速度，我认为这是问题的要点。关于它当然没有“当然”。

想象一下，你有一本大书。该书在后面有一个索引，列出了它们出现的不同术语和页码。

你的老板找到你说：“我希望你按照字母顺序列出书中前10个词汇，并且写下他们的一切”。您可以从索引开始，然后转到您找到的前10个术语列出的每个页面。这不会花很长时间。特别是与阅读整本书并尝试在头脑中排序的替代方法相比，后者找到前10个。

接下来，您的老板向您介绍并说他希望您列出书中的所有术语他们的定义，按字母顺序。天真地，你决定使用相同的方法。你会不断翻阅本书，重复访问每一页。这将需要永远。

到你完成的时候，你会读完整个索引并多次访问书中的每一页。如果你阅读这本书的时候，阅读本书的时候会更快一些，包括封面，按照内容分类（尤其是如果你是一个数据库，它比人类的短期记忆大得多，并且可以很容易地在内存中对大型列表进行排序）。

这正是数据库中发生的情况。计算机依次读取磁盘文件效率更高，因为它不需要太多地来回寻找磁盘头。它一次读取整个页面。它与我们这里的人类相比具有一些优势 - enourmouse短期记忆意味着它可以同时保存数千页的记忆。但是一张大桌子和/或繁重的工作量将会打败这一局面。

因此，数据库在执行它之前分析每个查询。它会尝试估计表中返回什么比例，连同它所知道的随机访问页面的成本与其他表的统计数据有关。有一点可以说，扫描整个表格并忘记索引会更高效。

您可能认为这种过分简单的比喻不适用于三元组索引，但它确实如此。索引不是字母，但构建排序列表的机制是相同的 - 除非并非所有索引类型都适合在任何情况下返回已排序的行。许多索引类型允许您快速查找某些内容，但不保持键的顺序。在内置索引类型中，只有b-tree适用于返回排序数据。我实际上对三撇子指数可用于此有些诧异。但它取决于ORDER表达式 - 我猜这个索引确实会返回< - >顺序的数据。

如果以排序顺序遍历行是该表上的常见操作，则可以采取一些措施使其更快。

如果您使用Postgresql 9.2，则可能可以使用index-only扫描。在你的查询中，你正在选择所有的列，这意味着它不能使用仅索引扫描，并且无论如何我不认为你能够使用带有trigram索引的只索引扫描。

您可以使用CLUSTER命令按照与索引相同的顺序排列表（尽管在插入或更新数据时不会保持这种方式，因此需要定期在表中执行经常更新）。

您可能会发现该表格可以通过微调正在保存的statistics而受益。更多的统计数据可能会让它更频繁地使用索引。

您可以调整计划程序用来估计顺序读取数据的相对成本与随机访问数据相比的参数。你可以切换到使用固态硬盘而不是旧式旋转磁盘。

当然，更多的内存永远不会伤害数据库。

说明有关索引+ ORDER BY在PostgreSQL的9.1

相关推荐