Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 643CE1337B67 for ; Mon, 1 Nov 2010 11:04:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 73805-02 for ; Mon, 1 Nov 2010 14:03:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.anarazel.de (mail.anarazel.de [217.115.131.40]) by mail.postgresql.org (Postfix) with ESMTP id E4EC81337B51 for ; Mon, 1 Nov 2010 11:03:54 -0300 (ADT) Received: from alap2.localnet (p579FA6DD.dip.t-dialin.net [87.159.166.221]) by mail.anarazel.de (Postfix) with ESMTPSA id E6DB8448003; Mon, 1 Nov 2010 15:03:53 +0100 (CET) From: Andres Freund To: pgsql-performance@postgresql.org Subject: Re: Insert performance with composite index Date: Mon, 1 Nov 2010 15:03:51 +0100 User-Agent: KMail/1.13.5 (Linux/2.6.36-rc6-andres-00006-g4193d91; KDE/4.5.1; x86_64; ; ) Cc: Divakar Singh References: <293254.80548.qm@web65413.mail.ac4.yahoo.com> In-Reply-To: <293254.80548.qm@web65413.mail.ac4.yahoo.com> MIME-Version: 1.0 Content-Type: Text/Plain; charset="utf-8" Content-Transfer-Encoding: 7bit Message-Id: <201011011503.51821.andres@anarazel.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/7 X-Sequence-Number: 41031 Hi, On Monday 01 November 2010 13:49:14 Divakar Singh wrote: > When I tried inserting 1M rows into a table with a Primary Key, it took > almost 62 seconds. > After adding a composite index of 2 columns, the performance degrades to > 125 seconds. > I am using COPY to insert all data in 1 transaction. Without seeing your config its hard to suggest anything here. Did you do basic tuning of your pg installation? wal_buffers, shared_buffers, checkpoint_segments, maintenance_work_mem are likely most relevant for that specific case. Andres