Re: [HACKERS] un-vacuum?
On Thu, Jan 19, 2006 at 02:25:15PM -0500, uwcssa wrote: i have a table that is already vacuumed. 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] un-vacuum?
On 1/19/06, uwcssa [EMAIL PROTECTED] 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 vacuumed. 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 ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] un-vacuum?
Ü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 vacuumed. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] un-vacuum?
Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] un-vacuum?
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 [EMAIL PROTECTED] 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 vacuumed. 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
Re: [HACKERS] un-vacuum?
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] un-vacuum?
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 vacuumed. 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] un-vacuum?
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 [EMAIL PROTECTED] 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 vacuumed. 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] un-vacuum?
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
