From: Richard Huxton <dev@archonet.com>
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: One tuple per transaction
Date: Tue, 15 Mar 2005 09:37:41 +0000
Message-ID: <4236ACE5.1040004@archonet.com> (raw)
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE770088052@black.aprote.com>
References: <A66A11DBF5525341AEF6B8DE39CDE770088052@black.aprote.com>
Tambet Matiisen wrote:
>>
>>Not exactly. The dead tuple in the index will be scanned the
>>first time (and its pointed heap tuple as well), then we will
>>mark it dead, then next time we came here, we will know that
>>the index tuple actually points to a uesless tuple, so we
>>will not scan its pointed heap tuple.
>>
>
>
> But the dead index tuple will still be read from disk next time? Maybe
> really the performance loss will be neglible, but if most of tuples in
> your table/index are dead, then it might be significant.
When a block is read from disk, any dead tuples in that block will be
read in. Vacuum recovers these.
> Consider the often suggested solution for speeding up "select count(*)
> from table" query: make another table rowcounts and for each of the
> original tables add insert and delete triggers to update row count in
> rowcounts table. Actually this is standard denormalization technique,
> which I use often. For example to ensure that order.total =
> sum(order_line.total).
This does of course completely destroy concurrency. Since you need to
lock the summary table, other clients have to wait until you are done.
> Now, if typical inserts into your most active table occur in batches of
> 3 rows, in one transaction, then row count for this table is updated 3
> times during transaction. 3 updates generate 3 tuples, while 2 of them
> are dead from the very start. You effectively commit 2 useless tuples.
> After millions of inserts you end up with rowcounts table having 2/3 of
> dead tuples and queries start to slow down.
>
> Current solution is to vacuum often. My proposal was to create new tuple
> only with first update. The next updates in the same transaction would
> update the existing tuple, not create a new.
How do you roll back to a savepoint with this model?
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, t.matiisen@aprote.ee
Subject: Re: One tuple per transaction
In-Reply-To: <4236ACE5.1040004@archonet.com>
* 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