Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T2dyg-0005KZ-1y for pgsql-performance@postgresql.org; Sat, 18 Aug 2012 08:01:50 +0000 Received: from nm30-vm3.bullet.mail.ne1.yahoo.com ([98.138.91.160]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1T2dyd-0003gQ-Cy for pgsql-performance@postgresql.org; Sat, 18 Aug 2012 08:01:48 +0000 Received: from [98.138.90.56] by nm30.bullet.mail.ne1.yahoo.com with NNFMP; 18 Aug 2012 08:01:45 -0000 Received: from [98.138.89.195] by tm9.bullet.mail.ne1.yahoo.com with NNFMP; 18 Aug 2012 08:01:45 -0000 Received: from [127.0.0.1] by omp1053.mail.ne1.yahoo.com with NNFMP; 18 Aug 2012 08:01:45 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 86368.16300.bm@omp1053.mail.ne1.yahoo.com Received: (qmail 37978 invoked by uid 60001); 18 Aug 2012 08:01:45 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1345276905; bh=A1hzm6nGj63M3k8bDMvY+ZXckP4DX3jVvBv9qX3gWZg=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:Cc:In-Reply-To:MIME-Version:Content-Type; b=0Xl8y0/jpjGYoxBBNLPdo7CawAnlPQ3k1hdim2twTEbWvkKxRhK/wbDwoVBvv84gQwm1cEs7hFYFj+UMs8mhxHMyY6mQqJons3PW1X9XuQw6RczbvKkoB5KLnoUDykJdpxN1zjR834a4pY1YQsmxPXwG+S12RoQz66JLlDNjTyo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:Cc:In-Reply-To:MIME-Version:Content-Type; b=4XiixlFXhDev6DxZdaKlaaBvkwIyPT5EZ1VofnqhUNpleMqfYZilgllOxMUp3GQVvjOSVmeIF9z0r/7+uBPYl6g/StXA6HVMCqDoNmVHk/HM5LIZfm1j/rch4I+/pArLWpIiGDaPpD19fQRmEymUZyg90xP1UJzpnwAi86kK82E=; X-YMail-OSG: JxDH6IQVM1lVKbEUIjBkQJffgrhtOMYXsdsEqMjXNpN5PJt r_SSyEWuYsLzYogEEkbd8qFD2qLx.ULHw8h007wrJ4XDasPN.B4NAF6MsGsd OQ6iTjQ7Z11vi6rRSuUZfduA48VG1vVM0oWQ.XRsY26WafnTNRRvUXv4rMfF fppOC96YbjaEikgCOn2Q.JFoiluAsZ.vBr0QJ6EmkwwH4y0zb.HFIUYvwCXg qlFEy7D8U5C5ajVn5kKylgBbEzvRlcQiDZQLZmKKJ.yYemmaG5yUTFQjxwKm dfVlH6L6_9oJ3.chjxbL..74c7W.e1YUS6Vzvjv1x9Py3ijCN65OBLijgOy5 rsqm2F8TQjLR62lmce4s3Rsn5VzSDyclr1ZbUZCDoJFBnXu1NH9iFAn61CsY lqblgt2ygNZu3gqjqcChtB0UMPZ.UZkHogFW4DTeqpZfC_1LGfMUfzddldw3 ZYOe_d8dk8C8mGwLTbfk- Received: from [98.210.146.78] by web125902.mail.ne1.yahoo.com via HTTP; Sat, 18 Aug 2012 01:01:44 PDT X-Mailer: YahooMailWebService/0.8.120.356233 References: Message-ID: <1345276904.17534.YahooMailNeo@web125902.mail.ne1.yahoo.com> Date: Sat, 18 Aug 2012 01:01:44 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: Re: Index Bloat Problem Cc: "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="90367331-360013250-1345276904=:17534" X-Pg-Spam-Score: -0.5 (/) X-Archive-Number: 201208/123 X-Sequence-Number: 47661 --90367331-360013250-1345276904=:17534 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Thanks for this description--we have index bloat problems on a massively ac= tive (but small) database.This may help shed light on our problems.=0A=0ASo= rry for top-posting--challenged email reader.=0A=0AGreg W.=0A=0A=0A=0A>____= ____________________________=0A> From: Jeff Janes =0A= >To: Strahinja Kustudi=C4=87 =0A>Cc: pgsql-perform= ance@postgresql.org =0A>Sent: Friday, August 17, 2012 7:33 PM=0A>Subject: R= e: [PERFORM] Index Bloat Problem=0A> =0A>On Thu, Aug 16, 2012 at 12:57 PM, = Strahinja Kustudi=C4=87=0A> wrote:=0A>>=0A>> @Jeff = I'm not sure if I understand what you mean? I know that we never reuse=0A>>= key ranges. Could you be more clear, or give an example please.=0A>=0A>If = an index leaf page is completely empty because every entry on it=0A>were de= leted, it will get recycled to be used in some other part of=0A>the index.= =C2=A0 (Eventually--it can take a while, especially if you have=0A>long-run= ning transactions).=0A>=0A>But if the leaf page is only mostly empty, becau= se only most of=0A>entries on it were deleted, than it can never be reused,= except for=0A>entries that naturally fall into its existing key range (whi= ch will=0A>never happen, if you never reuse key ranges)=0A>=0A>So if you ha= ve a million records with keys 1..1000000, and do a=0A>"delete from foo whe= re key between 1 and 990000", then 99% of those=0A>old index pages will bec= ome completely empty and eligible for reuse.=0A>But if you do "delete from = foo where key%100>0", then all of the pages=0A>will become 99% empty, and n= one will be eligible for reuse (except the=0A>very last one, which can stil= l accept 1000001 and so on)=0A>=0A>There has=C2=A0 been talk of allowing lo= gically adjacent, mostly empty=0A>pages to be merged so that one of them be= comes empty, but the way=0A>concurrent access to btree indexes was designed= this is extremely hard=0A>to do safely.=0A>=0A>Cheers,=0A>=0A>Jeff=0A>=0A>= =0A>-- =0A>Sent via pgsql-performance mailing list (pgsql-performance@postg= resql.org)=0A>To make changes to your subscription:=0A>http://www.postgresq= l.org/mailpref/pgsql-performance=0A>=0A>=0A> --90367331-360013250-1345276904=:17534 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Thanks for this descr= iption--we have index bloat problems on a massively active (but small) data= base.This may help shed light on our problems.

<= div>Sorry for top-posting--challenged email reader.

Greg W.

<= b>From: Jeff Janes <jeff.ja= nes@gmail.com>
To: = Strahinja Kustudi=C4=87 <strahinjak@nordeus.com>
Cc: pgsql-performance@postgresql.org
Sent: Friday, August 17, 2012 7:33 PM Subject: Re: [PERFORM] I= ndex Bloat Problem

=0AOn Thu, Aug 16, 2012 at 12:57 = PM, Strahinja Kustudi=C4=87
<strahinjak@nordeus.com> = wrote:
>
> @Jeff I'm not sure if I understand what you mean? I = know that we never reuse
> key ranges. Could you be more clear, or gi= ve an example please.

If an index leaf page is completely empty beca= use every entry on it
were deleted, it will get recycled to be used in s= ome other part of
the index.  (Eventually--it can take a while, esp= ecially if you have
long-running transactions).

But if the leaf p= age is only mostly empty, because only most of
entries on it were delete= d, than it can never be reused, except for
entries that naturally fall i= nto its existing key range (which will
never happen, if you never reuse = key ranges)

So if you have a million records with keys 1..1000000, a= nd do a
"delete from foo where key between 1 and 990000", then 99% of those
old index pages will become completely= empty and eligible for reuse.
But if you do "delete from foo where key%= 100>0", then all of the pages
will become 99% empty, and none will be= eligible for reuse (except the
very last one, which can still accept 10= 00001 and so on)

There has  been talk of allowing logically adj= acent, mostly empty
pages to be merged so that one of them becomes empty= , but the way
concurrent access to btree indexes was designed this is ex= tremely hard
to do safely.

Cheers,

Jeff


--
= Sent via pgsql-performance mailing list (pgsql= -performance@postgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance


<= /div>
--90367331-360013250-1345276904=:17534--