Message-ID: <4ED69DCD.5000806@agliodbs.com>
Date: Wed, 30 Nov 2011 13:19:09 -0800
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.2.24) Gecko/20111103 Lightning/1.0b2 Thunderbird/3.1.16
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: vacuum internals and performance affect
References: <1322586733308-5033043.post@n5.nabble.com>
In-Reply-To: <1322586733308-5033043.post@n5.nabble.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

MX,

> to my understanding, vacuum just marks the dead rows of a table so that from
> that point on that space would be re-used for new inserts and new updates on
> that specific table. however, if there is an open transaction, vacuum can
> only do what is described above up to the point that the open transaction
> was started. so if for example there is a query running for 1 day, no matter
> how many times i will have vacuumed the table (manual or auto), the dead
> rows wont be possible to be marked as re-usable space.
> -is the above correct?

More or less.  The transactionID isn't a timestamp, so the "stop point"
is based on snapshots rather than a point-in-time.  But that's a fine
distinction.

> -is there something more about vacuum in that case i am describing? would
> for example mark the rows as 'semi-dead' so that when a scan would be made
> these rows wouldn't be checked and so the queries would be faster? is there
> anything else for this specific case?

Well, vacuum does some other work, yes.

> -would there be any effect from the vacuum on the indexes of the table?like
> i said above for the table, would the entries of the index not be scanned
> for a query, due to some reason?

Vacuum also does some pruning dead index pointers.

Otherwise, I'm not sure what you're asking.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com