Forum OpenACS CMS: vacuum is screwing up

Collapse
1: vacuum is screwing up
Posted by Alpesh Shah on 07/30/03 01:36 PM
I am using postgres version-- PostgreSQL 7.2.3 on i686-pc-linux-gnu. My db query was going slow. I came to know that using vacuum u can clear some space captured by unneccesory data. ( left by delete query etc..). and also it improves performance by modifying statastics..So I did vacuum and before vacuum I noted cost and it was

(cost=1745428.46..1745428.46 rows=4 width=604)
  ->  Sort  (cost=1745428.46..1745428.46 rows=4 width=604)
        ->  Hash Join  (cost=1845.38..1745428.41 rows=4 width=604)
              ->  Seq Scan on correspondence c  (cost=0.00..1743574.23 rows=1749 width=12)
                    SubPlan
                      ->  Materialize  (cost=498.37..498.37 rows=350 width=8)
                            ->  Aggregate  (cost=480.88..498.37 rows=350 width=8)
                                  ->  Group  (cost=480.88..489.62 rows=3498 width=8)
                                        ->  Sort  (cost=480.88..480.88 rows=3498 width=8)
                                              ->  Seq Scan on correspondence  (cost=0.00..274.98 rows=3498 width=8)
              ->  Hash  (cost=1845.38..1845.38 rows=3 width=592)
                    ->  Seq Scan on ticket t  (cost=0.00..1845.38 rows=3 width=592)
                          SubPlan
                            ->  Materialize  (cost=1.69..1.69 rows=1 width=4)
                                  ->  Seq Scan on group_user  (cost=0.00..1.69 rows=1 width=4)

now after vacuum I am getting this result for the same query...

(cost=5439902.60..5439902.60 rows=30 width=973)
  ->  Sort  (cost=5439902.60..5439902.60 rows=127 width=973)
        ->  Hash Join  (cost=4720.89..5439898.18 rows=127 width=973)
              ->  Seq Scan on correspondence c  (cost=0.00..5435158.18 rows=2338 width=480)
                    SubPlan
                      ->  Materialize  (cost=1162.17..1162.17 rows=468 width=8)
                            ->  Aggregate  (cost=1138.79..1162.17 rows=468 width=8)
                                  ->  Group  (cost=1138.79..1150.48 rows=4676 width=8)
                                        ->  Sort  (cost=1138.79..1138.79 rows=4676 width=8)
                                              ->  Seq Scan on correspondence  (cost=0.00..853.76 rows=4676 width=8)
              ->  Hash  (cost=4720.65..4720.65 rows=93 width=493)
                    ->  Seq Scan on ticket t  (cost=0.00..4720.65 rows=93 width=493)
                          SubPlan
                            ->  Materialize  (cost=1.74..1.74 rows=1 width=4)
                                  ->  Seq Scan on group_user  (cost=0.00..1.74 rows=1 width=4)

and now for each vacuum cost is increasing...I thought there must be some problem while vacuuming so i did another vacuum and my next vacuum gave me below cost..

(cost=5674096.27..5674096.27 rows=30 width=981).....

I used query VACUUM VERBOSE ANALYZE for vacuuming...

I can't understand what is happening...
Can anybody help me..!!!!!!

thanks in advance..

Collapse
2: Re: vacuum is screwing up (response to 1)
Posted by Randy Ferrer on 07/30/03 02:29 PM
try 'vacuumdb -v -z -f dbname' (no quotes) and see what you get. I think you should also be using either FULL if you use vacuum or else the name of a table, column. I'm not sure if vaccum just assumes FULL as a default.
Collapse
3: Re: vacuum is screwing up (response to 2)
Posted by Alpesh Shah on 07/30/03 02:59 PM
Hi,

Thanks for ur reply. But I think if u don't use FULL word then postgres does full vacuum. I can say it from the messages that it printed for each table. also -v option is for VERBOSE that i already did for printing all garbage things that it does..And there is no such command name vacuumdb in 7.2. it is vacuum that I have done. And I am sure that my vacuum was FULL. but it seems that it has altered all the statistic and picking up wrong plan. Don't know why ?

Collapse
4: Re: vacuum is screwing up (response to 1)
Posted by Randy Ferrer on 07/30/03 04:06 PM

<"there is no such command name vacuumdb in 7.2."> I refer you to: 

http://www.postgresql.org/docs/7.2/static/app-vacuumdb.html

Not really sure why you are getting these results, but I would recommend checking the forums on postgresql.org. I think some users where complaining about this issue a few months back. Perhaps you can find something there.

Collapse
5: Re: vacuum is screwing up (response to 1)
Posted by Don Baccus on 07/31/03 12:45 AM
The plan is identical, the increase in cost is probably due to the fact that the table increased in size since the previous vacuum.

vacuum statistics are a snapshot - those stats don't change when you add or delete data.

Collapse
6: Re: vacuum is screwing up (response to 4)
Posted by Alpesh Shah on 07/31/03 08:39 AM
Ya ! I got vacuumdb but it is not a backend command. So i could not use it. It is a shell script which wrapes around the backend command and i need to fire it before entering in to psql shell. However that does the same thing which I have done with VACUUM VERBOSE ANALYSE. ( -v for verbose and -z for analyze ) so that won't make any difference. However I am now checking stuff in forum at postgressql.org. will be back soon if get anything helpful.
Collapse
7: Re: vacuum is screwing up (response to 5)
Posted by Alpesh Shah on 07/31/03 08:44 AM
Thanks for reply Don.

Actually I stopped my server before doing vacuum so no entry or delete was happened. So the table size was not changed. Also I can say that the statistics were changed after vacuum as it gave me higher cost after vacuum. I fired explain query and took screenshot then did vacuum and then again fired explain query. So this means whatever changes I got were due to vacuum query. Also the vacuum finished without any error.