2010/6/1 <Gareth.Williams@xxxxxxxx>
> From: Szymon Guz [mailto:mabewlun@xxxxxxxxx]
-snip-
Thanks Szymon,>> 2010/6/1 <Gareth.Williams@xxxxxxxx>
>> 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.
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
