I have a table that II am constantly inserting into (around 10 times a
second right now but hope to increase latter). I hold these rows for a
week then summarize and delete them. During that week I need to access
ranges of these rows based on a timestamp in each row set to now() when I
insert them. I have this column indexed but if I let the system run after
a few weeks it can take a fairly long time to get information from this
table (for instance the avg() of inserted values in the last 5 minuets
could take over a minuet to calculate). If I run CLUSTER on the index of
the time stamps my time to do this drops down to under 5 seconds again. I
checked the plan being used by explain analyze and orginally it was always
doing seqscans so I set enable_seqscan = FALSE just before I run this
query, this forces it to use the index and speeds it up quite a bit.
Does anyone know a way I can reorder the database without doing a
CLUSTER? It stops all insertions into the table and takes several minuets
during which time a large backlog builds up. Or should I be using some
other method of speeding up the table?
--- Adam Kavan
--- akavan@cox.net