2009/9/29 tomrevam <tomer@xxxxxxxxx>: > > 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@xxxxxxxxxxxxxx) > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
