Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group