Re: query is taking longer time after a while

2009/9/29 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
>> 2009/9/29 tomrevam <tomer(at)fabrix(dot)tv>:
>> > My DB is auto-vacuuming all the time. The specific table I'm talking about
>> > gets vacuumed at least every 2 hours (usually a little more frequently than
>> > that).
>> > Deletes are happening on the table at about the same rate as inserts (there
>> > are also some updates).
>>
>> The index quite likely is in a poor state.
>
> Really? Plain vacuum should allow things to reach a steady state after
> a while, doing a large delete will put things out of kilter, but that
> doesn't sound to be the case here.  Vacuum full can also cause things to
> go amiss, but if it's just regular vacuums then things should be OK.

If there are a lot of deletes, then likely the index parameters are
not the best.

ANALYSE yourtable;

Then, reindex (or create new index followed by drop index and rename -
if you want to leave the index online.

> What do you get out of vacuum analyse verbose? for this table?
>
>> You could try this:
>>
>> analyse ....
>> create index ... (same parameters as existing index)
>> delete the old index.
>> rename the new index to the same name as the old one
>> repeat this for all indexes.
>
> Why not just do:
>
>  REINDEX TABLE yourbigtable;
>
> No need to worry about rebuilding foreign key constraints or anything
> like that then.

Thats OK if the table can be taken offline. REINDEX locks the index
while in progress.

>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/