Re: [GENERAL] identifying performance hits: how to ???

On Wed, 12 Jan 2000, Jim Mercer wrote:

> On Wed, Jan 12, 2000 at 12:23:23PM -0400, The Hermit Hacker wrote:
> > Have/do you perform reasonably regular vacuum's of the database?
>
> on my databases, i have only been successful in doing a "VACUUM VERBOSE" on
> my tables.
>
> i suspect i've got the syntax wrong or something:
>
> nagoss=> \h vacuum
> Command: vacuum
> Description: vacuum the database, i.e. cleans out deleted records, updates statistics
> Syntax:
>         VACUUM [VERBOSE] [ANALYZE] [table]
>         or
>         VACUUM [VERBOSE]  ANALYZE  [table [(attr1, ...attrN)]];
>
> nagoss=> vacuum verbose analyse switches;
> ERROR:  parser: parse error at or near "switches"
> nagoss=> vacuum verbose switches;
> NOTICE:  --Relation switches--
> NOTICE:  Pages 1: Changed 1, Reapped 0, Empty 0, New 0; Tup 9: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 78,
MaxLen78; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec. 
> NOTICE:  Index switch_name: Pages 2; Tuples 9. Elapsed 0/0 sec.
> VACUUM
>
> what is the correct syntax for doing a vacuum analyse?

I've generally just done a 'vacuum verbose analyze' and do the whole
database ...

But, just tried it on a table and:

udmsearch=> vacuum verbose analyze url;
NOTICE:  --Relation url--
NOTICE:  Pages 240: Changed 2, Reapped 213, Empty 0, New 0; Tup 2740: Vac 5648, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen
120,MaxLen 616; Re-using: Free/Avail. Space 813836/811912; EndEmpty/Avail. Pages 0/212. Elapsed 0/0 sec. 
NOTICE:  Index url_next_index_time: Pages 35; Tuples 2740: Deleted 5648. Elapsed 0/0 sec.
NOTICE:  Index url_crc: Pages 34; Tuples 2740: Deleted 5648. Elapsed 0/0 sec.
NOTICE:  Index url_url: Pages 90; Tuples 2740: Deleted 4973. Elapsed 0/0 sec.
NOTICE:  Index url_pkey: Pages 25; Tuples 2740: Deleted 4973. Elapsed 0/0 sec.
NOTICE:  Rel url: Pages: 240 --> 142; Tuple(s) moved: 1486. Elapsed 0/0 sec.
NOTICE:  Index url_next_index_time: Pages 38; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE:  Index url_crc: Pages 46; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE:  Index url_url: Pages 90; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE:  Index url_pkey: Pages 26; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
VACUUM
udmsearch=>


What version of PostgreSQL are you using?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org