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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-11-25 17:27:22 | Re: query optimization |
Previous Message | Kevin Grittner | 2009-11-25 16:58:20 | Re: Query times change by orders of magnitude as DB ages |