Date: Fri, 18 May 2007 18:42:10 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: Abu Mushayeed <abumushayeed@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU Intensive query
Message-ID: <20070518164210.GA15372@uio.no>
Mail-Followup-To: Abu Mushayeed <abumushayeed@yahoo.com>,
	pgsql-performance@postgresql.org
References: <877956.83201.qm@web57112.mail.re3.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <877956.83201.qm@web57112.mail.re3.yahoo.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

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/