Recently a wired question about postgresql database really bothered me a lot, so i really need your help. Here is the problem, in the most situations the postgre database work very well, Average 3500tps/s per day, the cpu usage of its process is 3%~10% and every query can be responsed in less than 20ms, but sometimes the cpu usages of its process can suddenly grow up to 90%+ , at that time a simple query can cost 2000+ms. ps: My postgresql version is 9.3.5 and the database is oltp server.
From all the lseeks on the same file, it looks like you are doing a sequential scan on a table for the inside of a nested loop. What is the query it is running, and what is the execution plan for it? To get that, run:
explain (analyze, buffers) select o_count from ....
