Hi.
Moreover, what I find very strange is that when I log in with psql (or any other SQL client) onto the production database and execute the same query, it runs fast - even in peak hours!
That is strange. Is this a networking issue?
It is not. psql was executed on the same machine as the running production system.
> Are your clients becoming memory
bound (e.g. not enough RAM or poor garbage collection/memory leakage on the client side)?
The slow down is not on the client side: databse logs show long statement durations as well.
Quite strangely, but the problem disappeared. I have got now idea what happened, but statistics work very fast now.
Few hypethesis that I have are:
1. Documentation says indexes may be potentially blocking. This could have been the case: a long query uses an index and, as this index is blocked by concurrent inserts (which are MANY), the query is blocked as well. Queries also run in transactional environment (Spring transactional proxies), so it might have been wrong transaction isolation level or absence of the transaction or yet something else. this could explain why the same query in psql ran fast. Yesterday I've optimized the query a bit getting read of the join, so if new query does not use indexes, it is not blocked and is fast.
2. Some kind of index malfunction - the index on call_id was created yesterday manually on the prod. system. It might have not been "actual" or for some reasons not used by the queries ran from the prod. system (got now idea how could that happen). After the update yesterday late at night, the prod. system software was restarted - this could have helped, maybe...
3. Nightly ran analyze...
Still, these are only vague theories. I got no idea what actually happened, but am glad that it works.
Bye. /lexi
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
