Re: sp-gist porting to postgreSQL

On Wed, 10 Nov 2004, Oleg Bartunov wrote:

> On Wed, 10 Nov 2004, Ramy M. Hassan wrote:
>
>> I believe that it is still possible to have several index access methods
>> for
>> the same type and the same operations. But this requires that each index
>> access method has its own tuple in the pg_am relation and therefore
>> postgresql recognizes it by itself. But this is not the case with GiST
>> based
>> indices. They are all recognized by postgresql as same index access method,
>> and from here comes the limitation.
>
> It's possible, see contrib/intarray, for example. You can specify
> opclass in CREATE INDEX command:
>
> CREATE INDEX text_idx on test__int using gist ( a gist__int_ops );
> CREATE INDEX text_idx2 on test__int using gist ( a gist__intbig_ops );
>
> Here gist__int_ops and gist__intbig_ops are different opclasses for the
> same type and intended to use with different cardinality. The problem is how
> to use them (indices) automatically, how planner/optimizer could
> select which indices to use.

This is great. I didn't know that. Thanks.

>
>>
>> Also, I think GiST and SP-GiST are better viewed as index classes not as
>> indices by themselves. So may be it is more logical to say:
>> CREATE INDEX index_name ON table_name USING spgist_trie(field)
>> Where spgist_trie is an spgist based index instance.
>>
>> Than to say:
>> CREATE INDEX index_name ON table_name USING spgist(field)
>> And depend on the operator classes to define the required support methods
>> for the trie function.
>>
>>
>
> why not use existed syntax ?
> CREATE INDEX index_name ON table_name USING spgist (fiels trie_ops)
>

That's ok now.
The only concern now is the portability of the extensions. Currently Are there any
plans to introduce GiST to some other DBMS ? If yes, then I think all GiST
based indexes will have to be rewritten or atleast modified to a great
extent, as they depend on postgresql API and how index access methods work in postgresql.
Do you see any value in defining an SP-GiST API for the extensions
to completely isolate the extensions code from postgresql ?
Such isolation will require that SP-GiST code loads the extensions instead
of relying on postgresql to do that so it will no longer be a matter of
operator classes that defines extension.

>
>> I am not sure I have a complete vision, but this is what I see. I would
>> appreciate your opinions regarding to this design issue.
>>
>
> Teodor is rather busy right now, but he certainly knows better GiST
> internals,
> so we'll wait his comments.
>
>> Thanks
>> Ramy
>>
>>
>>
>>
>> -----Original Message-----
>> From: Oleg Bartunov [mailto:oleg(at)sai(dot)msu(dot)su]
>> Sent: Wednesday, November 10, 2004 5:21 AM
>> To: Ramy M. Hassan
>> Cc: 'Pgsql Hackers'; 'Teodor Sigaev'; 'Walid G. Aref'
>> Subject: RE: sp-gist porting to postgreSQL
>>
>> On Wed, 10 Nov 2004, Ramy M. Hassan wrote:
>>
>>> Oleg,
>>>
>>> Thanks for your prompt reply.
>>> Actually, I am able to create a new access method for testing and add an
>>> operator class for the type "integer" using the new access method. Then
>>> created a table with two integer fields, one indexed using the new access
>>> method and the other using a btree index, and everything is ok so far.
>> Even
>>> using EXPLAIN statement for queries show that the indexes are used
>> correctly
>>> as they should.
>>> I am using postgresql version 8.0.0beta3 from CVS.
>>
>> I was wrong, Ramy. You could have several indices for the same type as soon
>> as they support different operations. I don't know if it's possible
>> to have them for the same operation but for different conditions.
>>
>>>
>>> Thanks
>>> Ramy
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Oleg Bartunov [mailto:oleg(at)sai(dot)msu(dot)su]
>>> Sent: Wednesday, November 10, 2004 12:35 AM
>>> To: Ramy M. Hassan; Pgsql Hackers
>>> Cc: Teodor Sigaev; Walid G. Aref
>>> Subject: Re: sp-gist porting to postgreSQL
>>>
>>> Ramy,
>>>
>>> glad to hear from you !
>>> AFAIK, posgresql doesnt' supports several indices for the same type.
>>> I think this is a problem of optimizer. Probably other hackers know
>>> better. I forward your message to -hackers mailing list which is a
>>> relevant place for GiST discussion.
>>>
>>> regards,
>>> Oleg
>>>
>>>
>>> On Tue, 9 Nov 2004, Ramy M. Hassan wrote:
>>>
>>>> Dear Oleg and Teodor,
>>>> Thanks for offering help.
>>>> I have a design question for now.
>>>> Currently in the postgresql GiST implementation, I noticed that the way
>> to
>>>
>>>> have a GiST based index is to define an operator class for a certain type
>>>> using GiST index. There is no new index type defined from the point of
>>> view
>>>> of postgresql ( nothing is added to pg_am ). This means that for a
>> certain
>>>
>>>> type there could only be one GiST based index. I mean that there is no
>> way
>>> in
>>>> the same server to use gist to implement an xtree index and a ytree for
>>> the
>>>> same type even if they index different fields in different relations. is
>>>> that correct ?
>>>> What about doing it the other way ( I am talking about SP-GiST now ) , by
>>>> providing the extension writer with an API to use it to instantiate a
>>>> standalone SP-GiST based index ( for example trie index ) that has a
>>> record
>>>> in the pg_am relation. In my point of view this would give more
>>> flexibility,
>>>> and also would not require the extension writer to learn the postgresql
>>> API (
>>>> maybe oneday SP-GiST will be ported to another database engine ) he will
>>>> just need to learn the SP-GiST API which will propably be less amount of
>>>> study (and this is what GiST and SP-GiST is all about if I correctly
>>>> understand ).
>>>> Please let me know your opinions regarding to this.
>>>>
>>>> Thanks
>>>>
>>>> Ramy
>>>>
>>>
>>> Regards,
>>> Oleg
>>> _____________________________________________________________
>>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>>> Sternberg Astronomical Institute, Moscow University (Russia)
>>> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
>>> phone: +007(095)939-16-83, +007(095)939-23-83
>>>
>>
>> Regards,
>> Oleg
>> _____________________________________________________________
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>> Sternberg Astronomical Institute, Moscow University (Russia)
>> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>