Skip site navigation (1) Skip section navigation (2)

Re: Query times change by orders of magnitude as DB ages

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Richard Neill <rn214(at)cam(dot)ac(dot)uk>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query times change by orders of magnitude as DB ages
Date: 2009-11-26 11:14:14
Message-ID: alpine.DEB.2.00.0911261101140.684@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 25 Nov 2009, Grzegorz Jaśkiewicz wrote:
> the out of order data layout is primary reason for index bloat. And that happens , and
> gets worse over time once data is more and more distributed. ("random" deletes, etc).

That's not index bloat. Sure, having the table not in the same order as 
the index will slow down an index scan, but that's a completely different 
problem altogether.

Index bloat is caused by exactly the same mechanism as table bloat. The 
index needs to have an entry for every row in the table that may be 
visible by anyone. As with the table, it is not possible to 
deterministically delete the rows as they become non-visible, so the 
index (and the table) will be left with dead entries on delete and update. 
The vacuum command performs garbage collection and marks these dead rows 
and index entries as free, so that some time in the future more data can 
be written to those places.

Index bloat is when there is an excessive amount of dead space in an 
index. It can be prevented by (auto)vacuuming regularly, but can only be 
reversed by REINDEX (or of course deleting the index, or adding loads of 
new entries to fill up the dead space after vacuuming).

Matthew

-- 
 for a in past present future; do
   for b in clients employers associates relatives neighbours pets; do
   echo "The opinions here in no way reflect the opinions of my $a $b."
 done; done

In response to

pgsql-performance by date

Next:From: Sergey AleynikovDate: 2009-11-26 12:11:54
Subject: Re: Query times change by orders of magnitude as DB ages
Previous:From: Robert HaasDate: 2009-11-25 23:26:50
Subject: Re: query optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group