Postgres Pro
Downloads
Home   >   mailing lists

Re: [HACKERS] How to implement a SP-GiST index as a extension module? - Mailing list pgsql-hackers

From Alexander Korotkov
Subject Re: [HACKERS] How to implement a SP-GiST index as a extension module?
Date
Msg-id CAPpHfdtpOjDrQJfvGfBedzzWW60o9QCnALzxZEoAXKXqvzHTVw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] How to implement a SP-GiST index as a extension module?  (Connor Wolf <connorw@imaginaryindustries.com>)
Responses Re: [HACKERS] How to implement a SP-GiST index as a extension module?  (Connor Wolf <connorw@imaginaryindustries.com>)
List pgsql-hackers
Tree view
Hi!

On Mon, Nov 13, 2017 at 6:47 AM, Connor Wolf <connorw@imaginaryindustries.com> wrote:
Ok, I've managed to get my custom index working. 
 
Good!

It's all on github here: https://github.com/fake-name/pg-spgist_hamming, if anyone else needs a fuzzy-image searching system 
that can integrate into postgresql..

It should be a pretty good basis for anyone else to use if they want to implement a SP-GiST index too. 

I took a look at the code, and I feel myself a bit confused :)
It appears that you're indexing int8 values.  That seems like unrealistic short representation for image signature.
Also, name of repository make me think that hamming distance would be used to compare signatures.  But after look at the code, I see that plain absolute value of difference is used for that purpose.

static double
getDistance(Datum v1, Datum v2)
{
    int64_t a1 = DatumGetInt64(v1);
    int64_t a2 = DatumGetInt64(v2);
    int64_t diff = Abs(a1 - a2);
    fprintf_to_ereport("getDistance %ld <-> %ld : %ld", a1, a2, diff);
    return diff;
}

For such notion of distance, you don't need a VP-tree or another complex indexing.  B-tree is quite enough in this case.  Alternatively, distance function is not what it meant to be.

It would be useful if you provide complete usage example of this extension: from image to signature conversion to search queries. 

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

pgsql-hackers by date:

Previous
From: Anthony Bykov
Date:
Subject: Re: [HACKERS] Jsonb transform for pl/python
Next
From: Alexander Korotkov
Date:
Subject: Re: [HACKERS] Fix bloom WAL tap test