postgresql 是否可以暂时禁用 Postgres 中的索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6146024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to temporarily disable an index in Postgres?
提问by quodlibetor
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.
原因：我有一个索引，它可能会导致与它旨在加速的任何查询无关的查询出现问题。这是一个新的索引，而且自从它被引入以来，整个系统似乎变慢了。我只是希望能够可靠地消除它作为罪魁祸首，这似乎是最简单的方法，也欢迎其他解决方案建议以及更好的问题建议。
回答by araqnid
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.
这意味着索引不会用于查询，但仍会更新。它是用于并发索引构建的标志之一。请注意，我只是做了一个快速测试，看看索引是否似乎仍在更新，请注意空客。
回答by Seth Robertson
begin;
drop index foo_ndx;
explain analyze select * from foo;
rollback;
I don't think there is a way to disable just one, though you can do this in a transaction to make recovering from it dead simple. You can also disable indexscan to disable all indices.
我不认为有一种方法可以仅禁用一个，尽管您可以在事务中执行此操作以使从中恢复非常简单。您还可以禁用 indexscan 以禁用所有索引。
Also, make sure you are doing explain analyzeon your queries.
另外，请确保您正在explain analyze查询。
