On Wed, Feb 1, 2012 at 12:49 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Wed, Feb 1, 2012 at 12:29 AM, Rosser Schwarz
> <rosser.schwarz@gmail.com> wrote:
>> Remember, DDL is transactional in PostgreSQL. In principle, you
>> should be able to drop the index, do your inserts, and re-create the
>> index without affecting concurrent users, if you do all of that inside
>> an explicit transaction. Doing the inserts inside a transaction may
>> speed them up, as well.
>
> Creating an index requires an update lock on the table, and an
> exclusive lock on the system catalog.
> Even though with "CONCURRENTLY" it's only for a short while.
> So it does affect concurrent users.
Forgot to mention that if you don't commit the drop, you see no
performance increase.
So:
begin
drop
insert
create
commit
Does not work to improve performance. At all.