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=h69inH5uEWudh6LK5KnGSn9h5PO8gtAIKSkNCRzZxeIgJrrk6ag3FamR2ACZUTXAiG
	jEMExu2KX7/rcGIHlRh03VRADn8cGSmyj7ffIKwvHOQUkJ7vnD/BcYjg93qoCqZMsMsW
	WA96CobkAnp0vyYSGMkIQUUzYqakFWMdVPbFs=
Message-ID: <4B9FAF4B.20907@gmail.com>
Date: Tue, 16 Mar 2010 17:18:19 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Matthew Wakeling <matthew@flymine.org>
CC: 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>
In-Reply-To: <alpine.DEB.2.00.1003151548180.1887@aragorn.flymine.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matthew Wakeling wrote:
>> Matthew Wakeling wrote:
>>> A second quite distinct issue is the general performance of GiST 
>>> indexes
>>> which is also mentioned in the old thread linked from Open Items. For
>>> that, we have a test case at
>>> http://archives.postgresql.org/pgsql-performance/2009-04/msg00276.php 
>>> for
>>> btree_gist indexes. I have a similar example with the bioseg GiST 
>>> index. I
>>> have completely reimplemented the same algorithms in Java for algorithm
>>> investigation and instrumentation purposes, and it runs about a hundred
>>> times faster than in Postgres. I think this is a problem, and I'm 
>>> willing
>>> to do some investigation to try and solve it.
> I have not made any progress on this issue. I think Oleg and Teodor 
> would be better placed working it out. All I can say is that I 
> implemented the exact same indexing algorithm in Java, and it 
> performed 100 times faster than Postgres. Now, Postgres has to do a 
> lot of additional work, like mapping the index onto disc, locking 
> pages, and abstracting to plugin user functions, so I would expect 
> some difference - I'm not sure 100 times is reasonable though. I tried 
> to do some profiling, but couldn't see any one section of code that 
> was taking too much time. Not sure what I can further do.
Hello Mathew and list,

A lot of time spent in gistget.c code and a lot of functioncall5's to 
the gist's consistent function which is out of sight for gprof.
Something different but related since also gist: we noticed before that 
gist indexes that use a compressed form for index entries suffer from 
repeated compress calls on query operands (see 
http://archives.postgresql.org/pgsql-hackers/2009-05/msg00078.php).

The btree_gist int4 compress function calls the generic 
gbt_num_compress, which does a palloc. Maybe this palloc is allso hit al 
lot when scanning the index, because the constants that are queries with 
are repeatedly compressed and palloced.

regards,
Yeb Havinga