Thread: un-vacuum?
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?
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 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 > > > > > > >
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