X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F1BE7566AD for ; Wed, 16 Mar 2005 08:00:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76654-10 for ; Wed, 16 Mar 2005 07:59:58 +0000 (GMT) Received: from ucsns.ucs.co.za (ucs.co.za [196.23.43.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9C84E566D7 for ; Wed, 16 Mar 2005 07:59:51 +0000 (GMT) Received: from ucspost.ucs.co.za (mailgw1.ucs.co.za [196.23.43.253]) by ucsns.ucs.co.za (Postfix) with ESMTP id 9AF832BD79; Wed, 16 Mar 2005 09:59:36 +0200 (SAST) Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.3]) by ucspost.ucs.co.za (Postfix) with ESMTP id 614AAD9E97; Wed, 16 Mar 2005 09:59:39 +0200 (SAST) Received: from svb.ucs.co.za (svb.ucs.co.za [172.31.1.148]) by jhb.ucs.co.za (Postfix) with ESMTP id 7108E97AE9; Wed, 16 Mar 2005 09:59:39 +0200 (SAST) Date: Wed, 16 Mar 2005 09:59:39 +0200 From: Stef To: andrew@pillette.com Cc: pgsql-performance@postgresql.org Subject: Re: Slow loads when indexes added. Message-ID: <20050316095939.2d58a128@svb.ucs.co.za> In-Reply-To: <200503152042.j2FKgOo29331@pillette.com> References: <200503152042.j2FKgOo29331@pillette.com> X-Mailer: Sylpheed-Claws 1.0.1 (GTK+ 1.2.10; i386-pc-linux-gnu) User-Agent: sillypheed-claws (anti-aliased) X-Face: +lU/o'$=Zv9%Xjo]Kq[_{n&Kb`f{YEWH4&Y#M]'6BkD?VoWe#*@S\MWyD'I>s:0v4{#\[J]l*?]t2uaO(j$d*77Ca-UHo.tkl2)0$B3Jxww\o:xor]<:*Yfx9Vceh)9I/'2g\RKhGTxIIsR^ke^`LVeZ9ksb*:$kQeR@ X-Operating-System: sid X-X-X: _-^-_ Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.411 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200503/317 X-Sequence-Number: 11022 andrew@pillette.com mentioned : => Try ANALYZE after loading the referenced tables, but before loading the main table I attached a new script for creating the load file... Analyze didn't help, it actually took longer to load. I set autocommit to off, and put a commit after every 100 inserts, chattr'd noatime atrribute off recursively on PGDATA, and set fsync to off, this improved the time from 3min 51sec to 2min 37 sec for the slow scenario. But I was already doing all these things in the app that used to take 40 minutes, but now takes four hours to load. Any other suggestions? Kind Regards Stefan