Re: create index concurrently - duplicate index to reduce time without an index

2010/6/1 <Gareth(dot)Williams(at)csiro(dot)au>

> > From: Szymon Guz [mailto:mabewlun(at)gmail(dot)com]
> -snip-
> >> 2010/6/1 <Gareth(dot)Williams(at)csiro(dot)au>
> >> Hi,
>
> >> We want to reindex the database behind a production service without
> interrupting the service.
>
> >> I had an idea for creating the index with a new name then dropping the
> existing index and renaming the new one - and it seems to work and would
> reduce the time without an index to be minimal. I tried:
> psql -d ICAT -c 'create unique index concurrently tmp_idx_objt_access1
> on R_OBJT_ACCESS (object_id,user_id);'
> # would check if that worked before proceeding #
> psql -d ICAT -c 'drop index idx_objt_access1;'
> psql -d ICAT -c 'alter index tmp_idx_objt_access1 rename to
> idx_objt_access1;'
>
> >> But then a colleague pointed out that maybe the name of the index is not
> meaningful and this might not be any use.
>
> >> Can any experts confirm the validity of this approach or shoot it down?
>
> > The index name is not used for planning query execution (most important
> thing is the index definition), but of course it is important to have some
> logical name convention that usually helps in fast understanding database
> schema. Name the index as you want, database really doesn't understand the
> names.
>
> Thanks Szymon,
>
> I was afraid that was the case. So the rest of the question is, if I have
> two indexes with identical definitions, what happens? I've confirmed that I
> can create indexes with identical definitions (except name) without postgres
> complaining - and without breaking the client on my test system - but I am
> wary of trying it on my production system where there is much more data
> (8GB) and I care about it's integrity so much more.

> I've just tested further and if I create two indexes with identical
> definitions, my queries are fast, and I can delete either of them and the
> queries are still fast, but with both deleted the queries are slow. And yes,
> renaming makes no difference but is nice for understanding the purpose of
> the index.
>
> So I still haven't seen this procedure break anything, but does anyone know
> a reason it might be inadvisable?
>
>
When you have two exactly the same index definitions, that's just a waste of
resources. There will be used only one of them for speeding up selects, but
all of them must be updated during insert/update/delete operations.

regards
Szymon Guz