Обсуждение: CPU Intensive query

On Fri, May 18, 2007 at 09:02:52AM -0700, Abu Mushayeed wrote:
> I have an interesting problem. I have the following query that ran ok on
> Monday and Tuesday and it has been running ok since I have been at this
> job. I have seen it to be IO intensive, but since Wednesday it has become
> CPU intensive. Database wise fresh data has been put into the tables,
> vacuumed & analyzed, no other parameter has been modified.

What Postgres version is this?

>   The query is as follows and it's explain plan is also attached:

Normally EXPLAIN ANALYZE data would be much better than EXPLAIN, but if the
query indeed does not finish, it's not going to help much.

>   set enable_nestloop = off;

What's the rationale for this?

>   HashAggregate  (cost=152555.97..152567.32 rows=267 width=162)

152000 disk page fetches is a bit, but it shouldn't take 24 hours. There's
probably misestimation involved at some point here. Does it really return 267
rows, or many more?

/* Steinar */
--
Homepage: http://www.sesse.net/

On Fri, May 18, 2007 at 09:02:52AM -0700, Abu Mushayeed wrote:
> I have an interesting problem. I have the following query that ran ok on
> Monday and Tuesday and it has been running ok since I have been at this
> job. I have seen it to be IO intensive, but since Wednesday it has become
> CPU intensive. Database wise fresh data has been put into the tables,
> vacuumed & analyzed, no other parameter has been modified.

What Postgres version is this?

> The query is as follows and it's explain plan is also attached:

Normally EXPLAIN ANALYZE data would be much better than EXPLAIN, but if the
query indeed does not finish, it's not going to help much.

> set enable_nestloop = off;

What's the rationale for this?

> HashAggregate (cost=152555.97..152567.32 rows=267 width=162)

152000 disk page fetches is a bit, but it shouldn't take 24 hours. There's
probably misestimation involved at some point here. Does it really return 267
rows, or many more?

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Abu Mushayeed <abumushayeed@yahoo.com> writes:
>   The query is as follows and it's explain plan is also attached:

The query itself seems to be a simple join over not too many rows, so
I don't see how it could be taking 24 hours.  What I suspect is you're
incurring lots and lots of invocations of those user-written functions
and one of them has suddenly decided to get very slow.

            regards, tom lane

Abu Mushayeed writes:
> The query is as follows and it's explain plan is also attached:

The query itself seems to be a simple join over not too many rows, so
I don't see how it could be taking 24 hours. What I suspect is you're
incurring lots and lots of invocations of those user-written functions
and one of them has suddenly decided to get very slow.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Fri, May 18, 2007 at 02:37:27PM -0700, Abu Mushayeed wrote:
>>> set enable_nestloop = off;
>> What's the rationale for this?
> To eliminate nested loop. It does a nested loop betwwen to very large
> table(millions of rows).

If the planner chooses a nested loop, it is because it believes it is the
most efficient solution. I'd turn it back on and try to figure out why the
planner was wrong. Note that a nested loop with an index scan on one or both
sides can easily be as efficient as anything.

Did you ANALYZE your tables recently? If the joins are really between
millions of rows and the planner thinks it's a couple thousands, the stats
sound rather off...

>>> HashAggregate (cost=152555.97..152567.32 rows=267 width=162)
>> 152000 disk page fetches is a bit, but it shouldn't take 24 hours. There's
>> probably misestimation involved at some point here. Does it really return 267
>> rows, or many more?
> It returns finally about 19-20 thousand rows.

So the planner is off by a factor of at least a hundred. That's a good
first-level explanation for why it's slow, at least...

If you can, please provide EXPLAIN ANALYZE output for your query (after
running ANALYZE on all your tables, if you haven't already); even though
it will take some time, it usually makes this kind of performance debugging
much easier.

/* Steinar */
--
Homepage: http://www.sesse.net/

On Sat, May 19, 2007 at 12:32:33AM +0200, Steinar H. Gunderson wrote:
> Did you ANALYZE your tables recently? If the joins are really between
> millions of rows and the planner thinks it's a couple thousands, the stats
> sound rather off...

Sorry, I forgot your first e-mail where you said you had both vacuumed and
analyzed recently. The estimates are still off, though -- the WHERE query
might be difficult to estimate properly. (I'm not sure how Tom arrived on
his conclusion of expensive user-defined functions, but given the usual
precisions of his guesses, I'd check that too...)

/* Steinar */
--
Homepage: http://www.sesse.net/

On Fri, May 18, 2007 at 03:26:08PM -0700, Abu Mushayeed wrote:

>   Also, this query ran today and it already finished. Today it was
>   IO intensive.

Are you entirely sure that it's not a coincidence, and something
_else_ in the system is causing the CPU issues?

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
        --Roger Brinner