Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1A67EB5DD96 for ; Tue, 30 Aug 2011 14:29:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 16974-02 for ; Tue, 30 Aug 2011 17:29:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og122.obsmtp.com (exprod7og122.obsmtp.com [64.18.2.22]) by mail.postgresql.org (Postfix) with ESMTP id A422AB5DD4D for ; Tue, 30 Aug 2011 14:29:17 -0300 (ADT) Received: from mail-fx0-f49.google.com ([209.85.161.49]) (using TLSv1) by exprod7ob122.postini.com ([64.18.6.12]) with SMTP ID DSNKTl0d7EXiKmLmN21ppiEtY1i4NVR8Ao1K@postini.com; Tue, 30 Aug 2011 10:29:19 PDT Received: by fxd20 with SMTP id 20so6975424fxd.36 for ; Tue, 30 Aug 2011 10:29:15 -0700 (PDT) Received: by 10.223.13.137 with SMTP id c9mr5289502faa.140.1314725355635; Tue, 30 Aug 2011 10:29:15 -0700 (PDT) Received: from [192.168.1.183] (dsl-hkibrasgw2-ff7ac300-240.dhcp.inet.fi [88.195.122.240]) by mx.google.com with ESMTPS id g16sm1014577faa.27.2011.08.30.10.29.14 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 30 Aug 2011 10:29:15 -0700 (PDT) Message-ID: <4E5D1DE9.8070502@enterprisedb.com> Date: Tue, 30 Aug 2011 20:29:13 +0300 From: Heikki Linnakangas User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.2.20) Gecko/20110820 Icedove/3.1.12 MIME-Version: 1.0 To: Alexander Korotkov CC: Robert Haas , pgsql-hackers Subject: Re: WIP: Fast GiST index build References: <4E42DFCE.3050904@enterprisedb.com> <4E4AAC49.7070401@enterprisedb.com> <4E4AC0B9.8070203@enterprisedb.com> <4E4AC1C3.4070003@enterprisedb.com> <4E569D9A.4020308@enterprisedb.com> <4E5CA88C.50008@enterprisedb.com> <4E5CA9C5.1030708@enterprisedb.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/1344 X-Sequence-Number: 194099 On 30.08.2011 13:29, Alexander Korotkov wrote: > On Tue, Aug 30, 2011 at 1:13 PM, Heikki Linnakangas< > heikki.linnakangas@enterprisedb.com> wrote: > >> So, over 50% of the CPU time is spent in choosing a block from the >> temporary files. That should be pretty easy to improve.. >> > Yes, probably we can just remove free blocks sorting. Ok, the first results are in for that: testname | nrows | duration | accesses ---------------------------+-----------+-----------------+---------- points unordered buffered | 250000000 | 06:00:23.707579 | 4049832 From the previous test runs, the unbuffered index build took under 4 hours, so even though this is a lot better than with the sorting, it's still a loss compared to non-buffered build. I had vmstat running during most of this index build. At a quick glance, it doesn't seem to be CPU bound anymore. I suspect the buffers in the temporary file gets very fragmented. Or, we're reading it in backwards order because the buffers work in a LIFO fashion. The system seems to be doing about 5 MB/s of I/O during the build, which sounds like a figure you'd get for more or less random I/O. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com