pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: K C Lau <kclau60@netvigator.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why so slow?
Date: Sat, 29 Apr 2006 09:46:06 +0800
Message-ID: <6.2.1.2.0.20060429084119.05290438@localhost> (raw)
In-Reply-To: <20060428190005.GA15689@wolff.to>
References: <20060428145842.GI97354@pervasive.com>
<BAY101-F3027DA179AC8CEE9FAAF77ADB20@phx.gbl>
<20060428190005.GA15689@wolff.to>
At 03:00 06/04/29, Bruno Wolff III wrote:
>On Fri, Apr 28, 2006 at 17:37:30 +0000,
> Bealach-na Bo <bealach_na_bo@hotmail.com> wrote:
> > >The above shows that the indexes contained 10M rows and 160M of dead
> > >space each. That means you weren't vacuuming nearly enough.
> >
> > How is it that a row in the table can grow to a size far exceeding the sum
> > of the maximum sized of the fields it consists of?
>
>Because unless you run vacuum, the old deleted rows are not reused. Those
>rows cannot be deleted immediately, because the rows may be visible to
>other transactions. Periodic vacuums are used to find deleted rows which
>are no longer visible to any transactions.
>
>You probably want to read the following:
>http://developer.postgresql.org/docs/postgres/routine-vacuuming.html
Would recycling dead tuples on the fly (mentioned in p.14 in the article
http://www.postgresql.org/files/developer/transactions.pdf ) significantly
reduce the need for periodic vacuums?
Without knowing the internals, I have this simplistic idea: if Postgres
maintains the current lowest transaction ID for all active transactions, it
probably could recycle dead tuples on the fly. The current lowest
transaction ID could be maintained in a doubly linked list with maximum
<max_connections> entries. A backward link in the tuple header might be
needed too.
Any comments?
Cheers,
KC.
view thread (26+ messages) latest in thread
Message-ID: <6.2.1.2.0.20060429084119.05290438@localhost> Permalink: ../6.2.1.2.0.20060429084119.05290438@localhost/ Also on: postgresql.org/message-id/6.2.1.2.0.20060429084119.05290438@localhost ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: kclau60@netvigator.com Subject: Re: Why so slow? In-Reply-To: <6.2.1.2.0.20060429084119.05290438@localhost> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
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