Re: 100% CPU Utilization when we run queries.

On Wed, Jul 6, 2011 at 9:04 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> 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

In this case UNION ALL is probably more appropriate than UNION - and
may have different performance characteristics (saving the UNIQUE?).

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/