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.

Thanks for any tips,
....Markus

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

Markus Benne <thing@m-bass.com> 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...e-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