Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1F714B5DC5E for ; Thu, 1 Sep 2011 06:37:58 -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 87893-06 for ; Thu, 1 Sep 2011 09:37:51 +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 9C9A9B5DC5B for ; Thu, 1 Sep 2011 06:37:50 -0300 (ADT) Received: from mail-fx0-f48.google.com ([209.85.161.48]) (using TLSv1) by exprod7ob122.postini.com ([64.18.6.12]) with SMTP ID DSNKTl9SbTNm3bXI8HKrN3mKi8wSZY8Fy0Bp@postini.com; Thu, 01 Sep 2011 02:37:51 PDT Received: by fxg7 with SMTP id 7so515031fxg.7 for ; Thu, 01 Sep 2011 02:37:48 -0700 (PDT) Received: by 10.223.28.145 with SMTP id m17mr11087fac.64.1314869868348; Thu, 01 Sep 2011 02:37:48 -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 16sm347813faw.18.2011.09.01.02.37.45 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 01 Sep 2011 02:37:46 -0700 (PDT) Message-ID: <4E5F5266.4010602@enterprisedb.com> Date: Thu, 01 Sep 2011 12:37:42 +0300 From: Heikki Linnakangas User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.2.21) Gecko/20110831 Icedove/3.1.13 MIME-Version: 1.0 To: Alexander Korotkov CC: Robert Haas , pgsql-hackers Subject: Re: WIP: Fast GiST index build References: <4E4AAC49.7070401@enterprisedb.com> <4E4AC0B9.8070203@enterprisedb.com> <4E4AC1C3.4070003@enterprisedb.com> <4E569D9A.4020308@enterprisedb.com> <4E5CA88C.50008@enterprisedb.com> <4E5F495F.2010303@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/8 X-Sequence-Number: 194197 On 01.09.2011 12:23, Alexander Korotkov wrote: > On Thu, Sep 1, 2011 at 12:59 PM, Heikki Linnakangas< > heikki.linnakangas@enterprisedb.com> wrote: > >> 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? > > > But it's still a lot of overlap. It's about 220 accesses per small area > request. It's about 10 - 20 times greater than should be without overlaps. Hmm, those "accesses" numbers are actually quite bogus for this test. I changed the creation of the table as you suggested, so that all x and y values are in the range 0.0 - 1.0, but I didn't change the loop to calculate those accesses, so it still queried for boxes in the range 0 - 100000. That makes me wonder, why does it need 220 accesses on average to satisfy queries most of which lie completely outside the range of actual values in the index? I would expect such queries to just look at the root node, conclude that there can't be any matching tuples, and return immediately. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com