X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2A6883A6374 for ; Sat, 29 Jan 2005 05:59:07 +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 45629-07 for ; Sat, 29 Jan 2005 05:58:56 +0000 (GMT) Received: from priv-edtnes56.telusplanet.net (outbound01.telus.net [199.185.220.220]) by svr1.postgresql.org (Postfix) with ESMTP id 46FF33A5D3A for ; Sat, 29 Jan 2005 05:58:55 +0000 (GMT) Received: from localhost ([199.185.220.240]) by priv-edtnes56.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050129055854.MVPU10613.priv-edtnes56.telusplanet.net@localhost> for ; Fri, 28 Jan 2005 22:58:54 -0700 Received: from 64.180.225.200 ( [64.180.225.200]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Fri, 28 Jan 2005 21:58:54 -0800 Message-ID: <1106978334.41fb261e28ac2@webmail.telus.net> Date: Fri, 28 Jan 2005 21:58:54 -0800 From: Mischa To: pgsql-sql@postgresql.org Subject: Re: same question little different test MSSQL vrs Postgres References: <000901c5035a$3b619cc0$797ba8c0@jfradkin> <41F71AB4.5030208@illusions.com> <20050126210450.GA26734@wolff.to> <41F8043C.6080101@illusions.com> In-Reply-To: <41F8043C.6080101@illusions.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 64.180.225.200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.426 tagged_above=0 required=5 tests=DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200502/9 X-Sequence-Number: 20120 Quoting Dennis Sacks : > Bruno Wolff III wrote: > >On Tue, Jan 25, 2005 at 21:21:08 -0700, > > Dennis Sacks wrote: > >>One of the things you'll want to do regularly is run a "vacuum analyze". > >>You can read up on this in the postgresql docs. This is essential to the > >>indexes being used properly. At a bare minimum, after you import a large > >>amount of data, you'll want to run vacuum analyze. > > > Good point! Analyze after bulk inserts, vacuum analyze after > updates/deletes and inserts. :) Hmmm ... in performance tests of bulk inserts into a table with six indexes, I found that without vacuum analyze (rather than analyze), insertion slowed down albeit something less than linearly. Table of 6M rows, about 3GB (including index files). This is 7.4.1 on SuSE Linux, RAID5 Xeon(sigh) 2.8GHz 4GB nothing else running. The inserts were always done with an existing record check (LEFT JOIN ... WHERE joinkey IS NULL). -- "Dreams come true, not free."