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

On 2 Jun 2010, at 6:00, <Gareth(dot)Williams(at)csiro(dot)au> <Gareth(dot)Williams(at)csiro(dot)au> wrote:
> My understanding was that the index would not be updated for inserts to the actual table - which is why one has to reindex or drop the index and create it again (with concurrently if you don't want to block reads). Am I missing something?

That's definitely not true, indexes would be rather useless if that were the case. What doesn't get updated with inserts are planner statistics, but you update those by executing ANALYSE (or VACUUM ANALYSE), or automatically by autovacuum.

> Perhaps I don't need to recreate the index at all - but I've been advised to reindex.
>
> See:
> http://groups.google.com/group/irod-chat/browse_thread/thread/0396d48ffecfb2b0#
> "On a side note, for the irods performance, an other key factor is also
> the iCAT database performances and in some of your tests below, it has
> an impact. Recomputing the index on a regular basis is something
> important: here, I am doing it once per week for each iCAT (cron task).
> Even for a catalog with millions of files, if you have ingested tens of
> thousands of new files, you will see an improvement when doing
> reindexing (for example an ils performed in 0.3 s will goes down to less
> than 0.1 s when reindexing has been made)."

There are cases where reindexing shows a performance improvement over just analysing a table, but the above (only inserts) shouldn't be one of them.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4c064ad110154201810452!