Create and use an index inside a transaction

Question

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?

Answer 1

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.