Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 2ABF69FB278
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 15:14:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 25870-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 15:14:01 -0400 (AST)
X-Greylist: delayed 01:53:43.412457 by SQLgrey-1.7.4
Received: from myemma.com (db1.e2ma.net [66.179.147.163])
	by postgresql.org (Postfix) with ESMTP id EE4759FB276
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Jan 2007 15:14:02 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by myemma.com (Postfix) with ESMTP id EE27A23CDB;
	Thu, 11 Jan 2007 13:14:01 -0600 (CST)
Received: from myemma.com ([127.0.0.1])
	by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 18448-01; Thu, 11 Jan 2007 13:14:00 -0600 (CST)
Received: from [10.0.23.48] (unknown [209.149.59.218])
	by myemma.com (Postfix) with ESMTP id F08F423CD4;
	Thu, 11 Jan 2007 13:13:59 -0600 (CST)
Message-ID: <45A68C74.4080405@myemma.com>
Date: Thu, 11 Jan 2007 13:13:56 -0600
From: Kim <kim@myemma.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: unusual performance for vac following 8.2 upgrade
References: <45A671CD.1050706@myemma.com> <27939.1168539652@sss.pgh.pa.us>
In-Reply-To: <27939.1168539652@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------070004010908080009050809"
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at myemma.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.29 tagged_above=0 required=5 tests=AWL, BAYES_40,
	FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200701/181
X-Sequence-Number: 22782

This is a multi-part message in MIME format.
--------------070004010908080009050809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hey Tom,
We were running on 8.1.1 previous to upgrading to 8.2, and yes, we 
definitely have a heafty pg_class. The inheritance model is heavily used 
in our schema (the results of the group by you wanted to see are down 
below).  However, no significant problems were seen with vacs while we 
were on 8.1. Execution time for the vac seemed more linked to large 
table size and how active the table was with updates, rather than being 
universally over 10 minutes regardless of the vac's object. We will be 
doing an audit of our 8.2 install to try and make sure that it looks 
like a complete install, any tests you can think of that may further 
narrow things down for us?



 relkind | count
---------+--------
 v       |   1740
 t       |  49986
 c       |      4
 S       |     57
 r       | 108689
 i       | 374723
(6 rows)



Tom Lane wrote:

>Kim <kim@myemma.com> writes:
>  
>
>>size of pg_class: 535,226
>>number of relations: 108,694
>>    
>>
>
>Oh, I shoulda read all the way to the bottom of your email :-(.  What
>version of PG were you running before?  I would think that pretty much
>any version of pgstat_vacuum_tabstats would have had a performance issue
>with pg_class that large.  Also, could we see
>
>	select relkind, count(*) from pg_class group by relkind;
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>  
>

--------------070004010908080009050809
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hey Tom,<br>
We were running on 8.1.1 previous to upgrading to 8.2, and yes, we
definitely have a heafty pg_class. The inheritance model is heavily
used in our schema (the results of the group by you wanted to see are
down below).&nbsp; However, no significant problems were seen with vacs
while we were on 8.1. Execution time for the vac seemed more linked to
large table size and how active the table was with updates, rather than
being universally over 10 minutes regardless of the vac's object. We
will be doing an audit of our 8.2 install to try and make sure that it
looks like a complete install, any tests you can think of that may
further narrow things down for us? <br>
<br>
<br>
<br>
<tt>&nbsp;relkind | count<br>
---------+--------<br>
&nbsp;v&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 1740<br>
&nbsp;t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 49986<br>
&nbsp;c&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4<br>
&nbsp;S&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 57<br>
&nbsp;r&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 108689<br>
&nbsp;i&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 374723<br>
(6 rows)<br>
</tt><br>
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid27939.1168539652@sss.pgh.pa.us" type="cite">
  <pre wrap="">Kim <a class="moz-txt-link-rfc2396E" href="mailto:kim@myemma.com">&lt;kim@myemma.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">size of pg_class: 535,226
number of relations: 108,694
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Oh, I shoulda read all the way to the bottom of your email :-(.  What
version of PG were you running before?  I would think that pretty much
any version of pgstat_vacuum_tabstats would have had a performance issue
with pg_class that large.  Also, could we see

	select relkind, count(*) from pg_class group by relkind;

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               <a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a>

  </pre>
</blockquote>
</body>
</html>

--------------070004010908080009050809--