From d.wall@computer.org Wed Jun  13 19:04:18 2001
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5DJ4IE64843
	for <pgsql-general@postgresql.org>;
	Wed, 13 Jun 2001 15:04:18 -0400 (EDT)
	(envelope-from d.wall@computer.org)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id MAA11529
	for <pgsql-general@postgresql.org>; Wed, 13 Jun 2001 12:04:10 -0700
Message-ID: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <d.wall@computer.org>
From: "David Wall" <d.wall@computer.org>
To: <pgsql-general@postgresql.org>
Subject: Vacuum analyze in 7.1.1
Date: Wed, 13 Jun 2001 12:04:17 -0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00C1_01C0F400.F8D28F20"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200106/487
X-Sequence-Number: 10974

This is a multi-part message in MIME format.

------=_NextPart_000_00C1_01C0F400.F8D28F20
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Does a 'vacuum analyze' do the same functions as a plain 'vacuum' PLUS the =
stats updates?  Just wondering if I need to run both commands from time to =
time, or if I can just do 'vacuum analyze'?  I'm running 7.1.1 now.

Thanks,
David

------=_NextPart_000_00C1_01C0F400.F8D28F20
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4613.1700" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Does a 'vacuum analyze' do the same functi=
ons as a=20
plain 'vacuum' PLUS the stats updates?&nbsp; Just wondering if I need to ru=
n=20
both commands from time to time, or if I can just do 'vacuum analyze'?&nbsp=
; I'm=20
running 7.1.1 now.<BR><BR>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>David</FONT></DIV></BODY></HTML>

------=_NextPart_000_00C1_01C0F400.F8D28F20--




From peter_e@gmx.net Wed Jun  13 20:37:02 2001
Received: from mailout02.sul.t-online.de (mailout02.sul.t-online.com
	[194.25.134.17])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5DKb2E01998
	for <pgsql-general@postgresql.org>;
	Wed, 13 Jun 2001 16:37:02 -0400 (EDT) (envelope-from peter_e@gmx.net)
Received: from fwd06.sul.t-online.de by mailout02.sul.t-online.de with smtp
	id 15AHNj-0007wR-04; Wed, 13 Jun 2001 22:36:51 +0200
Received: from peter.localdomain (520083510237-0001@[212.185.245.34]) by
	fmrl06.sul.t-online.com
	with esmtp id 15AHNf-28YwnAC; Wed, 13 Jun 2001 22:36:47 +0200
Date: Wed, 13 Jun 2001 22:38:24 +0200 (CEST)
From: Peter Eisentraut <peter_e@gmx.net>
To: David Wall <d.wall@computer.org>
cc: <pgsql-general@postgresql.org>
Subject: Re: Vacuum analyze in 7.1.1
In-Reply-To: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
Message-ID: <Pine.LNX.4.30.0106132237130.756-100000@peter.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Sender: 520083510237-0001@t-dialin.net
X-Archive-Number: 200106/496
X-Sequence-Number: 10984

David Wall writes:

> Does a 'vacuum analyze' do the same functions as a plain 'vacuum' PLUS the stats updates?  Just wondering if I need to run both commands from time to time, or if I can just do 'vacuum analyze'?  I'm running 7.1.1 now.

yes / no / yes

However, depending on your application you might want to run 'vacuum' more
often than 'vacuum analyze'.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter




From tgl@sss.pgh.pa.us Wed Jun  13 21:33:35 2001
Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5DLXZE22317
	for <pgsql-general@postgresql.org>;
	Wed, 13 Jun 2001 17:33:35 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f5DLXP003104;
	Wed, 13 Jun 2001 17:33:25 -0400 (EDT)
To: "David Wall" <d.wall@computer.org>
cc: pgsql-general@postgresql.org
Subject: Re: Vacuum analyze in 7.1.1 
In-reply-to: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com> 
References: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
Comments: In-reply-to "David Wall" <d.wall@computer.org>
	message dated "Wed, 13 Jun 2001 12:04:17 -0700"
Date: Wed, 13 Jun 2001 17:33:25 -0400
Message-ID: <3101.992468005@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200106/500
X-Sequence-Number: 10988

"David Wall" <d.wall@computer.org> writes:
> Does a 'vacuum analyze' do the same functions as a plain 'vacuum' PLUS the
> stats updates?

Yes, exactly.

AFAIK that's always been true, but some of the documentation has been
confused about it :-(.  If you can find anything in the 7.1 docs that
sounds like VACUUM ANALYZE doesn't include VACUUM, please point it out.

			regards, tom lane



From d.wall@computer.org Wed Jun  13 22:55:29 2001
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5DMtTE51813
	for <pgsql-general@postgresql.org>;
	Wed, 13 Jun 2001 18:55:29 -0400 (EDT)
	(envelope-from d.wall@computer.org)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id PAA12132;
	Wed, 13 Jun 2001 15:55:13 -0700
Message-ID: <006501c0f45b$eca1c580$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <d.wall@computer.org>
From: "David Wall" <d.wall@computer.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-general@postgresql.org>
References: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
	<3101.992468005@sss.pgh.pa.us>
Subject: Re: Vacuum analyze in 7.1.1 
Date: Wed, 13 Jun 2001 15:55:21 -0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200106/503
X-Sequence-Number: 10993

> AFAIK that's always been true, but some of the documentation has been
> confused about it :-(.  If you can find anything in the 7.1 docs that
> sounds like VACUUM ANALYZE doesn't include VACUUM, please point it out.

Thanks.  The documentation doesn't say that VACUUM ANALYZE doesn't do a
regular VACUUM as well, but it does say:

"VACUUM serves two purposes in Postgres as both a means to reclaim storage
and also a means to collect information for the optimizer."  So, I know it
does two things.  It then says:

"VACUUM ANALYZE collects statistics representing the dispersion of the data
in each column. This information is valuable when several query execution
paths are possible."  This is also clear to me, but it left open the
possibility that this is ALL that ANALYZE does, and does not clean out
records and update the stats as VACUUM does.  The docs say that VACUUM:

"VACUUM opens every table in the database, cleans out records from rolled
back transactions, and updates statistics in the system catalogs. The
statistics maintained include the number of tuples and number of pages
stored in all tables."

Isn't also true that VACUUM cleans out deleted and updated rows for
committed transactions?  And for those who use large objects, I understand
that vacuum now cleans those up when they are deleted as well (no need for
vacuumlo anymore).  Perhaps it would be clear to just add words to that
affect, and to mention that VACUUM ANALYZE does a VACUUM plus it collects
statistics...


David





From tgl@sss.pgh.pa.us Thu Jun  14 01:17:25 2001
Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5E1HOE91113
	for <pgsql-general@postgresql.org>;
	Wed, 13 Jun 2001 21:17:25 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f5E1HB015663;
	Wed, 13 Jun 2001 21:17:11 -0400 (EDT)
To: "David Wall" <d.wall@computer.org>
cc: pgsql-general@postgresql.org
Subject: Re: Vacuum analyze in 7.1.1 
In-reply-to: <006501c0f45b$eca1c580$5a2b7ad8@expertrade.com> 
References: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
	<3101.992468005@sss.pgh.pa.us>
	<006501c0f45b$eca1c580$5a2b7ad8@expertrade.com>
Comments: In-reply-to "David Wall" <d.wall@computer.org>
	message dated "Wed, 13 Jun 2001 15:55:21 -0700"
Date: Wed, 13 Jun 2001 21:17:11 -0400
Message-ID: <15660.992481431@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200106/508
X-Sequence-Number: 10998

"David Wall" <d.wall@computer.org> writes:
> The docs say that VACUUM:

> "VACUUM opens every table in the database, cleans out records from rolled
> back transactions, and updates statistics in the system catalogs. The
> statistics maintained include the number of tuples and number of pages
> stored in all tables."

> Isn't also true that VACUUM cleans out deleted and updated rows for
> committed transactions?

Okay.  As far as the VACUUM reference page goes, that's already changed
for 7.2 --- you might like to look at

http://www.ca.postgresql.org/devel-corner/docs/postgres/sql-vacuum.html

The current description there is:

    VACUUM reclaims storage occupied by deleted tuples. In normal Postgres
    operation, tuples that are DELETEd or obsoleted by UPDATE are not
    physically removed from their table; they remain present until a VACUUM
    is done.  Therefore it's necessary to do VACUUM periodically, especially
    on frequently-updated tables.

    With no parameter, VACUUM processes every table in the current
    database. With a parameter, VACUUM processes only that table.

    VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
    table. This is a handy combination form for routine maintenance
    scripts. See ANALYZE for more details about its processing.

(The last para refers to the fact that ANALYZE is also available as a
separate command in 7.2; that wasn't true before.)

I was mainly wondering if you'd come across any misstatements other than
on the command reference page...

> And for those who use large objects, I understand that vacuum now
> cleans those up when they are deleted as well (no need for vacuumlo
> anymore).

Mmm, not really.  As of 7.1, VACUUM will clean up space occupied in
pg_largeobject by deleted or modified LOs.  However, it doesn't try to
detect whether an LO still has any references or not, so you still need
vacuumlo if your application is not careful to issue lo_unlink at all
the right times.  Not sure where this should be documented.  The docs
on large objects are pretty bad anyway :-(

			regards, tom lane



From d.wall@computer.org Thu Jun  14 04:01:06 2001
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5E416E39321;
	Thu, 14 Jun 2001 00:01:06 -0400 (EDT)
	(envelope-from d.wall@computer.org)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id VAA12726;
	Wed, 13 Jun 2001 21:00:59 -0700
Message-ID: <016901c0f486$a6372240$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <d.wall@computer.org>
From: "David Wall" <d.wall@computer.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-general@postgresql.org>, <pgsql-interfaces@postgresql.org>
References: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
	<3101.992468005@sss.pgh.pa.us>
	<006501c0f45b$eca1c580$5a2b7ad8@expertrade.com>
	<15660.992481431@sss.pgh.pa.us>
Subject: Re: Vacuum analyze in 7.1.1 
Date: Wed, 13 Jun 2001 21:01:08 -0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200106/511
X-Sequence-Number: 11001

The new vacuum comments are much more clear.  Bravo.

As for the large objects, I'll have to see what happens when I delete a row
that contains a large object.  Sounds like they are not being deleted for me
as I had thought.  That's too bad since I know it must be a common problem
for those who use them.  I use the JDBC library, so I don't really even
create the large objects except through standard JDBC calls like
PreparedStatement.setBytes().  I'll have to see if JDBC does an unlink
anywhere.

David




From dwall@Yozons.com Wed Jun  20 17:04:42 2001
Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28])
	by postgresql.org (8.11.3/8.11.1) with SMTP id f5KH4ga02989
	for <pgsql-general@postgresql.org>;
	Wed, 20 Jun 2001 13:04:42 -0400 (EDT)
	(envelope-from pgsql-general-owner@postgresql.org)
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5E4FKE43938;
	Thu, 14 Jun 2001 00:15:21 -0400 (EDT)
	(envelope-from dwall@Yozons.com)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id VAA12758;
	Wed, 13 Jun 2001 21:15:20 -0700
Message-ID: <019d01c0f488$a520e560$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <dwall@Yozons.com>
From: "David Wall" <dwall@Yozons.com>
To: <pgsql-general@postgresql.org>, <pgsql-interfaces@postgresql.org>
References: <00c401c0f43b$a5423000$5a2b7ad8@expertrade.com>
	<3101.992468005@sss.pgh.pa.us>
	<006501c0f45b$eca1c580$5a2b7ad8@expertrade.com>
	<15660.992481431@sss.pgh.pa.us>
	<016901c0f486$a6372240$5a2b7ad8@expertrade.com>
Subject: Re: Vacuum analyze in 7.1.1 
Date: Wed, 13 Jun 2001 21:15:28 -0700
Organization: Yozons, Inc.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200106/742
X-Sequence-Number: 11246

> I'll have to see if JDBC does an unlink anywhere.

Whoa!  Looks like JDBC (as makes sense if you think about the libraries)
doesn't unlink those large objects.  There is a call in the
postgresql-specific for doing the lo_unlink, but it requires an OID, and
when deleting a row, most of the time I only know the primary key, not the
OIDs inside.  So, I guess I'll have to run vacuumlo from contrib before I do
the regular vacuum.

Does anybody know if vacuumlo is truly safe to use?  I just ran it on my
test system and it removed a bunch of orphans, but I just hope they were
really orphans!

David





From d.wall@computer.org Thu Jun  14 04:16:16 2001
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5E4GGE44302;
	Thu, 14 Jun 2001 00:16:16 -0400 (EDT)
	(envelope-from d.wall@computer.org)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id VAA12771;
	Wed, 13 Jun 2001 21:16:16 -0700
Message-ID: <01b401c0f488$c684dea0$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <d.wall@computer.org>
From: "David Wall" <d.wall@computer.org>
To: <pgsql-general@postgresql.org>, <pgsql-interfaces@postgresql.org>
Subject: Re: Vacuum analyze in 7.1.1 
Date: Wed, 13 Jun 2001 21:16:24 -0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_01B1_01C0F44E.1A153DC0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200106/512
X-Sequence-Number: 11002

This is a multi-part message in MIME format.

------=_NextPart_000_01B1_01C0F44E.1A153DC0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

> I'll have to see if JDBC does an unlink anywhere.

Whoa!  Looks like JDBC (as makes sense if you think about the libraries)
doesn't unlink those large objects.  There is a call in the
postgresql-specific for doing the lo_unlink, but it requires an OID, and
when deleting a row, most of the time I only know the primary key, not the
OIDs inside.  So, I guess I'll have to run vacuumlo from contrib before I do
the regular vacuum.

Does anybody know if vacuumlo is truly safe to use?  I just ran it on my
test system and it removed a bunch of orphans, but I just hope they were
really orphans!

David



------=_NextPart_000_01B1_01C0F44E.1A153DC0
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4613.1700" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman" size=3D3>&g=
t; I'll have=20
to see if JDBC does an unlink anywhere.<BR><BR>Whoa!&nbsp; Looks like JDBC =
(as=20
makes sense if you think about the libraries)<BR>doesn't unlink those large=
=20
objects.&nbsp; There is a call in the<BR>postgresql-specific for doing the=
=20
lo_unlink, but it requires an OID, and<BR>when deleting a row, most of the =
time=20
I only know the primary key, not the<BR>OIDs inside.&nbsp; So, I guess I'll=
 have=20
to run vacuumlo from contrib before I do<BR>the regular vacuum.<BR><BR>Does=
=20
anybody know if vacuumlo is truly safe to use?&nbsp; I just ran it on my<BR=
>test=20
system and it removed a bunch of orphans, but I just hope they were<BR>real=
ly=20
orphans!<BR><BR>David</FONT><BR><BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_01B1_01C0F44E.1A153DC0--