Re: [PERFORM] Reindex - Is this necessary after a vacuum?

[Tom Lane]

Markus Benne <[EMAIL PROTECTED]> writes:
> Our table in question is vacuumed every 4 minutes, and
> we are reindexing after each one.

That's pretty silly.  You might need a reindex once in awhile, but
not every time you vacuum.

The draft 8.1 docs contain some discussion of possible reasons for
periodic reindexing:
http://developer.postgresql.org/docs/postgres/routine-reindex.html
but none of these reasons justify once-per-vacuum reindexes.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Reindex - Is this necessary after a vacuum?

[Richard Huxton]

Markus Benne wrote:

We are reindexing frequently, and I'm wondering if
this is really necessary, given that it appears to
take an exclusive lock on the table.

Our table in question is vacuumed every 4 minutes, and
we are reindexing after each one.

I'm not a fan of locking this table that frequently,
even if it is only for 5 - 10 seconds depending on
load.

The vacuum is a standard vacuum.  Nightly we do a
vacuum analyze.


At most I'd do a nightly reindex. And in fact, I'd probably drop the 
index, full vacuum, recreate index.


But you only need to reindex at all if you have a specific problem with 
the index bloating. Are you seeing this?


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match