Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 8F0CD2E0045 for ; Fri, 2 May 2008 17:27:05 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02636-06 for ; Fri, 2 May 2008 17:26:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.231]) by developer.postgresql.org (Postfix) with ESMTP id F40492E003E for ; Fri, 2 May 2008 17:26:52 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so988045rvf.43 for ; Fri, 02 May 2008 13:26:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:cc:message-id:from:to:in-reply-to:content-type:content-transfer-encoding:mime-version:subject:date:references:x-mailer; bh=egcBVNvCI1pwMqzQVgAYADmwwJHQaclHdP07ViDvFXc=; b=MeB6TuSaNC5CnOBlyNZOw8BSSCQSzTsu3eHzeOlg4jexh9JVXK4ZJYxHIshS8hAcI5uIrXuEep7o2QvJy3JRPzpmx/oSx/SOqzjvp03i5SMpmtzfMS9+cgSMy02fSrIXnGBqYbq9S+yStSJojx+AaTSWzDYKanj6JlkIs9RwtnM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=cc:message-id:from:to:in-reply-to:content-type:content-transfer-encoding:mime-version:subject:date:references:x-mailer; b=XmBhY2WPYnpZmKbwIgixHgpDsGXpmxbV9AYmfNz8/q2pezWXo7s7RsTqIPXKwfYUCrOxhI+IB3PLidIfJnZsZUFc8MqcH3SiywfO/Aw9iA9tCF9s5mYr0L/hhND8o1P1LIHs1MjwzETnlCG2kz6ogN6EkqDzrTuEDxcuQ5Ut7VI= Received: by 10.141.50.17 with SMTP id c17mr1531137rvk.146.1209760011693; Fri, 02 May 2008 13:26:51 -0700 (PDT) Received: from ?192.168.1.100? ( [24.19.22.42]) by mx.google.com with ESMTPS id g31sm5061656rvb.2.2008.05.02.13.26.50 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 02 May 2008 13:26:50 -0700 (PDT) Cc: "Scott Marlowe" , pgsql-performance@postgresql.org Message-Id: <72E02D29-848B-467A-AE6B-401568010254@gmail.com> From: Alexy Khrabrov To: Tom Lane In-Reply-To: <21403.1209759217@sss.pgh.pa.us> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v919.2) Subject: Re: two memory-consuming postgres processes Date: Fri, 2 May 2008 13:26:47 -0700 References: <27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com> <21403.1209759217@sss.pgh.pa.us> X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL=0.013 X-Spam-Level: X-Archive-Number: 200805/25 X-Sequence-Number: 29993 On May 2, 2008, at 1:13 PM, Tom Lane wrote: > I don't think you should figure on more than 1GB being > usefully available to Postgres, and you can't give all or even most of > that space to shared_buffers. So how should I divide say a 512 MB between shared_buffers and, um, what else? (new to pg tuning :) I naively thought that if I have a 100,000,000 row table, of the form (integer,integer,smallint,date), and add a real coumn to it, it will scroll through the memory reasonably fast. Yet when I had shared_buffers=128 MB, it was hanging there 8 hours before I killed it, and now with 1500MB is paging again for several hours with no end in sight. Why can't it just add a column to a row at a time and be done with it soon enough? :) It takes inordinately long compared to a FORTRAN or even python program and there's no index usage for this table, a sequential scan, why all the paging? Cheers, Alexy