Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3CF75B5DC07 for ; Tue, 30 Aug 2011 06:22:15 -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 38905-09 for ; Tue, 30 Aug 2011 09:22:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og107.obsmtp.com (exprod7og107.obsmtp.com [64.18.2.167]) by mail.postgresql.org (Postfix) with ESMTP id 08047B5DBFB for ; Tue, 30 Aug 2011 06:22:05 -0300 (ADT) Received: from mail-ey0-f174.google.com ([209.85.215.174]) (using TLSv1) by exprod7ob107.postini.com ([64.18.6.12]) with SMTP ID DSNKTlyrpG+KqFHxvl8mMmLZm2tHKEi9ieUR@postini.com; Tue, 30 Aug 2011 02:22:06 PDT Received: by mail-ey0-f174.google.com with SMTP id 24so3598880eyx.5 for ; Tue, 30 Aug 2011 02:21:40 -0700 (PDT) Received: by 10.213.14.5 with SMTP id e5mr22497eba.23.1314695627674; Tue, 30 Aug 2011 02:13:47 -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 h76sm3550071eeh.9.2011.08.30.02.13.42 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 30 Aug 2011 02:13:43 -0700 (PDT) Message-ID: <4E5CA9C5.1030708@enterprisedb.com> Date: Tue, 30 Aug 2011 12:13:41 +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> In-Reply-To: <4E5CA88C.50008@enterprisedb.com> 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/1321 X-Sequence-Number: 194076 On 30.08.2011 12:08, Heikki Linnakangas wrote: > What's going on here? This data set was large enough to not fit in RAM, > the table was about 8.5 GB in size (and I think the index is even larger > than that), and the box has 4GB of RAM. Does the buffering only help > with even larger indexes that exceed the cache size even more? The tests are still running, so I decided to try oprofile. The build is in the final emptying phase, according to the log, and has been for over half an hour now. Oprofile output looks very interesting: samples % image name symbol name 228590 30.3045 postgres pg_qsort 200558 26.5882 postgres gistBuffersFreeBlocksCmp 49397 6.5486 postgres gistchoose 45563 6.0403 postgres gist_box_penalty 31425 4.1661 postgres AllocSetAlloc 24182 3.2058 postgres FunctionCall3Coll 22671 3.0055 postgres rt_box_union 20147 2.6709 postgres gistpenalty 17007 2.2546 postgres DirectFunctionCall2Coll 15790 2.0933 no-vmlinux /no-vmlinux 14148 1.8756 postgres XLogInsert 10612 1.4068 postgres gistdentryinit 10542 1.3976 postgres MemoryContextAlloc 9466 1.2549 postgres FunctionCall1Coll 9190 1.2183 postgres gist_box_decompress 6681 0.8857 postgres med3 4941 0.6550 libc-2.12.so isnanf So, over 50% of the CPU time is spent in choosing a block from the temporary files. That should be pretty easy to improve.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com