pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reindexdb and REINDEX
2+ messages / 2 participants
[nested] [flat]

* Reindexdb and REINDEX
@ 2004-10-24 22:00 Rod Dutton <rod@e-rm.co.uk>
  2004-10-25 00:16 ` Re: Reindexdb and REINDEX Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Rod Dutton @ 2004-10-24 22:00 UTC (permalink / raw)
  To: pgsql-performance

Hi,
 
I have had some performance problems recently on very large tables (10s of
millions of rows).  A vacuum full did make a large improvement and then
dropping & re-creating the indexes also was very beneficial.  My performance
problem has now been solved.
 
My question is: will using the contrib/reindexdb or REINDEX sql command do
essentially the same job as dropping and re-creating the indexes.  I.E. do
you get a fully compacted and balanced index?  If so then I could use
contrib/reindexdb or REINDEX instead of drop/recreate.  
 
How is concurrency handled by contrib/reindexdb and REINDEX (I know you can
create an index on the fly with no obvious lock outs)?
 
Thanks,
 
Rod
 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Reindexdb and REINDEX
  2004-10-24 22:00 Reindexdb and REINDEX Rod Dutton <rod@e-rm.co.uk>
@ 2004-10-25 00:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-10-25 00:16 UTC (permalink / raw)
  To: Rod Dutton <rod@e-rm.co.uk>; +Cc: pgsql-performance

"Rod Dutton" <rod@e-rm.co.uk> writes:
> My question is: will using the contrib/reindexdb or REINDEX sql command do
> essentially the same job as dropping and re-creating the indexes.  I.E. do
> you get a fully compacted and balanced index?

Yes.
 
> How is concurrency handled by contrib/reindexdb and REINDEX (I know you can
> create an index on the fly with no obvious lock outs)?

In 8.0 they are almost equivalent, but in earlier releases
REINDEX takes an exclusive lock on the index's parent table.

The details are:

DROP INDEX: takes exclusive lock, but doesn't hold it long.
CREATE INDEX: takes ShareLock, which blocks writers but not readers.

So when you do it that way, readers can use the table while CREATE INDEX
runs, but of course they have no use of the dropped index.  Putting the
DROP and the CREATE in one transaction isn't a good idea if you want
concurrency, because then the exclusive lock persists till transaction
end.

REINDEX before 8.0: takes exclusive lock for the duration.

This of course is a dead loss for concurrency.

REINDEX in 8.0: takes ShareLock on the table and exclusive lock on
the particular index.

This means that writers are blocked, but readers can proceed *as long as
they don't try to use the index under reconstruction*.  If they try,
they block.

If you're rebuilding a popular index, you have a choice of making
readers do seqscans or having them block till the rebuild completes.

One other point is that DROP/CREATE breaks any stored plans that use the
index, which can have negative effects on plpgsql functions and PREPAREd
statements.  REINDEX doesn't break plans.  We don't currently have any
automated way of rebuilding stored plans, so in the worst case you may
have to terminate open backend sessions after a DROP/CREATE.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-10-25 00:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-24 22:00 Reindexdb and REINDEX Rod Dutton <rod@e-rm.co.uk>
2004-10-25 00:16 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox