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

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

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: 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-25 17:05:04
Message-ID: 2f4958ff0911250905r23a59c2fud6f96dac9e201bb8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Nov 25, 2009 at 4:58 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Grzegorz Jaœkiewicz<gryzman(at)gmail(dot)com> wrote:
>
> > Other way, is to perform regular cluster && reindex
>
> If you CLUSTER there is no reason to REINDEX; indexes are rebuilt by
> the CLUSTER command.
>
> Also, if you do a good job with regular VACUUMs, there isn't any bloat
> to fix.  In that case a regular CLUSTER would only be needed if it was
> worth the cost to keep data physically organized in the index
> sequence.
>
> 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).
Thus suggestion of partitioning.  I for one, hope in 8.5 we will get much
more user friendly partitioning interface - and we would no longer have to
write custom triggers. Which is probably the only reason I am only going to
partition a table only if it is really really really ... needed.




-- 
GJ

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-11-25 17:27:22
Subject: Re: query optimization
Previous:From: Kevin GrittnerDate: 2009-11-25 16:58:20
Subject: Re: Query times change by orders of magnitude as DB ages

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