The biggest impact on performance you can achieve is by using a materialized view. if it’s so heavily used as you said, even 2-3 seconds in a multiuser OLTP environment still unacceptable under my point of view. I don’t know if this is the case but if you have 1000 users connecting at 8 am all at the same time … it will freeze the app for a while .. Ask your self: how old data can be? and take into account that you can refresh the materialized view as often as you want, even every 10 secs if you want. Beides this, there there's still some room for improvement. Perhaps you have not created the right index to avoid seq scans. Have a look at indexes on expressions. On systems side: ask them if they have not changed anything in effective_cache_size and shared_buffers parameters, I presume they haven’t change anything related to costs. Regards. Daniel Blanch.
|