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