Question 175489 in PostgreSQL Question posted in
The official documentation can be found here.

Can an index be created & used in a Postgresql transaction?

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:

  1. Added the index.
  2. Deleted the rows.
  3. 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search