Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6C6059FC2AA for ; Fri, 18 May 2007 18:37:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 11950-03 for ; Fri, 18 May 2007 18:37:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web57106.mail.re3.yahoo.com (web57106.mail.re3.yahoo.com [216.252.111.119]) by postgresql.org (Postfix) with SMTP id B121B9FB8C5 for ; Fri, 18 May 2007 18:37:28 -0300 (ADT) Received: (qmail 84585 invoked by uid 60001); 18 May 2007 21:37:27 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=ME8nOMX2V+EfU+Jg959tfs+sAVQpoYsTKJ3fK1MIAD13qwu8MnGGbgobDogDLJ7X1zeSnyfLl0hQPKwWXAOt/2a2UUyEaYXu9DRHXGYaZzV21bUMEASn72cCVrl2JauyDdJ/mZbkEQWVbbhPzvWGLA8lztctmsy9FTgcAGwBFoQ=; X-YMail-OSG: 6bwdSecVM1km0cqUUQ2UnsbOjFx6FJx.u7IDoR7O4SXj0aLxpGpa6JkzXA2QQQxjsQ-- Received: from [64.237.4.140] by web57106.mail.re3.yahoo.com via HTTP; Fri, 18 May 2007 14:37:27 PDT Date: Fri, 18 May 2007 14:37:27 -0700 (PDT) From: Abu Mushayeed Subject: Re: CPU Intensive query To: "Steinar H. Gunderson" Cc: pgsql-performance@postgresql.org In-Reply-To: <20070518164210.GA15372@uio.no> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1498046634-1179524247=:84387" Content-Transfer-Encoding: 8bit Message-ID: <545855.84387.qm@web57106.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.164 tagged_above=0 required=5 tests=AWL=-0.397, BAYES_05=-1.11, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: ** X-Archive-Number: 200705/350 X-Sequence-Number: 24740 --0-1498046634-1179524247=:84387 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit What Postgres version is this? 8.1.3 > 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). > 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. "Steinar H. Gunderson" wrote: 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 --------------------------------- 8:00? 8:25? 8:40? Find a flick in no time with theYahoo! Search movie showtime shortcut. --0-1498046634-1179524247=:84387 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
What Postgres version is this?
 
8.1.3

> 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).
 
> 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.


"Steinar H. Gunderson" <sgunderson@bigfoot.com> wrote:
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

8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut. --0-1498046634-1179524247=:84387--