Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id EE9061336FA7
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  6 Dec 2010 03:10:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 35156-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  6 Dec 2010 07:10:25 +0000 (UTC)
X-Greylist: delayed 00:06:31.316916 by SQLgrey-1.7.6
Received: from nm3-vm0.bullet.mail.sp2.yahoo.com
	(nm3-vm0.bullet.mail.sp2.yahoo.com [98.139.90.230])
	by mail.postgresql.org (Postfix) with SMTP id 09B88133655C
	for <pgsql-admin@postgresql.org>; Mon,  6 Dec 2010 03:10:24 -0400 (AST)
Received: from [98.139.91.66] by nm3.bullet.mail.sp2.yahoo.com with NNFMP;
	06 Dec 2010 07:03:52 -0000
Received: from [98.139.91.22] by tm6.bullet.mail.sp2.yahoo.com with NNFMP;
	06 Dec 2010 07:03:52 -0000
Received: from [127.0.0.1] by omp1022.mail.sp2.yahoo.com with NNFMP;
	06 Dec 2010 07:03:52 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 340523.41580.bm@omp1022.mail.sp2.yahoo.com
Received: (qmail 9161 invoked by uid 60001); 6 Dec 2010 07:03:52 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1291619032; bh=wspJWiQbiwxxZqXU1rHwc9zgserghrztMopJiMx1zUg=;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=3xR/YQ+gL0eQO1DUAS1iTEE5TFdgh5BllmVo9JJlF54JPk3fEcqUnjZimh8wsEUNv5xo0h+lXmGKJQwnHGtato+8mTowDAqXJK0CfM7/vLivlVsMsvD3q4jhrHYEzDBovVlDxbwGlfZeV/ufI4V5B7xoinRLQKMkTmvHIfQAbh0=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=YIROB+28PtDHHukphxV/b9g747MVoMnYPH4TkPM3YLhNe9fW8+2yy/RTFUjMrJFusYk5hrdyxK/TdPxNIus6+xARe0symtxgyGHkGAhWvVwPwZLIDrfDrCuAF3kerUS61vAiBaWCDy5hzc1zcm6q3zFvy1erxB14rU36eHv1ZPY=;
Message-ID: <181094.9124.qm@web46106.mail.sp1.yahoo.com>
X-YMail-OSG: VvYTkpkVM1lrzHRclSPE.41Ix3QIXymAXyxZRdXzycH7_kD
	wn9TF.0X3DxI4J43_XTJwmhoXeQOh2ws.8xzLUZa2uPi6Po.alZr4B1HVc82
	KrjyMnugs9ItvzTkv0YGKT1SIqqVREtxYCa1iLUexJbfMK0GPFnvOPKqkOSx
	LWozYcnB2ayD5JVAn_ZS3mZJyMjnU7Gc_A4C99YbshkOzR4kHy6wQqKfQ7ez vCaw-
Received: from [71.202.181.130] by web46106.mail.sp1.yahoo.com via HTTP;
	Sun, 05 Dec 2010 23:03:51 PST
X-Mailer: YahooMailRC/553 YahooMailWebService/0.8.107.285259
References: 
 <68B59BEDCD36854AADBDF17E91B2937A07844CCA60@EXCHMAIL.staff.internode.com.au>
Date: Sun, 5 Dec 2010 23:03:51 -0800 (PST)
From: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: pg_* Tables
To: Samuel Stearns <SStearns@internode.com.au>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: 
 <68B59BEDCD36854AADBDF17E91B2937A07844CCA60@EXCHMAIL.staff.internode.com.au>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1401394294-1291619031=:9124"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.346 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001,
	HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: **
X-Archive-Number: 201012/39
X-Sequence-Number: 36268

--0-1401394294-1291619031=:9124
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Samuel Stearns wrote:=0A=0A=0A=0A>> Howdy,=0A>> =0A=0A>> Environment:=0A>> =
 =0A=0A>> Solaris 10=0A>> Postgres 8.3.3=0A>>  =0A=0A>> I=E2=80=99m getting=
 high sequential scans for some pg_* tables:=0A>>  =0A=0A>> <database>=3D# =
select relname, sum(seq_scan) as seq_scan,sum(seq_tup_read)=0A>> as seq_tup=
_read,sum(idx_scan) as idx_scan, sum(idx_tup_fetch) as =0A>idx_tup_fetch,=
=0A>> sum(n_tup_ins) as n_tup_ins, sum(n_tup_upd) as n_tup_upd, sum(n_tup_d=
el) as =0A>>n_tup_del=0A>> from  pg_stat_all_tables group by 1 order by 2 d=
esc limit 4;=0A>>              relname             | seq_scan | seq_tup_rea=
d | idx_scan | =0A>>idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del=0A>>-=
--------------------------------+----------+--------------+----------+-----=
----------+-----------+-----------+-----------=0A>>-=0A>> <table> |  689649=
8 |     91295702 |   107663 |        469057 |    103966 |    =0A>>103966 | =
   103966=0A>> pg_authid                       |  3119053 |    125950392 | =
12000732 |      =0A>>12000718 |         1 |         1 |         0=0A>> pg_a=
m                           |  2642438 |      2642440 |        5 =0A>>|    =
         5 |         0 |         0 |         0=0A>> pg_database            =
         |  1349020 |     14771768 |  6953392 |       =0A>>6953392 |       =
  0 |         0 |         0<...>=0A>> Is this normal?  Is it advisable to i=
ndex pg_* tables?=0A Do you vacuum the database regularly ?=0A=0AWhat are t=
he number of rows in those tables ?=0A=0AIt is possible that the planner is=
 doing a sequential scan because the tables =0Aare small and that's faster =
than doing indexed reads.=0A=0AGreg Williamson=0A=0A=0A      
--0-1401394294-1291619031=:9124
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:arial,helvetica,sans-serif;font-size:12p=
t"><div><font face=3D"Tahoma" size=3D"2">Samuel Stearns wrote:<br></font></=
div><div style=3D"font-family: arial,helvetica,sans-serif; font-size: 12pt;=
"><br><div style=3D"font-family: times new roman,new york,times,serif; font=
-size: 12pt;">&gt;&gt; Howdy,<div class=3D"WordSection1"><p class=3D"MsoNor=
mal"> &gt;&gt; <br></p><p class=3D"MsoNormal">&gt;&gt; Environment:</p><p c=
lass=3D"MsoNormal"> &gt;&gt;&nbsp; <br></p><p class=3D"MsoNormal">&gt;&gt; =
Solaris 10</p><p class=3D"MsoNormal">&gt;&gt; Postgres 8.3.3</p><p class=3D=
"MsoNormal"> &gt;&gt;&nbsp; <br></p><p class=3D"MsoNormal">&gt;&gt; I=E2=80=
=99m getting high sequential scans for some pg_* tables:</p><p class=3D"Mso=
Normal"> &gt;&gt;&nbsp; <br></p><p class=3D"MsoNormal">&gt;&gt; &lt;databas=
e&gt;=3D# select relname, sum(seq_scan) as seq_scan,sum(seq_tup_read)</p><p=
 class=3D"MsoNormal">&gt;&gt; as
 seq_tup_read,sum(idx_scan) as idx_scan, sum(idx_tup_fetch) as idx_tup_fetc=
h,</p><p class=3D"MsoNormal">&gt;&gt; sum(n_tup_ins) as n_tup_ins, sum(n_tu=
p_upd) as n_tup_upd, sum(n_tup_del) as n_tup_del</p><p class=3D"MsoNormal">=
&gt;&gt; from&nbsp; pg_stat_all_tables group by 1 order by 2 desc limit 4;<=
/p><p class=3D"MsoNormal">&gt;&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; relname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | seq_scan | seq_tup_read | idx_scan =
| idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del</p><p class=3D"MsoNorma=
l">&gt;&gt; ---------------------------------+----------+--------------+---=
-------+---------------+-----------+-----------+-----------</p><p class=3D"=
MsoNormal"> &gt;&gt; &lt;table&gt; |&nbsp; 6896498 |&nbsp;&nbsp;&nbsp;&nbsp=
; 91295702 |&nbsp;&nbsp; 107663 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 469057 |&nbsp;&nbsp;&nbsp; 103966 |&nbsp;&nbsp;&nbsp; 103966
 |&nbsp;&nbsp;&nbsp; 103966</p><p class=3D"MsoNormal"> &gt;&gt; pg_authid&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 3119053 |&n=
bsp;&nbsp;&nbsp; 125950392 | 12000732 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12000=
718 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp; 0</p><p class=3D"MsoNormal"> &gt;&gt; pg_am&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2642438 |=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2642440 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0
 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0</p><p class=3D"MsoNorm=
al"> &gt;&gt; pg_database&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nb=
sp; 1349020 |&nbsp;&nbsp;&nbsp;&nbsp; 14771768 |&nbsp; 6953392 |&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp; 6953392 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0</p>&lt;...&gt;<p class=3D"MsoNormal=
">&gt;&gt; Is this normal?&nbsp; Is it advisable to index pg_* tables?</p><=
p class=3D"MsoNormal"> &nbsp;</p>Do you vacuum the database regularly ?<br>=
<br>What are the number of rows in those tables ?<br><br>It is possible tha=
t the planner is doing a sequential scan because the tables are small and t=
hat's faster than doing indexed reads.<br><br>Greg Williamson<br><br></div>=
</div></div>=0A</div><br>=0A=0A      </body></html>
--0-1401394294-1291619031=:9124--