Re: 100% CPU Utilization when we run queries.

Dne 6.7.2011 15:30, bakkiya napsal(a):
> Any help, please?

According to the EXPLAIN ANALYZE output (please, don't post it to the
mailing list directly - use something like explain.depesz.com, I've done
that for you this time: http://explain.depesz.com/s/HMN), you're doing a
UNIQUE over a lot of data (2 million rows, 1.5GB).

That is done by sorting the data, and sorting is very CPU intensive task
usually. So the fact that the CPU is 100% utilized is kind of expected
in this case. So that's a feature, not a bug.

In general each process is hitting some bottleneck. It might be an I/O,
it might be a CPU, it might be something less visible (memory bandwidth
or something like that).

But I've noticed one thing in your query - you're doing a UNIQUE in the
view (probably, we don't know the definition) and then once again in the
query (but using just one column from the view).

The problem is the inner sort does not remove any rows (1979735 rows
in/out). Why do you do the UNIQUE in the view? Do you really need it
there? I guess removing it might significantly improve the plan.

Try to do the query without the view - it seems it's just an union of
current tables and a history (both partitioned, so do something like this)

SELECT DISTINCT init_service_comp FROM (
SELECT init_service_comp FROM events
UNION
SELECT init_service_comp FROM hist_events
)

or maybe even

SELECT DISTINCT init_service_comp FROM (
SELECT DISTINCT init_service_comp FROM events
UNION
SELECT DISTINCT init_service_comp FROM hist_events
)

Let's see how that works - post EXPLAIN ANALYZE using explain.depesz.com

Tomas