Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 289BEB5DC39 for ; Fri, 5 Aug 2011 07:36:45 -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 00476-07 for ; Fri, 5 Aug 2011 10:36:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id A5F86B5D800 for ; Fri, 5 Aug 2011 07:36:38 -0300 (ADT) Received: by fxh19 with SMTP id 19so2688071fxh.19 for ; Fri, 05 Aug 2011 03:36:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=oGduxnjH81R8N0mAzYNreIR5aQ+10Qt7rpHbqyNZyjk=; b=f6G6ZTeMSNNw9GZ25J+B8j4xQTvXeXuCArNprjZhVpOuq/9ii/CplQwNxmpdxbE53E NNiNFfOD6nHuFkHUJo5QIKheCimMw1KnTiBRYcldpMsyM6ScYEPw03dXKgwHb42h348y /3Fd5Stf03QNIles4O04QsIy8W3C5GFYCHNpw= Received: by 10.204.144.207 with SMTP id a15mr605767bkv.30.1312540597880; Fri, 05 Aug 2011 03:36:37 -0700 (PDT) Received: from [10.64.1.26] ([94.45.140.16]) by mx.google.com with ESMTPS id ae11sm818547bkc.56.2011.08.05.03.36.36 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 05 Aug 2011 03:36:36 -0700 (PDT) Message-ID: <4E3BC7B6.8050700@gmail.com> Date: Fri, 05 Aug 2011 13:36:38 +0300 From: Vitalii Tymchyshyn User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: Robert Ayrapetyan CC: Kevin Grittner , "pgsql-performance@postgresql.org" Subject: Re: Performance die when COPYing to table with bigint PK References: <4E366C9C.4040904@gmail.com> <4E37F07D020000250003F9A7@gw.wicourts.gov> <4E3A7BAC020000250003FA6C@gw.wicourts.gov> <4E3AC4CE.2000507@gmail.com> <4E3A8F07020000250003FA72@gw.wicourts.gov> In-Reply-To: Content-Type: text/plain; charset=KOI8-U; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.799 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/91 X-Sequence-Number: 44538 05.08.11 11:44, Robert Ayrapetyan ΞΑΠΙΣΑΧ(ΜΑ): > Yes, you are right. Performance become even more awful. > Can some techniques from pg_bulkload be implemented in postgres core? > Current performance is not suitable for any enterprise-wide production system. BTW: I was thinking this morning about indexes. How about next feature: Implement new index type, that will have two "zones" - old & new. New zone is of fixed configurable size, say 100 pages (800 K). Any search goes into both zones. So, as soon as index is larger then 800K, the search must be done twice. As soon as new zone hit's it's size limit, part (may be only one?) of it's pages are merged with old zone. The merge is "rolling" - if last merge've stopped at "X" entry, next merge will start at entry right after X. As for me, this should greatly resolve large index insert problem: 1) Insert into new zone must be quick because it's small and hot in cache. 2) During merge writes will be grouped because items with near keys (for B-tree) or hashes (for hash index) will go to small subset of "old" zone pages. In future, merge can be also done by autovacuum in background. Yes, we get dual index search, but new zone will be hot, so this won't make it twice as costly. Best regards, Vitalii Tymchyshyn