Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8EE03B5DC47 for ; Thu, 1 Sep 2011 05:59:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 72249-07 for ; Thu, 1 Sep 2011 08:59:22 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og111.obsmtp.com (exprod7og111.obsmtp.com [64.18.2.175]) by mail.postgresql.org (Postfix) with ESMTP id 1F164B5DC45 for ; Thu, 1 Sep 2011 05:59:20 -0300 (ADT) Received: from mail-fx0-f46.google.com ([209.85.161.46]) (using TLSv1) by exprod7ob111.postini.com ([64.18.6.12]) with SMTP ID DSNKTl9JZwasD01MUgO4LMOt/Z2j7R9MbPWJ@postini.com; Thu, 01 Sep 2011 01:59:21 PDT Received: by fxh19 with SMTP id 19so600384fxh.5 for ; Thu, 01 Sep 2011 01:59:18 -0700 (PDT) Received: by 10.223.58.66 with SMTP id f2mr1403122fah.106.1314867558454; Thu, 01 Sep 2011 01:59:18 -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 c5sm322088fai.44.2011.09.01.01.59.12 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 01 Sep 2011 01:59:13 -0700 (PDT) Message-ID: <4E5F495F.2010303@enterprisedb.com> Date: Thu, 01 Sep 2011 11:59:11 +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: 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: 201109/6 X-Sequence-Number: 194195 On 30.08.2011 13:38, Alexander Korotkov wrote: > On Tue, Aug 30, 2011 at 1:08 PM, Heikki Linnakangas< > heikki.linnakangas@enterprisedb.com> wrote: > >> >> Thanks. Meanwhile, I hacked together my own set of test scripts, and let >> them run over the weekend. I'm still running tests with ordered data, but >> here are some preliminary results: >> >> testname | nrows | duration | accesses >> -----------------------------+**-----------+-----------------+**---------- >> points unordered auto | 250000000 | 08:08:39.174956 | 3757848 >> points unordered buffered | 250000000 | 09:29:16.47012 | 4049832 >> points unordered unbuffered | 250000000 | 03:48:10.999861 | 4564986 >> >> As you can see, the results are very disappointing :-(. The buffered builds >> take a lot *longer* than unbuffered ones. I was expecting the buffering to >> be very helpful at least in these unordered tests. On the positive side, the >> buffering made index quality somewhat better (accesses column, smaller is >> better), but that's not what we're aiming at. >> >> 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? >> > This seems pretty strange for me. Time of unbuffered index build shows that > there is not bottleneck at IO. That radically differs from my > experiments. I'm going to try your test script on my test setup. > While I have only express assumption that random function appears to be > somewhat bad. Thereby unordered dataset behave like the ordered one. Can you > rerun tests on your test setup with dataset generation on the backend like > this? > CREATE TABLE points AS (SELECT point(random(), random() FROM > generate_series(1,10000000)); So I changed the test script to generate the table as: CREATE TABLE points AS SELECT random() as x, random() as y FROM generate_series(1, $NROWS); The unordered results are in: testname | nrows | duration | accesses -----------------------------+-----------+-----------------+---------- points unordered buffered | 250000000 | 05:56:58.575789 | 2241050 points unordered auto | 250000000 | 05:34:12.187479 | 2246420 points unordered unbuffered | 250000000 | 04:38:48.663952 | 2244228 Although the buffered build doesn't lose as badly as it did with more overlap, it still doesn't look good :-(. Any ideas? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com