DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=m7lxmPbwyv01iN2QxEAiW9Yvq24Tyu+cXtF5uGbz6/CVmjMdZkVwdrn98eSavsmoen0r5qJMKiP03EQB66oyZF6R+2STfa5ErLM5FWDFZ6LcBIT8E+bTdpSdgz/R7N4Vu/1/c1iVqeoqrkYOFVR6BjYjFYbpPP3U22HwS6hjSsU=;
Message-ID: <173228.29370.qm@web39701.mail.mud.yahoo.com>
Date: Fri, 24 Sep 2010 12:06:05 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Memory usage - indexes
To: Tobias Brox <tobixen@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <AANLkTinK6jGN5+q=1FYjD8EkTJTB24rc-dd3_YnNgg=5@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Tobias,=0A=0AFirst off, what version of PostgreSQL are you running?  If you=
 have 8.4, nightly vacuuming shouldn't be necessary with properly tuned aut=
ovacuum jobs.  =0A=0AThe pertinent difference between pg_stat_user_indexes =
and pg_statio_user_indexes is the latter shows the number of blocks read fr=
om disk or found in the cache.  You're correct, unused indexes will remain =
on disk, but indexes that don't completely fit into memory must be read fro=
m disk for each index scan, and that hurts performance.  (In fact, it will =
suddenly drop like  a rock.  BTDT.)  By making smaller equivalent indexes o=
n partitioned data the indexes for individual partitions are more likely to=
 stay in memory, which is particularly important when multiple passes are m=
ade over the index by a query.=0A=0AYou are correct on all the points you m=
ake concerning indexes, but point 4 is the one I'm referring to.  You disco=
vered this independently yourself, according to your anecdote about the ove=
rlapping indexes.=0A=0ABob Lunney=0A=0A=0A--- On Fri, 9/24/10, Tobias Brox =
<tobixen@gmail.com> wrote:=0A=0A> From: Tobias Brox <tobixen@gmail.com>=0A>=
 Subject: Re: [PERFORM] Memory usage - indexes=0A> To: "Bob Lunney" <bob_lu=
nney@yahoo.com>=0A> Cc: pgsql-performance@postgresql.org=0A> Date: Friday, =
September 24, 2010, 12:46 PM=0A> On 24 September 2010 18:23, Bob=0A> Lunney=
 <bob_lunney@yahoo.com>=0A> wrote:=0A> > Consult pg_statio_user_indexes to =
see which indexes=0A> have been used=0A> > and how much.=0A> =0A> What is t=
he main differences between pg_statio_user_indexes=0A> and=0A> pg_stat_user=
_indexes?=0A> =0A> >=A0 =A0Indexes with comparitively low usages rates=0A> =
aren't helping you much and are=0A> > candidates for elimination.=0A> =0A> =
No doubt about that - but the question was, would it really=0A> help us to=
=0A> drop those indexes?=0A> =0A> I think the valid reasons for dropping in=
dexes would be:=0A> =0A> 1) To speed up inserts, updates and deletes=0A> =
=0A> 2) To spend less disk space=0A> =0A> 3) Eventually, speed up nightly v=
acuum (it wouldn't be an=0A> issue with=0A> autovacuum though)=0A> =0A> 4) =
To spend less memory resources?=0A> =0A> I'm not at all concerned about 1 a=
nd 2 above - we don't=0A> have any=0A> performance issues on the write part=
, and we have plenty of=0A> disk=0A> capacity.=A0 We are still doing the ni=
ghtly vacuum=0A> thing, and it does=0A> hurt us a bit since it's dragging e=
ver more out in=0A> time.=A0 Anyway, it's=0A> number four I'm wondering mos=
t about - is it anything to be=0A> concerned=0A> about or not for the least=
 frequently used indexes?=A0=0A> An index that=0A> aren't being used would =
just stay on disk anyway,=0A> right?=A0 And if there=0A> are limited memory=
 resources, the indexes that are most=0A> frequently=0A> used would fill up=
 the cache space anyway?=A0 That's my=0A> thoughts at=0A> least - are they =
way off?=0A> =0A> We did have similar experiences some years ago - everythi=
ng=0A> was=0A> running very fine all until one day when some=0A> semi-compl=
icated=0A> very-frequently-run selects started taking several seconds=0A> t=
o run=0A> rather than tens of milliseconds.=A0 I found that we had=0A> two =
slightly=0A> overlapping indexes like this ...=0A> =0A> =A0 account_transac=
tion(customer_id, trans_type)=0A> =A0 account_transaction(customer_id, tran=
s_type,=0A> created)=0A> =0A> both of those indexes where heavily used.=A0 =
I simply=0A> dropped the first=0A> one, and the problems disappeared.=A0 I =
assume that both=0A> indexes up to=0A> some point fitted snuggly into memor=
y, but one day they=0A> were competing=0A> for the limited memory space, dr=
opping the redundant index=0A> solved the=0A> problem all until the next ha=
rdware upgrade.=A0 I would=0A> never have found=0A> those indexes searching=
 for the least used indexes in the=0A> pg_stat(io)_user_indexes view.=0A> =
=0A=0A=0A