是否可以临时禁用Postgres中的索引？ [英] Is it possible to temporarily disable an index in Postgres?

问题描述

原因：我有一个索引，我想暂时禁用，我找不到任何文件表明这是可能的。我有一个索引，可能会导致与其加速设计的任何无关的查询问题。这是一个新的索引，系统作为一个整体似乎比推出后更慢。我只是想能够可靠地消除它作为罪魁祸首，这似乎是最简单的方法，其他解决方案建议，以及更好的问题建议，也欢迎。

解决方案

您可以捅系统目录来禁用索引：

  update pg_index set indisvalid = false其中indexrelid ='test_pkey':: regclass 
  

这意味着索引不会用于查询，但仍将被更新。它是用于并发索引构建的标志之一。请注意，我只做了一个快速测试，看看该索引是否仍然被更新，请注意。

I have got one index on a table that I would like to temporarily disable, I can't find any documentation suggesting that it's possible, though.

Reason: I've got an index that might be causing problems in queries unrelated to to any of the ones it was designed to speed up. It's a new index, and the system as a whole seems slower since it was introduced. I just want to be able to reliably eliminate it as the culprit, and this seems like the easiest way, other solution suggestions, as well as better question suggestions, are also welcome.

解决方案

You can poke the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

This means that the index won't be used for queries but will still be updated. It's one of the flags used for concurrent index building. Note that I've only done a quick test to see if the index still seems to be updated, caveat emptor.

这篇关于是否可以临时禁用Postgres中的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！