From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: 100% CPU Utilization when we run queries.
Date: Wed, 06 Jul 2011 21:04:13 +0200
Message-ID: <4E14B1AD.40306@fuzzy.cz> (raw)
In-Reply-To: <1309959046671-4556775.post@n5.nabble.com>
References: <1307506768241-4465765.post@n5.nabble.com>
<4DEF037E.9020105@postnewspapers.com.au>
<1307691557007-4475458.post@n5.nabble.com>
<4DF21007.2010807@postnewspapers.com.au>
<1308198900426-4493567.post@n5.nabble.com>
<1309959046671-4556775.post@n5.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: 100% CPU Utilization when we run queries.
In-Reply-To: <4E14B1AD.40306@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox