Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id AAA27690 for ; Sat, 30 Oct 1999 00:11:50 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id AAA05524; Sat, 30 Oct 1999 00:11:11 -0400 (EDT) To: Werner Reisberger cc: pgsql-sql@hub.org Subject: Re: [SQL] tuning large selects In-reply-to: Your message of Fri, 29 Oct 1999 21:36:03 +0200 <19991029213603.B830@tribe.free.de> Date: Sat, 30 Oct 1999 00:11:11 -0400 Message-ID: <5522.941256671@sss.pgh.pa.us> From: Tom Lane Werner Reisberger 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