Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 89BD464FCFF for ; Mon, 27 Oct 2008 11:35:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81770-08 for ; Mon, 27 Oct 2008 11:35:21 -0300 (ADT) X-Greylist: delayed 00:05:11.539577 by SQLgrey-1.7.6 Received: from n62.bullet.mail.sp1.yahoo.com (n62.bullet.mail.sp1.yahoo.com [98.136.44.35]) by postgresql.org (Postfix) with SMTP id B600664FCB2 for ; Mon, 27 Oct 2008 11:35:21 -0300 (ADT) Received: from [216.252.122.218] by n62.bullet.mail.sp1.yahoo.com with NNFMP; 27 Oct 2008 14:30:07 -0000 Received: from [69.147.65.155] by t3.bullet.sp1.yahoo.com with NNFMP; 27 Oct 2008 14:30:07 -0000 Received: from [127.0.0.1] by omp403.mail.sp1.yahoo.com with NNFMP; 27 Oct 2008 14:30:07 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 756824.98554.bm@omp403.mail.sp1.yahoo.com Received: (qmail 25156 invoked by uid 60001); 27 Oct 2008 14:30:07 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Message-ID; b=BueAmapxptGE7vsTAgi/CBqSLVznEWXRHqiCQja1Ydpr/IE0s/boy4ajix/SX/bXubq88iLvEGV3hLyJOH1C+YCYe3sl+8VK3yX2hvHp4JjZGkXEoHNhMTvjV3SRRPE7qmnlPW0HG2tv3Tj9HRySEyLT8KeRRlG977ExbE+oiQQ=; X-YMail-OSG: rIPu5n0VM1lOwpLuvREKJrGMNjJIes0SGX7CHhXPCAVKoSZULaA4wN4X_ub0dOAvMxk5dAH583p5LcfQLcyDLQ3S06U32pb.JG_oOFENC3ep9G8XLX891IrA1E86mA8NJWQjeKUpJFd304XM3OtIgIJx6Lc- Received: from [207.164.182.8] by web45101.mail.sp1.yahoo.com via HTTP; Mon, 27 Oct 2008 07:30:07 PDT X-Mailer: YahooMailWebService/0.7.247.3 Date: Mon, 27 Oct 2008 07:30:07 -0700 (PDT) From: Ioana Danes Reply-To: ioanasoftware@yahoo.ca Subject: maintenance_work_mem and create index To: PostgreSQL Performance MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Message-ID: <541411.24327.qm@web45101.mail.sp1.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/112 X-Sequence-Number: 31693 Hello, I have a question regarding the maintenance_work_mem and the index creation. I have a dedicated postgresql server with 16GB of RAM. The shared_buffers is 4GB and the maintenance_work_mem is 2GB. I have a table with 28 mil records and 2 one-column indexes: 1. First index is for an integer column - size on disk 606MB 2. The second index is for a varchar column (15 characters usually) - size on disc 851MB. When I create the first index (for the integer column) it fits in the memory and it takes 1 minute to be created and is using around 1.7GB of the maintenance work memory... The second index is swapping on pgsql_tmp and it takes 26 minutes to be created so it looks like the 2GB of maintenance work memory is not enough to create a 851MB index... So my question is the 2GB of maintenance work memory would be enough only for indexes 600MB or smaller on disk? It looks like for creating an index is required a maintenance work memory 3 times larger than the size of the index on disk or I am missing other parameters? Thanks a lot, Ioana __________________________________________________________________ Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your favourite sites. Download it now at http://ca.toolbar.yahoo.com.