un-vacuum?

On Thu, Jan 19, 2006 at 02:25:15PM -0500, uwcssa wrote:

i have a table that is already "vacuum"ed. for some reason i want
to un-vacuum it instead of dropping the table and recreate the table
and indexes on it. is there a existing command to do so?

What effect do you want this un-vacuum to have? What problem are
you trying to solve?

--
Michael Fuhr

On 1/19/06, uwcssa <uwcssa@gmail.com> wrote:

I have a simple question here, not sure if i should posted here but
if you have the quick answer, it helps a lot

i have a table that is already "vacuum"ed. for some reason i want
to un-vacuum it instead of dropping the table and recreate the table
and indexes on it. is there a existing command to do so?

can you explain yourself a bit better?
vacuum is good, why do you think you want to undo it?
why do you think that drop and create will undo vacuum?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Ühel kenal päeval, N, 2006-01-19 kell 14:25, kirjutas uwcssa:

I have a simple question here, not sure if i should posted here but
if you have the quick answer, it helps a lot

i have a table that is already "vacuum"ed. for some reason i want
to un-vacuum it instead of dropping the table and recreate the table
and indexes on it. is there a existing command to do so?

What exactly are you tryingto achieve ?

-------------
Hannu

I want to do this for repeating some experiment results, not for
tuning the db (pretty much like using an old machine to find
performance difference for an algorithm). so if i have a way
of knowing which tables are storing the statistics, i guess i can
delete all from that table to archieve this.

On Thu, Jan 19, 2006 at 03:54:33PM -0500, uwcssa wrote:

I want to do this for repeating some experiment results, not for
tuning the db (pretty much like using an old machine to find
performance difference for an algorithm). so if i have a way
of knowing which tables are storing the statistics, i guess i can
delete all from that table to archieve this.

pg_statistic stores statistics. I think it's safe to delete rows,
but you might want to wait for one of the developers to comment
before mucking around with the stored values, especially if you're
not familiar with reading the pg_stats view.

http://www.postgresql.org/docs/8.1/interactive/catalog-pg-statistic.html

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:

On Thu, Jan 19, 2006 at 03:54:33PM -0500, uwcssa wrote:

I want to do this for repeating some experiment results, not for
tuning the db (pretty much like using an old machine to find
performance difference for an algorithm). so if i have a way
of knowing which tables are storing the statistics, i guess i can
delete all from that table to archieve this.

pg_statistic stores statistics. I think it's safe to delete rows,

"DELETE FROM pg_statistic" is safe enough, but it's more of an
"un-analyze" than an "un-vacuum". There is no "un-vacuum".

regards, tom lane

You could also do this by doing a filesystem copy of $PG_DATA (with
postgresql shut down), and then restoring that copy after your test. If
you used rsync (or something that allowed filesystem snapshots) this
probably wouldn't be very painful.

On Thu, Jan 19, 2006 at 03:54:33PM -0500, uwcssa wrote:

I want to do this for repeating some experiment results, not for
tuning the db (pretty much like using an old machine to find
performance difference for an algorithm). so if i have a way
of knowing which tables are storing the statistics, i guess i can
delete all from that table to archieve this.

On 1/19/06, Hannu Krosing <hannu@skype.net> wrote:

?hel kenal p?eval, N, 2006-01-19 kell 14:25, kirjutas uwcssa:

I have a simple question here, not sure if i should posted here but
if you have the quick answer, it helps a lot

i have a table that is already "vacuum"ed. for some reason i want
to un-vacuum it instead of dropping the table and recreate the table
and indexes on it. is there a existing command to do so?

What exactly are you tryingto achieve ?

-------------
Hannu

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, 2006-01-19 at 14:25 -0500, uwcssa wrote:

I have a simple question here, not sure if i should posted here but
if you have the quick answer, it helps a lot

i have a table that is already "vacuum"ed. for some reason i want
to un-vacuum it instead of dropping the table and recreate the table
and indexes on it. is there a existing command to do so?

I think your best route to experimentation is to stick to executing real
commands in as a very similar environment to actual usage. I would never
trust experimental results derived from the use of such a command,
should such a thing ever exist. Good testing takes time and care; there
are few shortcuts to good experimental results in any scientific
endeavour.

Best Regards, Simon Riggs

On Thu, Jan 19, 2006 at 04:54:21PM -0600, Jim C. Nasby wrote:

You could also do this by doing a filesystem copy of $PG_DATA (with
postgresql shut down), and then restoring that copy after your test. If
you used rsync (or something that allowed filesystem snapshots) this
probably wouldn't be very painful.

Hmmm...wouldn't using a template database work the same way? Doesn't
CREATE DATABASE simply do a recursive copy of the template database's
directory? I'm thinking you could

1. Set up the initial test conditions in some database. This could
include creating unanalyzed tables in dire need of vacuuming.

2. Use createdb or CREATE DATABASE to create a new database using
the database in (1) as the template.

3. Run tests in the new database.

4. Repeat (2) and (3) as necessary.

--
Michael Fuhr