Hi Andy,
Thanks for your reply. I don't know the answers to your questions off the top of my head, so you have given me some things to look into (open transactions, memory swap, etc).
Now I am suspecting that memory is a partial culprit as I just discovered that rebooting (hadn't had the opportunity to do that) also seems to boost query performance to a lesser degree. I'm going to take a hard look at that.
It will take me several days to come up with an "explain analyze" when it's fast and when it's slow, so I'll come back with that when I have it ready if I had not resolved the issue by then. Good idea.
Can you post an "explain analyze" when its fast, and then again when its slow?
Are you leaving transactions open? (does "select * from pg_stat_activity" show any "idle in transaction"?)
Does memory usage increase during the week so it starts using swap?
-Andy
