Postgres Professional
en

Re: Index bloat problem?

От: Dave Chapeskie
Тема: Re: Index bloat problem?
Дата: ,
Msg-id: 20050421193305.GA84813@ddm.wox.org
(см: обсуждение, исходный текст)
Ответ на: Re: Index bloat problem?  (Josh Berkus)
Ответы: Re: Index bloat problem?  (Josh Berkus)
Re: Index bloat problem?  ("Jim C. Nasby")
Список: pgsql-performance

Скрыть дерево обсуждения

Index bloat problem?  (Bill Chandler, )
 Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (Alex Turner, )
   Re: Index bloat problem?  (Josh Berkus, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (Josh Berkus, )
   Re: Index bloat problem?  (Michael Guerin, )
    Re: Index bloat problem?  (Josh Berkus, )
     Re: Index bloat problem?  (Dave Chapeskie, )
      Re: Index bloat problem?  (Josh Berkus, )
      Re: Index bloat problem?  ("Jim C. Nasby", )
  Re: Index bloat problem?  (Alex Turner, )
 Re: Index bloat problem?  (Chris Browne, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Mischa Sandberg, )
 Re: Index bloat problem?  (Bill Chandler, )
  Re: Index bloat problem?  (Josh Berkus, )
  Re: Index bloat problem?  (John A Meinel, )
 Re: Index bloat problem?  (Tom Lane, )
  Re: Index bloat problem?  (David Roussel, )
   Re: Index bloat problem?  (Tom Lane, )
    Re: Index bloat problem?  ("David Roussel", )
     Re: Index bloat problem?  (Tom Lane, )
 Re: Index bloat problem?  (, ) 
On Thu, Apr 21, 2005 at 11:28:43AM -0700, Josh Berkus wrote:
> Michael,
>
> > ....Every five minutes, DBCC INDEXDEFRAG will report to the user an
> > estimated percentage completed. DBCC INDEXDEFRAG can be terminated at
> > any point in the process, and *any completed work is retained.*"
>
> Keen.  Sounds like something for our TODO list.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

See http://archives.postgresql.org/pgsql-general/2005-03/msg01465.php
for my thoughts on a non-blocking alternative to REINDEX.  I got no
replies to that message. :-(


I've almost got a working solution integrated in the backend that does
correct WAL logging and everything.  (Writing the code to write and
replay WAL logs for complicated operations can be very annoying!)

For now I've gone with a syntax of:

  REINDEX INDEX btree_index_name INCREMENTAL;

(For now it's not a proper index AM (accessor method), instead the
generic index code knows this is only supported for btrees and directly
calls the btree_compress function.)

It's not actually a REINDEX per-se in that it doesn't rebuild the whole
index.  It holds brief exclusive locks on the index while it shuffles
items around to pack the leaf pages fuller.  There were issues with the
code I attached to the above message that have been resolved with the
new code.  With respect to the numbers provided in that e-mail the new
code also recycles more pages than before.

Once I've finished it up I'll prepare and post a patch.

--
Dave Chapeskie
OpenPGP Key ID: 0x3D2B6B34

В списке pgsql-performance по дате сообщения:

Предыдущее:
От: Enrico Weigelt
Дата:
Сообщение: foreign key performance
Следующее:
От: "Shoaib Burq (VPAC)"
Дата:
Сообщение: Re: two queries and dual cpu (perplexed)