Re: WIP: SP-GiST, Space-Partitioned GiST

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP: SP-GiST, Space-Partitioned GiST
Date: 2011-12-05 13:49:45
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi there,

attached is the latest version of patch (v.110) for current 9.2. Now it
includes README and spgist.sgml.

There is one annoying problem under MAC OS (Linux, FreeBSD have no problem), we
just can't figure out how to find it, since we are not familiar with MAC OS -
it fails to restart after 'kill -9' backend, but only if sources were
compiled with -O2 option (no problem occured with -O0). Since the fail happens
not every time, we use following script to reproduce the problem. We ask
MAC OS guru to help us debugging this problem.


# enable core file dumping by a system
ulimit -c unlimited

# create test data
createdb test
psql test -c 'drop table if exists qq;
create table qq as select point(, p.long) as p
from (
select (0.5-random())*180 as lat, random()*360 as long
from generate_series(1,10000000)
) as p;

while :
psql test -c 'create index spgist_idx on qq using spgist(p)' & sleep 20
kill -9 `ps ax | grep postgres: | grep -v grep | awk '{print $1}' | xargs`
if [ $rc -gt 0 ]
echo Something is going wrong (rc=$rc) ...
sleep 10

On Sun, 2 Oct 2011, Tom Lane wrote:

> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> On 06.09.2011 20:34, Oleg Bartunov wrote:
>>> Here is the latest spgist patch, which has all planned features as well as
>>> all overhead, introduced by concurrency and recovery, so performance
>>> measurement should be realistic now.
>> I'm ignoring the text suffix-tree part of this for now, because of the
>> issue with non-C locales that Alexander pointer out.
> It seems to me that SP-GiST simply cannot work for full text comparisons
> in non-C locales, because it's critically dependent on the assumption
> that comparisons of strings are consistent with comparisons of prefixes
> of those strings ... an assumption that's just plain false for most
> non-C locales.
> We can dodge that problem in the same way that we did in the btree
> pattern_ops opclasses, namely implement the opclass only for the =
> operator and the special operators ~<~ etc. I think I favor doing this
> for the first round, because it's a simple matter of removing code
> that's currently present in the patch. Even with only = support
> the opclass would be extremely useful.
> Something we could consider later is a way to use the index for the
> regular text comparison operators (< etc), but only when the operator
> is using C collation. This is not so much a matter for the index
> implementation as it is about teaching the planner to optionally
> consider collation when matching an operator call to the index. It's
> probably going to tie into the unfinished business of marking which
> operators are collation sensitive and which are not.
> In other news, I looked at the patch briefly, but I don't think I want
> to review it fully without some documentation. The absolute minimum
> requirement IMO is documentation comparable to what we have for GIN,
> ie a specification for the support methods and some indication of when
> you'd use this index type in preference to others. I'd be willing to
> help copy-edit and SGML-ize such documentation, but I do not care to
> reverse-engineer it from the code.
> regards, tom lane

Oleg Bartunov, Research Scientist, Head of AstroNet (,
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(495)939-16-83, +007(495)939-23-83

Attachment Content-Type Size
spgist_patch-0.110.gz application/octet-stream 47.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kohei KaiGai 2011-12-05 13:54:09 Re: Prep object creation hooks, and related sepgsql updates
Previous Message Heikki Linnakangas 2011-12-05 13:23:45 Re: Inlining comparators as a performance optimisation