pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Werner Reisberger <wreis@datacomm.ch>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] tuning large selects
Date: Sat, 30 Oct 1999 00:11:11 -0400
Message-ID: <5522.941256671@sss.pgh.pa.us> (raw)
In-Reply-To: <19991029213603.B830@tribe.free.de>

Werner Reisberger <wreis@datacomm.ch> writes:
> I am running a db (v. 6.4.2) accessible via DBI/Pg from the internet.
> Recently I observed several running postmaster processes consuming
> lots of CPU, resulting in a high load of the machine. There was nobody
> connected to the db from the net. Later I saw that my db partition was
> filled to 100 % by a huge file (pg_psort.189xx). My db partition is
> approx. 950 MB. The db consumes 15 MB and the file consumed the rest.

Yipes.  I can't see any obvious reason that a sort temp file would
even be created from the query you describe, much less that it would
grow to many times the size of the original table.  What does
"explain" show for the query you were running?

BTW: the existing sort code misbehaves if the disk fills up (it fails
to notice write errors on its temp file).  I've fixed this for 7.0,
I believe, but I don't really think that's the immediate cause of
your problem.

			regards, tom lane



view thread (2+ messages)

Message-ID: <5522.941256671@sss.pgh.pa.us>
Permalink:  ../5522.941256671@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5522.941256671@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, wreis@datacomm.ch, pgsql-sql@hub.org
  Subject: Re: [SQL] tuning large selects
  In-Reply-To: <5522.941256671@sss.pgh.pa.us>
  References: <19991029213603.B830@tribe.free.de> <5522.941256671@sss.pgh.pa.us>

* 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