Re: query is taking longer time after a while

2009/9/29 tomrevam <tomer(at)fabrix(dot)tv>:
>
> I have a table with 5 million rows. 10 inserts and deletes are performed on
> this table every second.
> The table has indexes on the columns I use to query it, and the query is
> returning about a 1000 rows. Initially the query takes a very short time
> (order of miliseconds), after a few hours it takes hundreds of miliseconds,
> and after a few days it can take more than 10 seconds. When this happens it
> also blocks all other operations on the database and I see very long times
> for all of them.

When did you last do an analyse and re-create indexes?
Is the table UPDATEd to or DELETEd from, or just INSERTed ?
Is your DB auto vacuuming?

I found that if you delete a lot of rows, the index seems to still
have entries for those deleted rows, though it automatically skips
them, this "skipping of them" takes time.
Also, if the index was created when the table was smalleror the types
of values were quite different, then the parameters for the index will
be wrong for the current total data. So it will be inefficient.
Tables that are frequently UPDATEd become fragmented, and need VACUUM.

I have a table from which I deleted 1/3 of its contents (32 million
rows), and then if I did an index search that would have included
those deleted rows, it took an age. Hours in fact! So I had to first
run ANALYSE on the table, create a new index, then delete the old and
rename the new index.

> I thought this may be caused by the indexes not remaining in the memory, but
> I increased the shared_buffers to 0.5 GB and this didn't seem to help.
> --
> View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25661219.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> 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/