Re: compress method for spgist - 2

From: Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: Teodor Sigaev <teodor(at)sigaev(dot)ru>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Paul Ramsey <pramsey(at)cleverelephant(dot)ca>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: compress method for spgist - 2
Date: 2017-09-20 12:20:59
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On Mon, Sep 18, 2017 at 6:21 PM, Alexander Korotkov <
a(dot)korotkov(at)postgrespro(dot)ru> wrote:

> On Tue, Aug 25, 2015 at 4:05 PM, Michael Paquier <
> michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Thu, Jul 23, 2015 at 6:18 PM, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
>> >>> Poorly, by hanging boxes that straddled dividing lines off the parent
>> >>> node in a big linear list. The hope would be that the case was
>> >>
>> >> Ok, I see, but that's not really what I was wondering. My question is
>> >> this:
>> >> SP-GiST partitions the space into non-overlapping sections. How can you
>> >> store
>> >> polygons - which can overlap - in an SP-GiST index? And how does the
>> >> compress
>> >> method help with that?
>> >
>> >
>> > I believe if we found a way to index boxes then we will need a compress
>> > method to build index over polygons.
>> >
>> > BTW, we are working on investigation a index structure for box where
>> 2d-box
>> > is treated as 4d-point.
>> There has been no activity on this patch for some time now, and a new
>> patch version has not been submitted, so I am marking it as return
>> with feedback.
> There is interest to this patch from PostGIS users. It would be nice to
> pickup this patch.
> AFAICS, the progress on this patch was suspended because we have no
> example for SP-GiST compress method in core/contrib.
> However, now we have acdf2a8b committed with 2d to 4d indexing of boxes
> using SP-GiST. So, extending this 2d to 4d approach to polygons would be
> good example of SP-GiST compress method in core. Would anyone be
> volunteering for writing such patch?
> If nobody, then I could do it....

Nobody answered yet. And I decided to nail down this long term issue.
Please, find following attached patches.


Patch with SP-GiST compress method rebased on current master. Index AM
interface was changed since that time. I've added validation for compress
method: it validates input and output types of compress method. That
required to call config method before. That is controversial solution. In
particular, no collation is provided in config method call. It would be
weird if collation could affect data types in SP-GiST config method output,
but anyway...


This patch provides example of SP-GiST compress method usage. It adds
SP-GiST indexing for circles and polygons using mapping of their bounding
boxes to 4d. This patch is based on prior work by Nikita Glukhov for

Alexander Korotkov
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0001-spgist-compress-method-6.patch application/octet-stream 18.1 KB
0002-spgist-circle-polygon-6.patch application/octet-stream 42.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2017-09-20 12:55:47 Re: Partition-wise join for join between (declaratively) partitioned tables
Previous Message Stephen Frost 2017-09-20 12:14:05 Re: [GENERAL] USER Profiles for PostgreSQL