Re: Curious about dead rows.

From: Jean-David Beyer <jeandavid8(at)verizon(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Curious about dead rows.
Date: 2007-11-14 16:53:17
Message-ID: 473B27FD.5050702@verizon.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Andrew Sullivan wrote:
> On Wed, Nov 14, 2007 at 07:12:45AM -0500, Jean-David Beyer wrote:
>> I know there have been rollbacks but I do a REINDEX, CLUSTER, and VACUUM
>> ANALYZE before starting the inserts in question. Do I need to do a VACUUM
>> FULL ANALYZE instead?
>
> I had another idea. As Alvaro says, CLUSTER will do everything you need.
> But are you sure there are _no other_ transactions open when you do that?

I am sure. I have a single-threaded program, so unless the postgres server
processes begin and end transactions on their own initiative, the only
things that would initiate transactions would be my one of my applications
that I run only one at a time, or leaving psql running. But as I understand
it, psql does not bother with transactions, and besides, I normally just do
SELECTs with that. (I also do INSERTs and UPDATEs with it in shell scripts,
but I do not run those when I am running the application either.

> This could cause problems, and CLUSTER's behaviour with other open
> transactions is not, um, friendly prior to the current beta.
>
I suppose it might.

Right now I put

// Reset statistics counters.
EXEC SQL BEGIN WORK;
EXEC SQL
SELECT pg_stat_reset();
EXEC SQL COMMIT WORK;

into my application so that the statistics counters will not count previous
UPDATEs and ROLLBACKs when the main program that I intend and believe to do
only INSERTs is running. It will make those statistics easier to read than
having to subtract previous values to get the changes.

Well, it will not work because I must be superuser (i.e., postgres) to
execute that, and if I am, I cannot read the input files. I will do it
manually with psql but that means I have to watch it run to do it at the
right time.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 11:20:01 up 22 days, 4:38, 4 users, load average: 6.16, 5.98, 5.62

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Sullivan 2007-11-14 16:58:23 Re: Curious about dead rows.
Previous Message Andrew Sullivan 2007-11-14 15:22:49 Re: Curious about dead rows.