From: Abu Mushayeed <abumushayeed@yahoo.com>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU Intensive query
Date: Fri, 18 May 2007 14:37:27 -0700 (PDT)
Message-ID: <545855.84387.qm@web57106.mail.re3.yahoo.com> (raw)
In-Reply-To: <20070518164210.GA15372@uio.no>
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.
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: abumushayeed@yahoo.com, sgunderson@bigfoot.com
Subject: Re: CPU Intensive query
In-Reply-To: <545855.84387.qm@web57106.mail.re3.yahoo.com>
* 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