2010/6/1 <gareth.willi...@csiro.au> > > From: Szymon Guz [mailto:mabew...@gmail.com] > -snip- > >> 2010/6/1 <gareth.willi...@csiro.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
