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

Gareth(dot)Williams(at)csiro(dot)au wrote:
> 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.
>

The database doesn't care one bit if you create a bunch of redundant
indexes. So long as one of them is around to satisfy the queries that
need the index to run well, you're fine.

The main thing you can't do with the index concurrently/rename shuffle
you've discovered here is use that approach to concurrently rebuild an
index that enforces a constraint or unique index. If your index is
enforcing a PRIMARY KEY for example, you'll discover a major roadblock
were you to try this same technique to rebuild it. Those are tied into
the constraint portion of the system catalogs and manipulating them
isn't so easy.

Regular indexes that exist just to speed up queries, those you can
rename around as you've been figuring out without any downside. From a
general paranoia perspective, you should run an explicit ANALYZE on the
underlying table after you finish the shuffle, just to make absolutely
sure the right statistics are available afterwards.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us