From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: vacuum internals and performance affect
Date: Wed, 30 Nov 2011 13:19:09 -0800
Message-ID: <4ED69DCD.5000806@agliodbs.com> (raw)
In-Reply-To: <1322586733308-5033043.post@n5.nabble.com>
References: <1322586733308-5033043.post@n5.nabble.com>
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
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: josh@agliodbs.com
Subject: Re: vacuum internals and performance affect
In-Reply-To: <4ED69DCD.5000806@agliodbs.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