英文:
Create and use an index inside a transaction
问题
我有一个删除查询很慢的问题。如此处建议，当我添加索引时，查询结果大大改善。除了这种情况，我不需要这个索引，所以我做了以下操作：
- 添加了索引。
- 删除了行。
- 删除了索引。
是否可能在一个事务中执行这3个步骤，而不需要提交这个索引，但仍然能够在delete查询中使用它？
英文:
I have a slow delete query. As suggested here when I add an index I get much better results. Beside this scenario I don't need this index, so what I did is:
- Added the index.
- Deleted the rows.
- Dropped the index.
Is it possible to do these 3 steps in one transactions and not having this index committed at all but still be able to use it in the delete query?
答案1
得分: 2
可以在事务内创建索引（除非您使用CONCURRENTLY）。然而，这是一个不好的想法：CREATE INDEX会锁定表，阻止所有数据修改，而该锁将持续到您的事务结束。因此，这将对并发的SQL语句产生比在原地多一个索引的影响。
英文:
Yes, you can create an index inside a transaction (unless you are using CONCURRENTLY). However, that is a bad idea: CREATE INDEX locks the table against all data modifications, and that lock will be held until your transaction ends. So you will affect concurrent SQL statements way more that you would with an extra index in place.
答案2
得分: 1
不要通过创建索引、运行查询，然后删除索引来自寻开心。
创建索引的计算和I/O工作量是相当大的。而且，除非在运行删除查询之间对表进行了极大数量的更改（插入、更新和删除）在生产中，否则维护索引的开销是适度的。存储开销很可能相当合理。HDD / SSD存储便宜且价格在下降。保留索引不会影响SELECT查询性能，而且可能会对某些查询有所帮助。
因此，虽然您可以在事务内创建索引、运行查询，然后删除索引，但您几乎肯定更好地创建索引并保留它。
英文:
You're not doing yourself any favors by creating the index, running the query, then dropping the index.
The computational and I/O workload for CREATE INDEX is nontrivial. And, the overhead for maintaining an index is modest unless you have an extremely large number of changes made to your table in production (INSERTs, UPDATEs, and DELETEs) between runs of your delete query. And the storage overhead is very likely quite reasonable. HDD / SSD storage is cheap and getting cheaper. Having the index in place won't hurt SELECT query performance, and might help some queries.
So while you can create the index, run the query, and drop the index inside a transaction, you're almost certainly better off creating the index and leaving it in place.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论