DomainKey-Signature: a=rsa-sha1; c=nofws; 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;
	b=D9z0mXm2D3i0Z2RdPkYQKKkmAD78NLc9NofUW1APeNoMCWkp10uQQzG00mv2vqOVLc
	k340zBGB6ERBMjNHe1et3eFKt6AAxSECz5z8H3Q3GkJQUyE+p8ssvQTEg/Zv5AU3ELAt
	ERthb3fKzIfdY2RHCoonU6SdjIzHfwqjnfnto=
Message-ID: <4BA77876.30803@gmail.com>
Date: Mon, 22 Mar 2010 15:02:30 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Matthew Wakeling <matthew@flymine.org>
CC: Kenneth Marshall <ktm@rice.edu>, Bruce Momjian <bruce@momjian.us>,
	pgsql-performance@postgresql.org
Subject: Re: GiST index performance
References: <201002252342.o1PNgtG07127@momjian.us>
	<alpine.DEB.2.00.1003151548180.1887@aragorn.flymine.org>
	<4B9FAF4B.20907@gmail.com> <4BA0A036.1070508@gmail.com>
	<4BA3A6C6.80406@gmail.com> <4BA3DCA1.2010808@gmail.com>
	<4BA3E35A.1000905@gmail.com> <20100319211638.GI1218@it.is.rice.edu>
	<e4961fb71003200916r6c9913dag28c04aef290f877d@mail.gmail.com>
	<alpine.DEB.2.00.1003221322080.9798@aragorn.flymine.org>
In-Reply-To: <alpine.DEB.2.00.1003221322080.9798@aragorn.flymine.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matthew Wakeling wrote:
> On Sat, 20 Mar 2010, Yeb Havinga wrote:
>> The gist virtual pages would then match more the original blocksizes 
>> that
>> were used in Guttman's R-tree paper (first google result, then figure 
>> 4.5).
>> Since the nature/characteristics of the underlying datatypes and keys 
>> is not
>> changed, it might be that with the disk pages getting larger, gist 
>> indexing
>> has therefore become unexpectedly inefficient.
>
> Yes, that is certainly a factor. For example, the page size for bioseg 
> which we use here is 130 entries, which is very excessive, and doesn't 
> allow very deep trees. On the other hand, it means that a single disc 
> seek performs quite a lot of work.
Yeah, I only did in-memory fitting tests and wondered about increased 
io's. However I bet that even for bigger than ram db's, the benefit of 
having to fan out to less pages still outweighs the over-general non 
leaf nodes and might still result in less disk io's. I redid some 
earlier benchmarking with other datatypes with a 1kB block size and also 
multicolumn gist and the multicolumn variant had an ever greater benefit 
than the single column indexes, both equality and range scans. (Like 
execution times down to 20% of original). If gist is important to you, I 
really recommend doing a test with 1kB blocks.

regards,
Yeb Havinga